Google スプレッドシートを方眼紙としてではなく、ちゃんとデータを保存するテーブルとして使用していた場合、外部のシステムからそのデータを使用したいと思うのは必然ですよね？なんなら Web のフロントエンドから直接 JavaScript で fetch して使いたいですよね？？
それ簡単にできますよ？そう、Google Apps Script ならね！
例えば Google スプレッドシートにこんなデータがあって、このデータを外部から JSON で取得したいとしましょう。この記事の最後に完成するこんな API を作るという事です。
それには、前述のように Google Apps Script を書く必要があるので、ツールからスクリプトエディタを開きます。
ただこのエディタ、挙動がおかしくてすこぶるストレスがたまるので、ちょっとしたスクリプトを書いたり既存のスクリプトの簡単な修正ならともかく、ある程度本格的に書くつもりならば手元の使い慣れたエディタで書いたものを貼り付けた方が良いでしょう。
さて、デフォルトの関数は要らないので消して、代わりの関数を書いてスプレッドシートのデータをオブジェクトのリストに詰めてみましょう。こんな風になります。
function getData() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet1 = spreadsheet.getSheetByName('シート1');
const range = sheet1.getRange('A2:G11');
const values = range.getValues();
const data = values.map(row => {
let col = 0;
return {
id: row[col++],
name: row[col++],
furi: row[col++],
gender: row[col++],
bloodType: row[col++],
birthDate: row[col++].getTime(),
zip: row[col++],
}
});
console.log(data);
return data;
}
初見で理解が難しいポイントになるのは一ヶ所くらいかと思います。range.getValues() は Object の二次元配列を返しますが、実際の型はスプレッドシート上の型に依存するので、生年月日の列が Date 型で返ってきます。JSON では日付型をそのまま扱うことが出来ないので、それを getTime() でエポックタイムにしています。
このエディタにはデバッガも搭載されていてステップ実行なども出来るのですが、残念ながらあまり快適ではないので、とりあえず console.log で結果を出力するようにしました。実行前に保存する必要があるので、適当な名前を付けて保存しましょう。
ここで上部の▶アイコンで getDate を実行しようとすると、初回のみ権限の付与が求められます。
これは「スプレッドシートの読み書きを誰の権限で実行するか」という設定で、通常はスプレッドシートの所有者として許可して良いでしょう。ところがその途中で下記のような警告が出ます。
これの意味するところは「この Google が知らない謎のスクリプトは、今からお前のスプレッドシートを読み書きする権限を要求するぞ、気をつけろ！」です。マーケットプレイスなどに登録された Google 確認済みのアドオンに許可を与える場合などは表示されないものと思われます。
今回の場合、自分自身は信頼できるデベロッパーなので、詳細の中にある「ユーザ一覧 API (安全ではないページ) に移動」をクリックして進めます。権限の付与が済むと無事に関数が実行できるようになるので、▶アイコンで実行後にログを確認しましょう。
スプレッドシート上のデータがいい感じにリストにまとまってることが確認できましたね！
次に、ここで取得したデータを JSON で返す API にします。それにはまず特殊な名前の関数 doGet を実装する必要があります。こんな感じです。
function doGet() {
const data = getData();
const response = ContentService.createTextOutput();
response.setMimeType(MimeType.JSON);
response.setContent(JSON.stringify(data));
return response;
}
特に難しいところはないですね。Google Apps Script の SDK を知っているか知っていないか、というだけの問題です。
ここまで来たら後は最後のステップ、この doGet にユニークな URL を与えて API として公開します。公開からウェブアプリケーションとして導入を選んで下さい。
ここまでの過程でもちょこちょこ翻訳漏れを見かけましたが、このダイアログはほぼ完全に英語です。いかに日本のユーザが少ないか、あるいは日本が軽視されているかが分かりますね。
ここで、Project version は "New"、Execute the app as (誰の権限でスクリプトを実行するか) は "Me" (あなた自身) に設定し、Who has access to the app (誰が API にアクセスできるか) は用途に応じて変更して下さい。
ここを例えば、"Only myself" (自分だけ) にすると、Google ログイン状態の時しか使えない API になりますが、それだと JS からシンプルに fetch する事は出来なくなるので、この記事の趣旨に照らせば "Anyone, even anonymous" (匿名であっても誰でも) を選ぶことになります。
一つ注意点として、元データのスプレッドシートを読み書きする権限をスクリプトに付与していることを忘れないようにして下さい。万一のことがあった場合に外部に流出すると深刻な問題になるデータは、間接的にせよ公開状態にするスプレッドシートには置かない事が肝要です。
ここで Deploy ボタンを押すとユニークな URL が発行されて API としてアクセスすることが出来るようになります。
今回発行された URL は https://script.google.com/macros/s/AKfycbyRzwWIOOl6xI-c-kB6-rKYm5L-UwBRe_FZwD13_n0An4_Pyeg/exec です。試しに開いてみて下さい。どうでしょう？思った通りの JSON が取得出来ましたか？
なお、何かしらの修正を行って保存をしても、その内容が即座に反映されたりはしません。Google Apps Script 上でバージョン管理がなされているためです。API を更新したい場合は、再び公開からウェブアプリケーションとして導入を選びます。
初期状態では、最後に公開したバージョンが選ばれているので、Project version に "New" を選び直して更新することで、最新の状態を反映することが出来ます。この際すでに発行済みのユニーク URL は変わらないので、API を fetch しているクライアント側の変更は必要ありません。
Google スプレッドシートのデータを外部に API を通じて JSON として公開する方法は以上になります。当初は他にも色々と書こうと思っていたのですが、大分長くなったのでいったんここで一区切りとします。
この記事で扱ったのはあくまでデータを読み出す部分だけでしたが、反響が大きければ下記の内容も別に書こうと思います。
- Google フォームを使ってデータを集めてスプレッドシートに溜める
- POST メソッドも Google Apps Script で受けられるようにして、より高度なフォームを自作する
- cron のように定期的に Google Apps Script を実行して、必要な時にメールで通知する
LGTM お待ちしています！
【2020-07-15 追記】
続編にあたるGoogle フォームって知名度低くない？Google スプレッドシートと連携させるとこんなに幸せに！を公開しました。Google Apps Script からはいったん離れた内容ですが、これはこれで色々便利に使えるはずです。