WebAPI を Google スプレッドシートで無料で作るシンプルな方法 [2019年版]

※ Qiita にも同じ記事を掲載しています。

無料で使えるスプレッドシートで API を作りたい

「Google スプレッドシート」… Google アカウントさえ持っていれば、いくつでも、無料で作ることが出来ます。
これを JSON で出力出来ると知ってから、はや数年…。
独自 API を作りたいと思いつつ、なかなか機会に恵まれませんでしたが、やっと着手出来ました。

約19万件のデータにも対応

さて、今回は19万件ものデータから検索、というものでした(列数は4）。
よく知られている方法では大変に重たくなります。
JSON データを for 文で回す、というのがスタンダードですが、大規模データを扱うには不向きです。

特別難しい方法は使いません

この記事では、膨大なレコードでもそこそこ、応答速度の早い API を、「簡単な技術の足し算」で作ることができましたので、手順を紹介したいと思います。

※古い記事ですと、「Google Visualization API」を使う方法もあるようですが、処理が若干ややこしかったり、昔と今では使い方が変わっていたり…で長く使うにはちょっと向いていないなという印象でした。

考え方: シートを用途で分ける

さて、最低、3つのシートを用意します。

• 実際のデータが入っているシート (仮: data) ※1行目は見出し
• ユーザからのパラメータを保存するシート(仮: param)
• 結果を表示するシート(仮: result)

今まで、「Google スプレッドシート」で WebAPI を作ろうとする場合、
1枚のシートから、Google Apps Script (GAS) でプログラミングするというやり方がほとんどだったように思えます。

今回、膨大なデータをいかに速く返すか、という問題に直面した時、
スプレッドシートの関数に「query」というものがあることを知りました。
これはシートにある情報をデータベースに見たて、範囲内にあるものを SQL のように問い合わせて、結果を得ることができるというスグレモノです。

処理のフロー

勘の良い方はここまででほとんどお分かりでしょうが、フローです。

• doGet(e) 関数で受け取った、ユーザーからの入力値（パラメータ）を「param」シート(セルA1)に書き込みます
• 「param」シートの「セルA2」に query
  式を書き、「param」シートの「セルA1」の値を参照して結果を取得します
• 「result」シートの「A2」に「param」シートの結果を参照して出力します（A1には見出しを書きます。JSONデータとして返すには、見出しが必須です）
• 「param」シートと「result」シートを分けているのは、前者は「式」を担当、後者は「値」のみを担当していると考えてください

分かってしまえば簡単かと思います。

ポイントは、スプレッドシート自体の便利機能(**query関数**)に任せてしまおうということです。

作った WebAPI を公開します(都道府県API)

「Google スプレッドシート」なので、どうしても URL は長くなりますが、無料なのでそこは目をつむってください…。
先に紹介した19万件のデータは、商用で使うことになるかも知れないので、出せません…。

ですが、この「都道府県API」と基本的な作り方はまったく同じです。

都道府県のIDと名称をすべて取得する URL
https://script.google.com/macros/s/AKfycbw2CeSCNAD9q-2aJTHFoLWZ5DrFArjJ_Xkyf9ZlXlMYvNK4yZhm/exec

結果は以下のように返ります。

[
  {
    "id": "1",
    "name": "北海道"
  },
  {
    "id": "2",
    "name": "青森県"
  },
  {
    "id": "3",
    "name": "岩手県"
  },
  {
    "id": "4",
    "name": "宮城県"
  },

都道府県の名前だけが欲しい場合: ?id=[1～47の番号] を付けます
https://script.google.com/macros/s/AKfycbw2CeSCNAD9q-2aJTHFoLWZ5DrFArjJ_Xkyf9ZlXlMYvNK4yZhm/exec?id=28

[
  {
    "name": "兵庫県"
  }
]

Codepen にこの API を使ってセレクトボックスを生成する JS を書いてみました。

GAS の中身

1. ?id= のようなパラメータは e.parameter.id で受け取ることができます
2. GETパラメータなら doGet(e)、POSTパラメータなら doPost(e) です
3. あとは、「処理のフロー」の通りです
4. パラメータがなければ、全件が入ったシートのデータを返します
5. パラメータがあれば、param シートに書き込みます
6. ここがキモですが、query 関数では、=Query(pref!A1:B, “Select B Where A = “&param!A1&””)と書いています
7. 別のシートを参照する時は「シート名!範囲」という書式になります
8. ※ ここではエラー処理は書いていませんので、あしからず

function getData(sheetName) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var rows = sheet.getDataRange().getValues();
  var keys = rows.splice(0, 1)[0];
  return rows.map(function(row) {
    var obj = {};
    row.map(function(item, index) {
      obj[String(keys[index])] = String(item);
    });
    return obj;
  });
}

function doGet(e) {
  if (isNaN(e.parameter.id)) {
    var data = getData('pref');
  } else {
    var pref_id = e.parameter.id;
    var p_sheet = SpreadsheetApp.getActive().getSheetByName('param');
    p_sheet.getRange(1,1).setValue(pref_id);
    var data = getData('result');
  }
  return ContentService.createTextOutput(JSON.stringify(data, null, 2))
  .setMimeType(ContentService.MimeType.JSON);
}

CORS への対応

スクリプトエディタのメニューで「公開」> 「ウェブアプリケーションとして導入」より

• 次のユーザーとしてアプリケーションを実行: 「自分」
• アプリケーションにアクセスできるユーザー: 「全員（匿名ユーザーを含む）」

以上の設定で、誰でも（ドメインが違うサイトからでも）利用可能になります。

最後に

いかがでしたでしょうか。

数年前、某ベンチャー企業に常駐していた時に、GAS で Google アナリティクスの日次レポートを作成し、
毎日9時にHTMLメールで送信できるようにしてほしい、という依頼がありました。

使ってみると、スプレッドシートの扱いやすさに驚きました。
また、Google Apps Script の柔軟さにも。
レポートは完成し、営業の方に喜ばれたのがうれしかったです。
※ 今では、「Google Data Studio」がありますので、上記のような手間は不要かも知れません…。

そのような経験があったので、GASを使うことには抵抗はありませんでした。

今回、スプレッドシートで API を作るにあたり、苦労したのは、CORS の設定と、
query 関数を使うことに気づくことでした。

やりながら、19万件のデータといっても、やることは単純だから、きっと何か方法があるはず、と考え続けて、
「シートを分ける」ということに気づきました（本当にコロンブスの卵）。
※ちなみに、19万件のデータですが、シート数をもうひとつ作ろうとしたところ、容量オーバーでエラーとなりました…。

無制限にデータを保存するのは不可（200万セルが上限）ですので、用途は限られると思いますが、参照用には充分だと思います。

やり方が分かれば、中学生でも API を立てられるレベルだと思います。Google はすごいですね。
SSL付きで、無料ですので、お役に立てれば幸いです。

追記

『Google Apps Scriptの罠7選 ～2日で終わるお！って仕事が10日以上かかったお…～』
↓
上記の記事を読むと、やっぱり参照系で使うのが一番なのかな…という気がしてきます。
検索でネックになる点は、本記事でクリアできていると思いますが…。
書き込みで、こけるのは怖いですね。
同じデータを複数人が同時に編集するのは、トランザクション機能のない GAS では流石に無理なのでしょう。

CRUD 処理は個人ツールとして限定して使い、
公開API としては、都道府県APIのような参照系に限定するのが無難、という気がしてきました。

なんにせよ、インターネット全体で利用するものではないようです。
繰り返しますが、個人ツールとしては充分良いものだと思います。

公開アプリは、Firebase か、きちんとデータベースを立てて開発…。そういうことですね。