Google スプレッドシートをデータベース代わりに使うには？初心者でもできるAPI連携の始め方

1. はじめに

企業や個人でGoogleスプレッドシートを利用している人は多いかと思います。Google スプレッドシートは表計算アプリですが、簡易的なデータベースとしても利用できます。また、Google スプレッドシートはAPIを提供しており、プログラミングを通してデータの読み書きが可能です。

今回は、このGoogle スプレッドシートをAPIとして利用する方法を紹介します。Google スプレッドシートをAPIとして使うと、アプリのバックエンドとしてデータの保存や取得に利用できます。また、他のユーザーとの情報共有や、バックエンドでのデータメンテナンス用の画面としても活用できます。

一般的なデータベースを利用する場合、データベース自体はもちろんのこと、それを操作するアプリケーションサーバーも必要です。しかし、Google スプレッドシートならば、アプリケーションサーバーを立てる必要もなく、利用コストもGoogleアカウントで契約している範囲で済みます。そうした費用面でのメリットもあります。

2.実装方法の紹介

Google スプレッドシートをAPIとして利用する方法としては、以下の3つがあります。

Google Sheets API v4を利用する
Google Apps Script（GAS）を利用する
SaaSサービスを利用する

Google Sheets API v4の利用方法

Google Sheets API v4は、Googleが提供するAPIです。OAuth認証を利用し、Googleスプレッドシートの作成、編集が可能です。Google Sheets API v4を利用するには、Google Cloud Platform（GCP）でプロジェクトを作成し、APIキーを取得する必要があります。また、認証を行うので、その認証範囲において利用できます。

Google Sheets API の概要 | Google for Developers

Google Apps Script（GAS）での実装方法

特定のGoogleスプレッドシートを操作するのであれば、Google Apps Script（GAS）を利用する方法があります。拙作ですが SheetToREST というライブラリで、GoogleスプレッドシートにREST APIを追加できます。このAPIでは、以下の操作が可能です。

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

権限なしで操作できるので（GASのスクリプトエディタで公開設定を行う必要があります）、Googleスプレッドシートをデータベースとして利用する際に便利です。

SaaSサービスを利用する

GoogleスプレッドシートをラッピングしてAPI化してくれるサービスを利用すれば、簡単にAPIを手に入れられます。

SheetDB
SSSAPI ※ データ取得のみ

3. 具体的な実装手順

今回は SheetToREST の簡単な使い方を紹介します。

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

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

2. Apps Scriptを開く

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

3. ライブラリの追加

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

1VCmljgDuM6rWGcsNmAuOsrQh6kTIoqOIpni3R52R56PtBG-tb3_3Cdb2

スクリプトを検索して、 SheetToRest を追加します。Identifierは変更可能ですが、後述するコードは 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);
    }
  }
}

必要があれば、このコードの中でトークンの検証などを行って、セキュアにできます。

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

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

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

6. URLを取得

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

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

4. 使い方

以下は、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://monaca.io/"
  },
  {
    "id": 2,
    "name": "test2",
    "address": "address2",
    "date": "2023-11-01T15:00:00.000Z",
    "number": 2,
    "bool": false,
    "check": false,
    "link": "https://en.monaca.io/"
  }
]

レコードの検索

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

パラメーター	説明
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://monaca.io/"
  }
]

レコードの更新

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

パラメーター	説明
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",
  "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はWebアプリからも利用できます。以下は、その利用例です。

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

4. 注意点とベストプラクティス

SheetToRESTは、そこまで速くありません（むしろ遅いです）。そのため、あまり頻繁にデータアクセスする用途には向きません。より本格的に使う場合には、キャッシュ機構があるSheetDBやSSSAPIの方が良いでしょう。

Google Sheets API v4は認証を挟むので、よりセキュアに利用できます。しかし、権限によって利用できるシートの制限があるので、誰でも利用できるというのは難しいかも知れません。業務用途で、社内のメンバーしか使わないといった条件があれば、Google Sheets API v4を使うのも良いでしょう。

また、Googleスプレッドシートは表計算アプリであり、データベースではありません。ユニーク制約であったり、セルに入れられるデータ型の制限などは指定できないので注意してください。リレーションなどもないので、複雑なデータ構造を持つデータを扱うのは難しいかも知れません。あくまでも簡易的なデータベース的使い方もできるといった捉え方で利用してください。

5. まとめ

今回はGoogleスプレッドシートをAPI化し、バックエンドとして利用する方法を解説しました。幾つかの方法がありますので、目的に合ったものを選んでください。Googleスプレッドシートは、データベースとして使うには簡易的すぎるかも知れませんが、簡単にデータを保存し、取得することができるので、小規模なアプリケーションや、データの共有が必要な場合には便利です。

1. はじめに

2.実装方法の紹介

Google Sheets API v4の利用方法

Google Apps Script（GAS）での実装方法

SaaSサービスを利用する

3. 具体的な実装手順

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

2. Apps Scriptを開く

3. ライブラリの追加

4. コードの記述

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

6. URLを取得

4. 使い方

レコードの登録

レコードを取得する

レコード一覧の取得

レコードの検索

レコードの更新

レコードの削除

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

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

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

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

4. 注意点とベストプラクティス

5. まとめ

関連