はじめに
「幾つかのデータを紐づけて処理したいけれど、わざわざRDBを使うほどではない...」という時に、どのように対処するでしょうか？
RDBを使う人もいれば、Excel上で関数を駆使する人もいると思いますが、今回はGoogle Spread Sheetを使って「テーブルのJOINのような操作」を実現する方法を調べた結果を記事としてまとめてみました。
使用するデータ
球団マスタ
- 以下のデータを「球団」という名前のシートに記述します。
| ID | 球団名 |
|---|---|
| 1 | 巨人 |
| 2 | 中日 |
| 3 | ヤクルト |
| 4 | 広島 |
| 5 | DeNA |
| 6 | 阪神 |
| 7 | ソフトバンク |
| 8 | 西武 |
| 9 | 楽天 |
| 10 | ロッテ |
| 11 | 日本ハム |
| 12 | オリックス |
マスコットマスタ
- 以下のデータを「マスコット」という名前のシートに記述します。
| ID | 球団ID | 名前 |
|---|---|---|
| 1 | 1 | ジャビット |
| 2 | 1 | シスタージャビット |
| 3 | 1 | キッズジャビット |
| 4 | 1 | おじいちゃんジャビット |
| 5 | 2 | ドアラ |
| 6 | 3 | つば九郎 |
| 7 | 3 | つばみ |
| 8 | 4 | スラィリー |
| 9 | 5 | DB.スターマン |
| 10 | 6 | トラッキー |
| 11 | 7 | ハリーホーク |
| 12 | 7 | ハニーホーク |
| 13 | 7 | ハーキュリーホーク |
| 14 | 7 | ホンキーホーク |
| 15 | 7 | ヘレンホーク |
| 16 | 7 | ハック |
| 17 | 7 | リック |
| 18 | 7 | ホック |
| 19 | 8 | レオ |
| 20 | 8 | ライナ |
| 21 | 9 | クラッチ |
| 22 | 9 | クラッチーナ |
| 23 | 10 | マーくん |
| 24 | 10 | リーンちゃん |
| 25 | 10 | ズーちゃん |
| 26 | 11 | フレップ |
| 27 | 11 | ポリー |
| 28 | 12 | バファローブル |
| 29 | 12 | バファローベル |
バッファ用シート
- データを一時的に書き出すバッファ用シート（シート名：buffer）を用意します。
作成したコード
- 球団名の「ヤクルト」に紐づくマスコットの名称をメッセージボックスに表示します。
- 1つの球団に紐づくマスコットは、1ないし複数となっている点に注意が必要です。
function FindRelationRecords() {
const SHEET_team = "球団"
const SHEET_official_mascot = "マスコット"
// シート関数のQUERYを使って、球団シートから指定した球団名（※B列）に該当する球団ID（※A列）を取り出す。
let query_team = '=QUERY(\'' + SHEET_team + '\'!A1:B13,"SELECT A WHERE B=\'ヤクルト\'")'
// 球団IDをbufferシートに書き込む
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("buffer").getRange(1, 1).setFormula(query_team)
// (1,1)セルは列名が書き込まれているため、1つ下の(2,1)セルから球団IDを取得する。
let team_id = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("buffer").getRange(2, 1).getValue()
// bufferシートをクリアする
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("buffer").clear()
// 球団IDをキーにして、マスコットを取り出す。
let query_mascot = '=QUERY(\'' + SHEET_official_mascot + '\'!A1:C30,"SELECT C WHERE B=' + team_id + '")'
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("buffer").getRange(1, 1).setFormula(query_mascot)
let mascot_names = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("buffer").getDataRange().getValues()
// 配列の先頭の要素（※列名）を削除してメッセージボックスに出力する。
mascot_names.shift()
Browser.msgBox(mascot_names.join(','))
}
実行結果
- 球団名の「ヤクルト」に紐づくマスコットの名称が、メッセージボックスに表示されました。
課題点
- Query関数の結果をバッファ用シートに書き出しているため、ちょっとした処理にもかかわらずコード数が増えてしまっています。
- バッファ用シートを使わない方法があるかを調べてみましたが、Query関数を使う場合はシートへの書き出しが必須のようです。
- 処理が遅いので、大量のデータを処理するのには全く向いていないです。
- もしかすると劇的に処理速度を速める方法があるのかもしれませんが、GAS初心者の私にはそれが分かりませんでした...