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

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

```js
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\.](https://github.com/goofmint/SheetToREST)

## セットアップ

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

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

![sheet.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/197026/7aa803f8-425c-18cf-003f-993af684f5ef.jpeg)


### 2. Apps Scriptを開く

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

![menu.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/197026/71867025-9f16-e82d-0ed0-ed0837a1fb41.jpeg)


### 3. ライブラリの追加

![library.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/197026/9281c6f6-2448-9ab2-9956-f9acf19b4551.jpeg)


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

```
1VCmljgDuM6rWGcsNmAuOsrQh6kTIoqOIpni3R52R56PtBG-tb3_3Cdb2
```

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


### 4. コードの記述

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

```js
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/197026/05949574-03e8-46a3-799b-6722a26ff823.jpeg)


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

![webapp.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/197026/3d451927-d63e-3b84-610b-7c829f0c7f79.jpeg)


### URLを取得

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

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

![url.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/197026/cf975a5c-fe3f-fcf3-1347-a9507016bca2.jpeg)


## 使い方

以下は、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` ですが、それ以外のカラム名をユニークキーとして指定することもできます。
```js
// 更新の例。id以外のユニークキーを指定する場合は、第4引数にカラム名を指定します。
return SheetToRest.update(sheet, id, params, 'key');
```

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

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

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

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

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

```js
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\.](https://github.com/goofmint/SheetToREST)