この記事はGoogle Apps Scriptを実例交えて基礎からざっくり学ぶ Advent Calendar 2017 3日目の記事です。
本アドベントカレンダーは@rt_pの個人プロジェクトですが、筆者はAteam Brides Inc. Advent Calendar 2017にも参加しています。そちらでも出張版記事を書いているので、覗いていただけると嬉しいです。
はじめに
Excelやスプレッドシートを、住所録のような簡易的なデータベース代わりにして運用したいことってありますよね。
その時に、マスタデータの別スプレッドシートから参照したいなーということありませんか？
今回はスプレッドシートだけでそれを実現させる方法と、GASから似たようなことの2種類を実践してみます。
1．別スプレッドシートから参照（スプレッドシートのみ使用）
まずは前回使ったスプレッドシートに適当に名前を付けて、値を入れておきます。
そしてスプレッドシートのURLをコピーしておきましょう。
続いて新しいスプレッドシートを作成し、A1セルに以下数式を入力します。
=IMPORTRANGE("[さっきのスプレッドシートURL]", "A1:B3")
以下メッセージが出るので「アクセスを許可」を押します。
すると先程のスプレッドシートからデータをインポートできます。
別ユーザーが作成したスプレッドシートでもインポートできますが、当然スプレッドシートが共有されて閲覧できる状態でないと参照できません。
IMPORTRANGEで指定した範囲は、VLOOKUPでも利用できます。
=VLOOKUP(A1, IMPORTRANGE([参照スプレッドシートURL], "A1:B3"), 2, false)
この方法を使えば、あらゆるスプレッドシートにマスタデータが点在することなく、マスタデータに変更があった際は即座に反映されるようになります（一長一短ではありますが）。
2．別スプレッドシートから参照（GASで別スプレッドシートを読み込む）
GASでも別ファイルのデータを読み込むことができます。
上記で用いたように、マスタデータとは別に、新規でスプレッドシートを作成します。
スクリプトエディタを開き、以下コードに置き換えて実行します。
スクリプトエディタの開き方や承認が必要ですメッセージが出た際の対処法が分からない場合は
アドベントカレンダー1日目のHello, world!記事をご参照ください。
function myFunction() {
var spreadsheet = SpreadsheetApp.openById('SHEET_ID'); // SHEET_IDにご自身のスプレッドシートIDを入力
var sheet = spreadsheet.getActiveSheet();
var values = sheet.getRange('A1:B3').getValues();
Logger.log(values);
}
openById(id)
ここで入力するSHEET_IDとは、マスタデータとなるスプレッドシートのURLのspreadsheets/d/以降、スラッシュ以前までの文字列になります。
例えばマスタデータのURLが
docs.google.com/spreadsheets/d/hoge-hogehoge/edit#gid=0
の場合、var spreadsheet = SpreadsheetApp.openById('hoge-hogehoge');
となります（本物のIDはこんなに短くないですが）。
うまくいけば以下のように結果が返ってきます。ログはCtrl+Enterで見れます。
では先程のVLOOKUPでやったように、取得した二次元配列から値を検索してみたいと思います。
マスタデータから「チャーハン」の料金を取得してみるには、以下の通り書き換えて実行します。
function myFunction() {
var spreadsheet = SpreadsheetApp.openById('SHEET_ID'); // SHEET_IDにご自身のスプレッドシートIDを入力
var sheet = spreadsheet.getActiveSheet();
var values = sheet.getRange('A1:B3').getValues();
var price = findValue(values, 'チャーハン', 2); // マスタデータの左から2列目、つまり料金列の値を取得
Logger.log(price);
}
// 二次元配列から指定されたカラム番号の値を探す
function findValue(values, value, column) {
var columnIndex = column - 1; // VLOOKUPのように引数では左から何列目と渡しているが、配列キーは0から始まるので1ズレる
for (var i in values) {
if (values[i][0] == value) {
return values[i][columnIndex];
}
}
return false;
}
うまくいけば、ログにチャーハンの値段が吐かれているはずです。
繰り返しになりますが、ログはCtrl+Enterで見れます。
おわりに
別シートの値を取得することができればできることがだいぶ広がるはずです。
本アドベントカレンダーは、1日目～9日目あたりは基礎編でGASに関する基本的な操作を紹介していきます。
10日目以降は応用編ということで、実際に作ったアプリケーションとそれに関する技術の紹介になります。お楽しみに！
明日
【Google Apps Script】その4 日付を綺麗に整形する
となります。
JavaScriptでは少々整形がしづらい日付ですが、GASなら簡単にフォーマットを整えることができます！
お楽しみに。
前の記事
【Google Apps Script】その2 スプレッドシートのデータと連携する
次の記事
【Google Apps Script】その4 日付を綺麗に整形する