Googleスプレッドシートへ簡単にREST APIを追加するSheetToRESTの紹介

Googleスプレッドシート、使っていますか？個人的には仕事・プライベートを問わずよく使っているのですが、その際に良く行っているのが登録したデータをJSONで取得する処理です。↓な感じのGoogle Apps Scriptで、スプレッドシートのデータをJSONで取得できます。

function getData(sheetName, all = false) {
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  const rows = sheet.getDataRange().getValues();
  const keys = rows.splice(0, 1)[0];
  return rows.map(function(row) {
    const obj = {};
    row.forEach(function(item, index) {
      if (!all && keys[index].indexOf('_') === 0) return;
      obj[String(keys[index])] = String(item);
    });
    return obj;
  });
}

function doGet(e) {
    const data = getData(e.parameter.name || 'events');
    return ContentService.createTextOutput(JSON.stringify(data, null, 2))
    .setMimeType(ContentService.MimeType.JSON);
}

データの取得はこれでいいのですが、データを登録・更新したい場合もあります。データの登録はともかく、Googleスプレッドシートではデータ更新が少し面倒です。データベースのように操作できないのが不便です。

そこで、週末プロジェクトとしてGoogleスプレッドシートをREST APIを介してデータ操作できるライブラリを作りました。これを使うと、スプレッドシートのデータをJSONで取得・登録・更新・削除できます。また、データの検索や件数の取得もできます。

できること

2023年11月現在、提供しているのは以下の機能です。

• データ一覧の取得
• データの検索
• データ1件の取得
• データの登録
• データの更新
• データの削除
• 件数の取得

リポジトリ

ライブラリはオープンソース・ソフトウェア（MIT License）です。

goofmint/SheetToREST: Add REST API endpoint to Google Sheets.

セットアップ

1. Googleスプレッドシートを作成

まずは、Googleスプレッドシートを作成します。1行目はカラム名とします。デフォルトではidというカラム名がユニークキーとして使われます。また、カラム名の先頭に_をつけると、そのカラムは非表示（API公開されないデータ）になります。

2. Apps Scriptを開く

機能拡張メニューからApps Scriptを開きます。

3. ライブラリの追加

ライブラリを追加します。 + ボタンをクリックして、以下のスクリプトIDを入力します。

1VCmljgDuM6rWGcsNmAuOsrQh6kTIoqOIpni3R52R56PtBG-tb3_3Cdb2

スクリプトを検索して、 SheetToRest を追加します。後述するコードは SheetToRest を前提としているので、このまま追加してもらうのがいいでしょう。

4. コードの記述

Code.gsのコードを以下のように書き換えます。 YOUR_SHEET_NAME はRESTで操作したいシート名にしてください。もちろんクエリーストリングで可変にすることもできます。

const sheetName = 'YOUR_SHEET_NAME';

// 取得系処理用
function doGet(e) {
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  const { action, limit, skip, term, id } = e.parameter;
  switch ((action || '').toUpperCase()) {
    case 'ONE': // 1件取得
      return SheetToRest.get(sheet, id);
    case 'FIND': // 検索
      return SheetToRest.find(sheet, term);
    case 'COUNT': // カウント
      return SheetToRest.count(sheet);
    default: // 一覧取得
      return SheetToRest.list(sheet, limit, skip);
  }
}

function doPost(e) {
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  const { action, id } = e.parameter;
  switch ((action || '').toUpperCase()) {
    case 'UPDATE': { // データ更新
      const params = JSON.parse(e.postData.getDataAsString());
      return SheetToRest.update(sheet, id, params);
    }
    case 'DELETE': // データ削除
      return SheetToRest.remove(sheet, id);
    default: { // データ登録
      const params = JSON.parse(e.postData.getDataAsString());
      return SheetToRest.create(sheet, params);
    }
  }
}

Webアプリケーションとしてデプロイ

デプロイ ボタンを押して、ウェブアプリとしてデプロイします。

アクセスできるユーザーは 全員 とします。次のユーザーとして実行、は自分を選びます。

URLを取得

デプロイが完了したら、以下のようなURLが表示されます。これがREST APIのエンドポイントです。

https://script.google.com/macros/s/AKf...h91/exec

使い方

以下は、curlコマンドを使ってREST APIを叩いている例です。 URL は上で取得したエンドポイントです。

レコードの登録

% curl -H "Content-Type: application/json" \
	-d '{"name": "User 1", "address": "address 1"}' \
	-L \
	"URL"
{"name":"User 1","address":"address 1","id":"65fe3b74-ef3a-4642-a327-82a84ee6b689"}

IDは自動で生成されますが、IDを指定することもできます。自動の場合はUUIDが使われます。

% curl -H "Content-Type: application/json" \
	-d '{"id": 999, "name": "User 1", "address": "address 1"}' \
	-L \
	"URL"
{"name":"User 1","address":"address 1","id":"999"}

IDが重複する場合はエラーになります。

% curl -H "Content-Type: application/json" \
	-d '{"id": 999, "name": "User 1", "address": "address 1"}' \
	-L \
	"URL"
{error: 'duplicate'}

レコードを取得する

1件のデータを取得する場合には、以下のパラメーターをクエリーストリングで指定します。

