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

## 無料で使えるスプレッドシートで 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

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

```json
[
  {
    "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

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

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

<p class="codepen" data-height="265" data-theme-id="0" data-default-tab="js,result" data-user="mikiaki" data-slug-hash="PraxjZ" style="height: 265px; box-sizing: border-box; display: flex; align-items: center; justify-content: center; border: 2px solid; margin: 1em 0; padding: 1em;" data-pen-title="スプレッドシートAPI・JSONの読み込み">
  <span>See the Pen <a href="https://codepen.io/mikiaki/pen/PraxjZ/">
  スプレッドシートAPI・JSONの読み込み</a> by mikiaki (<a href="https://codepen.io/mikiaki">@mikiaki</a>)
  on <a href="https://codepen.io">CodePen</a>.</span>
</p>
<script async src="https://static.codepen.io/assets/embed/ei.js"></script>


## GAS の中身

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

```javascript
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 への対応

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

* 次のユーザーとしてアプリケーションを実行: **「自分」**
* アプリケーションにアクセスできるユーザー: **「全員（匿名ユーザーを含む）」** ※表記が「Anyone, even anonymous」となっている場合があるようです

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44077/9b1fb296-2ed0-e859-38d1-65ae3ad9ae8b.png)


## 最後に

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

数年前、某ベンチャー企業に常駐していた時に、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日以上かかったお…～](https://qiita.com/shgkt/items/522e09ae6efcf861aabf)』
↓
上記の記事を読むと、やっぱり参照系で使うのが一番なのかな…という気がしてきます。
検索でネックになる点は、本記事でクリアできていると思いますが…。
書き込みで、こけるのは怖いですねえ…。
同じデータを複数人が同時に編集するのは…、トランザクション機能のない GAS では流石に無理な気がします。

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

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

公開アプリは、[Firebase](https://firebase.google.com/?hl=ja) か、きちんとデータベースを立てて開発…。そういうことですね。