スプレッドシートをWebAPI化するサービスの作り方

スプレッドシートを Web API 化するアプローチとして Google Apps Script を使ったやり方があります。
最近、人気記事になっているのがまさにそのパターンですね。
3分で API を作って世の中にデプロイするライブコーディング〜今日から君もスピードスターエンジニア〜

しかし、Google Apps Script を使った手法には次のような弱点があるため、簡易的なものにしか使えません。¹

• URLを自由に設定できない
• リクエスト数など各種制限

本記事では、サーバーやクライアントなど好きな環境からスプレッドシートを読み込む手法の解説と、スプレッドシートをWebAPI化するサービスの作り方について記載します。
スプレッドシートの読み込みは、GAS使わなくても簡単にできますよ！

Web API を動かしてみよう

読込対象のスプレッドシート

デモ用として次のスプレッドシートを用意してあります。
ユーザー一覧を管理しているスプレッドシートで、ヘッダー行とデータがあるよくあるテーブル構造です。
※URL欄で囲ってある部分は スプレッドシートのID でスプレッドシートを特定するためのキーとなります。

APIを実行

スプレッドシートを Web API 化するサービスを作ってあるので次のURLを叩いてみましょう。
https://asia-northeast1-sheetstowebapi.cloudfunctions.net/api?id=1u7TFoWtbeTS0PKKWKKXQ0RfdIgjbuzuQd53hCqOEuao&range=user!B3:E100

※URLのフォーマットは次のようになります。
https://asia-northeast1-sheetstowebapi.cloudfunctions.net/api?id={スプレッドシートのID}&range={取得する範囲}

すると次のJSONが返されます。
ヘッダー行をプロパティ名として、スプレッドシート上の赤枠で囲ってあった部分がJSONとして変換されているのがわかります。

仕組みの解説

先程のデモで使ったスプレッドシートのURLにアクセスしてみましょう。
https://docs.google.com/spreadsheets/d/1u7TFoWtbeTS0PKKWKKXQ0RfdIgjbuzuQd53hCqOEuao/edit

権限がないため次のような画面になると思います。²

ではこのWebサーバーはどうやってスプレッドシートへのアクセス権を取得しているのでしょうか。

実はサービスアカウントと呼ばれるものにスプレッドシートへのアクセス権を付与してあるのです。
※サービスアカウントの作り方については後で解説します。

このスプレッドシートの共有設定を開くと次のようになっています。
[e-mail]というサービスアカウントに読み取り権限を付与しているのが確認できます。

つまり、このWebAPIサーバーは[e-mail]というアカウントからアクセスしてシートを読んでいるような形になります。

ご自分でスプレッドシートを作成＆共有設定を行い、上記APIを叩いてみるとJSONが取得できます。
※見られて困るデータは使用しないでください

サービスアカウントを使った手法のメリット・デメリット

サービスアカウントを使った場合のメリット・デメリットを記載しておきます。

メリット/ コードをほとんど書かなくて済む

サービスアカウントを使った場合、システムに対してユーザーが認可するのではなく、システムが使うアカウントに対してユーザーが権限を与えているため、認可処理が不要になります。
そのため認可処理が不要になった分、API実行コードが 格段 にシンプルになります。

例としてNodeJSのコードを記載しておきます。
これだけのコードでシートの内容が読めています。

サービスアカウントを使ったAPI実行

const { google } = require('googleapis');
const sheets = google.sheets('v4');
const path = require('path');

execAPI('1u7TFoWtbeTS0PKKWKKXQ0RfdIgjbuzuQd53hCqOEuao', 'user!B3:E100');

async function execAPI(spreadsheetId, range) {
  const auth = await google.auth.getClient({
    keyFile: path.join(__dirname, 'service_account.json'),
    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
  });

  const apiOptions = {
    auth,
    spreadsheetId,
    range,
  };

  sheets.spreadsheets.values.get(apiOptions, (err, res) => {
    console.log(res.data.values);
  });
}

上記のコードを実行

