LoginSignup
8
3
@goofmint(中津川 篤司)

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

Posted at

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公開されないデータ）になります。

sheet.jpg

2. Apps Scriptを開く

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

menu.jpg

3. ライブラリの追加

library.jpg

ライブラリを追加します。 + ボタンをクリックして、以下のスクリプト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アプリケーションとしてデプロイ

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

deploy.jpg

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

webapp.jpg

URLを取得

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

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

url.jpg

使い方

以下は、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.

8
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
3