パラメーター	説明
action	one を指定します。
id	レコードのIDを指定します。

% curl -H "Content-Type: application/json" \
	-L "URL?action=one&id=YOUR_RECORD_ID" | jq
{
  "id": "65fe3b74-ef3a-4642-a327-82a84ee6b689",
  "name": "User 1",
  "address": "address 1",
  "_secret": "",
  "date": "",
  "number": "",
  "bool": "",
  "check": "",
  "link": ""
}

もし、レコードが存在しない場合はエラーになります。

{error: 'not found'}

レコード一覧の取得

レコード一覧を取得する場合には、以下のパラメーターをクエリーストリングで指定します。

パラメーター	説明
limit	取得するレコード数を指定します。
skip	取得するレコードの開始位置を指定します。

% curl -H "Content-Type: application/json" -L "URL?limit=2" | jq
[
  {
    "id": 1,
    "name": "test1",
    "address": "address1",
    "date": "2023-10-31T15:00:00.000Z",
    "number": 1,
    "bool": true,
    "check": true,
    "link": "https://devrel.dev/"
  },
  {
    "id": 2,
    "name": "test2",
    "address": "address2",
    "date": "2023-11-01T15:00:00.000Z",
    "number": 2,
    "bool": false,
    "check": false,
    "link": "https://devrel.dev/"
  }
]

レコードの検索

レコードを検索する場合には、以下のパラメーターをクエリーストリングで指定します。

パラメーター	説明
action	find を指定します。
term	検索する文字列を指定します。

レコード検索は createTextFinder を使っているので、シート全体の検索になります。

% curl -H "Content-Type: application/json" \
	-L "URL?action=find&term=address3" | jq
[
  {
    "id": 3,
    "name": "test3",
    "address": "address3",
    "_secret": "a",
    "date": "2023-11-02T15:00:00.000Z",
    "number": 3,
    "bool": true,
    "check": true,
    "link": "https://devrel.dev/"
  }
]

レコードの更新

レコードを更新する場合には、以下のパラメーターをクエリーストリングで指定します。

パラメーター	説明
action	update を指定します。
id	レコードのIDを指定します。

データ内容はJSONで指定します。 curl の場合、 -d オプションを指定すると自動でPOSTリクエストになります。逆に -XPOST を付けると、GASがエラーになったので注意してください。

% curl -H "Content-Type: application/json" \
	-d '{"name": "User 2"}' \
	-L "URL?action=update&id=YOUR_RECORD_ID" | jq
{
  "id": "YOUR_RECORD_ID",
  "name": "User 2",
  "address": "address 1",
  "_secret": "",
  "date": "",
  "number": "",
  "bool": "",
  "check": "",
  "link": ""
}

もし、レコードが存在しない場合はエラーになります。

{error: 'not found'}

レコードの削除

レコードを削除する場合には、以下のパラメーターをクエリーストリングで指定します。

パラメーター	説明
action	delete を指定します。
id	レコードのIDを指定します。

% curl -H "Content-Type: application/json" \
	-d '{}' \
	-L "URL?action=delete&id=65fe3b74-ef3a-4642-a327-82a84ee6b689" | jq
{
  "result": "ok"
}

もし、レコードが存在しない場合はエラーになります。

{
	"error": "not found"
}

レコードの件数を取得する

レコードの件数を取得する場合には、以下のパラメーターをクエリーストリングで指定します。

パラメーター	説明
action	count を指定します。

% curl -H "Content-Type: application/json" \
	-L "URL?action=count" | jq 
{
  "count": 11
}

id以外のユニークキーを指定する

デフォルトでは id ですが、それ以外のカラム名をユニークキーとして指定することもできます。

// 更新の例。id以外のユニークキーを指定する場合は、第4引数にカラム名を指定します。
return SheetToRest.update(sheet, id, params, 'key');

単にデータを取得する場合

デフォルトの SheetToRest.list などは GoogleAppsScript.Content.TextOutput を返すので、そのままREST APIのレスポンスに使えます。しかし、単にデータを取得したい場合は、以下のように _ ではじまるメソッドを使うと便利です。

// 指定したシートのデータを取得する。レスポンスはJavaScriptオブジェクトの配列
const ary = SheetToRest._list(sheet, limit, skip);

フロントエンドアプリでの利用

このAPIはフロントエンドアプリケーションからも利用できます。以下は、フロントエンドアプリケーションからの利用例です。

const url = 'URL?action=one&id=YOUR_RECORD_ID';
const res = await fetch(url);
const json = await res.json();

セキュリティ部分は自分で実装する必要あり

Google Apps ScriptのdoGetやdoPostではHTTPヘッダーを見られないので、URLが分かるとデータの追加や更新が自由にできてしまいます（doPostやdoGetの実装次第ですが）。そのため、セキュリティ部分は自分で実装する必要があります。トークンをGoogleスプレッドシートで管理したり、何らかのロジックを実装するといいでしょう。

まとめ

SheetToRESTを使うと、Googleスプレッドシートをよりデータベース風に使えるようになります。データの一部をCMSのように公開したり、お問い合わせの受付（データ追加のみ）などにも使えると思います。ぜひ、お試しください。

goofmint/SheetToREST: Add REST API endpoint to Google Sheets.