% node src/hoge.js
[ [ 'id', 'slackAccount', 'email', 'name' ],
  [ 'A00001', 'admin', '[e-mail]', '管理者' ],
  [ 'U00001', 'hi', '[e-mail]', 'ハイ' ],
  [ 'U00002', 'hey', '[e-mail]', 'ヘイ' ],
  [ 'U00003', 'hello', '[e-mail]', 'ハロー' ],
  [ 'U00004', 'howdy', '[e-mail]', 'ハウディ' ] ]

メリット/ トークンの管理が不要になる

これも認可が不要になったため発生するメリットです。
認可処理を行うと、時間制限のあるアクセストークンと期限のないリフレッシュトークンを取得し、それぞれ管理する必要があります。
このトークン管理が面倒で、アクセスした人のアカウントとそれに対応するリフレッシュトークンの紐づけを保持したり、アクセストークンが切れたら再取得したり・・・などの処理が必要になります。

メリット/ 権限範囲を狭くすることができる

例えば、スプレッドシートのAPIの認可を行うと、そのユーザーが保持するすべてのスプレッドシートに対する権限を与えてしまいます。
このスプレッドシートだけアクセスを許可したいんだけど、、、みたいなことができません。
サービスアカウントを用いると、スプレッドシートの共有設定で権限を与えられるため、対象のスプレッドシートだけの許可が可能です。

デメリット/ 権限設定が面倒

スプレッドシート単位の権限設定のため、複数のスプレッドシートを読み込みたい場合はすべてのファイルに設定する必要があります。

デメリット/ 認可パターンと比べてQuota制限が厳しくなる

スプレッドシートのAPIは大量にAPIを発行できないように制限がかかっています。³

例えば、スプレッドシートの読み込みの場合、次の２つの制限が付きます。

1. プロジェクト(APIを発行するシステムとみていいです）あたり、100秒間に500リクエスト
2. アカウントあたり100秒間に100リクエスト

サービスアカウントを使用した場合、1アカウントにQuota制限がかかります。
認可を利用した場合、それぞれのユーザー単位でのQuota制限がかかるため分散されるのです。

ただし、このQuota制限はGoogleへ申請をだして緩和できるため、ものすごいデメリットかというとそうでもありません。

サーバーからSheetsの中を見るための設定手順

サービスアカウントを使ったAPI実行の仕組みを解説しましたが、ここからは実際にどうやってシートのAPIを実行するサービスアカウントを作っていくか、という解説をします。

GCPプロジェクトを作る

GCPプロジェクト 作成 とかでググってください。
※キャプチャ撮り忘れたorz

以降の手順ではSheetsToWebAPIという名前でGCPプロジェクトを作ったあとの流れになります。

サービスアカウントを作る

API & Services - Credentials から行います。
Create credentials -> Service account key と選択します。

名前とIDを入力します。
roleは不要です。

作り終わるとjsonがダウンロードされます。
このファイルをプログラムから読み込んでサービスアカウントからアクセスする形にします。

Sheets API を有効化する

スプレッドシートを読み込むために Sheets API を有効化する必要があります。

API & Services - Dashboard から行います。
ENABLES API AND SERVICES を選択します。

Sheets API を検索して有効化します。

Web API 化するプログラム

私が作成したデモは Cloud Functions を使っているためNode.jsを利用しています。
ソースをGitHubに置いとくので参考までにどうぞ。
howdy39/sheets-to-webapi: スプレッドシートを読み込んでWebAPI化するCloudFunctionsのソース

Node.js以外の言語を用いる場合は、API Client Libraries のページからAPIライブラリを探してください。
service accountで検索すると、サービスアカウントを使用したAPI実行方法が出てくると思います。

※実際にこのようなサービスを作るなら、Sheets API の結果をキャッシュするような仕組みを作って毎回API発行しないようにしたほうがいいかと思います。

あとがき

ちなみにこのデモをなぜ Cloud Functions で作ったかというと
Cloud Functions が GA になった！！
Node8 が使えるようになった！ async function 使えるぞ！！
記念になんか作っとくか！
という理由です。

1. GASの各種制限 https://developers.google.com/apps-script/guides/services/quotas#current_limitations ↩

2. Gmailにログインしていない場合は、Gmailのログイン画面になります ↩

3. スプレッドシートに限らず、GoogleのAPIには基本的に制限がある ↩