【Google Apps Script】その3　外部スプレッドシートをDBとして利用する

Last updated at 2017-12-16Posted at 2017-12-11

この記事は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!記事をご参照ください。

open_by_id.gs

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でやったように、取得した二次元配列から値を検索してみたいと思います。
マスタデータから「チャーハン」の料金を取得してみるには、以下の通り書き換えて実行します。

find_value.gs

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;
}