Help us understand the problem. What is going on with this article?

Google スプレッドシートのデータを JS で fetch したい!

Google スプレッドシートを方眼紙としてではなく、ちゃんとデータを保存するテーブルとして使用していた場合、外部のシステムからそのデータを使用したいと思うのは必然ですよね?なんなら Web のフロントエンドから直接 JavaScript で fetch して使いたいですよね??

それ簡単にできますよ?そう、Google Apps Script ならね!

例えば Google スプレッドシートにこんなデータがあって、このデータを外部から JSON で取得したいとしましょう。この記事の最後に完成するこんな API を作るという事です。

user-list.png

それには、前述のように Google Apps Script を書く必要があるので、ツールからスクリプトエディタを開きます。

script-editor-menu.png
plain-editor.png

ただこのエディタ、挙動がおかしくてすこぶるストレスがたまるので、ちょっとしたスクリプトを書いたり既存のスクリプトの簡単な修正ならともかく、ある程度本格的に書くつもりならば手元の使い慣れたエディタで書いたものを貼り付けた方が良いでしょう。

さて、デフォルトの関数は要らないので消して、代わりの関数を書いてスプレッドシートのデータをオブジェクトのリストに詰めてみましょう。こんな風になります。

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 で結果を出力するようにしました。実行前に保存する必要があるので、適当な名前を付けて保存しましょう。

script-name.png

ここで上部の▶アイコンで getDate を実行しようとすると、初回のみ権限の付与が求められます。

authentication.png

これは「スプレッドシートの読み書きを誰の権限で実行するか」という設定で、通常はスプレッドシートの所有者として許可して良いでしょう。ところがその途中で下記のような警告が出ます。

unsafe-page.png

これの意味するところは「この Google が知らない謎のスクリプトは、今からお前のスプレッドシートを読み書きする権限を要求するぞ、気をつけろ!」です。マーケットプレイスなどに登録された Google 確認済みのアドオンに許可を与える場合などは表示されないものと思われます。

今回の場合、自分自身は信頼できるデベロッパーなので、詳細の中にある「ユーザ一覧 API (安全ではないページ) に移動」をクリックして進めます。権限の付与が済むと無事に関数が実行できるようになるので、▶アイコンで実行後にログを確認しましょう。

log-menu.png
log.png

スプレッドシート上のデータがいい感じにリストにまとまってることが確認できましたね!

次に、ここで取得したデータを 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 として公開します。公開からウェブアプリケーションとして導入を選んで下さい。

publish-menu.png
publish-new-version.png

ここまでの過程でもちょこちょこ翻訳漏れを見かけましたが、このダイアログはほぼ完全に英語です。いかに日本のユーザが少ないか、あるいは日本が軽視されているかが分かりますね。

ここで、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 としてアクセスすることが出来るようになります。

published.png

今回発行された URL は https://script.google.com/macros/s/AKfycbyRzwWIOOl6xI-c-kB6-rKYm5L-UwBRe_FZwD13_n0An4_Pyeg/exec です。試しに開いてみて下さい。どうでしょう?思った通りの JSON が取得出来ましたか?

なお、何かしらの修正を行って保存をしても、その内容が即座に反映されたりはしません。Google Apps Script 上でバージョン管理がなされているためです。API を更新したい場合は、再び公開からウェブアプリケーションとして導入を選びます。

new-version.png

初期状態では、最後に公開したバージョンが選ばれているので、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 からはいったん離れた内容ですが、これはこれで色々便利に使えるはずです。

otchy
1979 年 2 月 10 日生まれ / 仕事: Web システム開発 / 好きなもの: IT 全般、科学分野 / 座右の銘: 明日は明日の風が吹く / 好きな言葉: ハードルは高ければ高いほどくぐりやすい
https://www.otchy.net
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away