Spreadsheet を KVS として使う & JSON API として使うテクニック

はじめに

こんにちは。debiru です。

の記事の続きです。前回は Spreadsheet を KVS として使ってお問い合わせフォームを実装するのみだったのですが、今回は Spreadsheet に書き込んだ情報を JSON として取得してそれを Web サイト側で参照するテクニックについて紹介します。

Spreadsheet の用意

form シート

こんなシートを用意します。

	A	B	C	D
1	date	name	subject	body
2

post シート

form と同じカラム構造ですが、こんなシートを用意します。

	A	B	C	D
1	date	name	subject	body
2	2024-01-19	debiru	サンプル投稿	こんにちは。 リンクのテストです。 https://example.com/ [test](https://example.com/)

Spreadsheet の GAS コード

GAS に以下のコードを貼り付けて保存します。そして WebApp API としてデプロイします。

既にデプロイしている場合は「新しいデプロイ」ではなく「デプロイを管理」を開き、右上の「編集」ボタンを押し、「新バージョン」を選び、「デプロイ」ボタンを押します。こうすることで、前回のデプロイと同じ API URL が利用できます。

code.gs

const Util = {
  isDate(arg) { return arg != null && typeof arg === 'object' && arg.constructor.name === 'Date'; },
  getDateStr(date = null, format = 'yyyy-MM-dd HH:mm:ss') { if (date == null) date = new Date(); return Utilities.formatDate(date, 'JST', format); },
  output(range, value) { return range.setNumberFormat('@').setValue(value); },
};

function responseAsJson(obj) {
  const content = JSON.stringify(obj, null, 2) + '\n';
  const mimeType = ContentService.MimeType.JSON;
  const response = ContentService.createTextOutput(content).setMimeType(mimeType);
  return response;
}

function doPost(e) {
  return doStore(e.parameters);
}

function doStore(args = null) {
  if (args == null) args = {};

  const params = {
    name: args.name ?? '',
    subject: args.subject ?? '',
    body: args.body ?? '',
  };

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const form = {};
  form.sheet = ss.getSheetByName('form');
  form.newRow = form.sheet.getLastRow() + 1;
  form.getCell = (col) => form.sheet.getRange(`${col}${form.newRow}`);
  form.output = (col, value) => Util.output(form.getCell(col), value);

  form.output('A', Util.getDateStr());
  form.output('B', params.name);
  form.output('C', params.subject);
  form.output('D', params.body);

  return responseAsJson({ params });
}

function doGet(e) {
  return doShow(e.parameters);
}

function doShow(args = null) {
  if (args == null) args = {};

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const post = {};
  post.sheet = ss.getSheetByName('post');
  post.rows = post.sheet.getDataRange().getValues();
  post.keys = post.rows.shift();
  post.records = [];
  post.rows.forEach((row) => {
    const record = {};
    post.keys.forEach((key, idx) => {
      let value = row[idx];
      if (Util.isDate(value)) value = Util.getDateStr(value, 'yyyy-MM-dd');
      record[key] = value;
    });
    post.records.push(record);
  });

  return responseAsJson(post.records);
}

用意できた Web API URL を確認する

「デプロイを管理」からウェブアプリの API URL が確認できます。

• https://script.google.com/macros/s/AKfycbzgqTYLZYqcVLYZvqJndtL7BEx6aXRuimDTjhFEvhlUnepnEq5N61_6owKy2CTbDwd7bw/exec

こんなような API URL です。

この API URL は二つの API のエンドポイントとなっています。

• POST リクエストで、KVS として保存を受け付ける更新系 API
• GET リクエストで、JSON をレスポンスする取得系 API

JSON API を使いやすくする

• https://script.google.com/macros/s/AKfycbzgqTYLZYqcVLYZvqJndtL7BEx6aXRuimDTjhFEvhlUnepnEq5N61_6owKy2CTbDwd7bw/exec

のような URL にアクセスすると JSON が取得できますが、このままではレスポンスに時間がかかってしまいますし、大量のアクセスをすると API 呼び出し制限に引っかかってしまいます。

そこで、キャッシュサーバーを介して JSON API を呼び出せるようにします。

方法はいろいろありますが、今回は Cloudflare Workers を使ってキャッシュを実現してみます。

Cloudflare アカウントを作成する

• https://dash.cloudflare.com/sign-up/workers-and-pages

• メールアドレス
• パスワード

を入力して Sign up ボタンを押します。

メールアドレスの確認

メールが届くので、同じブラウザ上で届いたメールに含まれている URL にアクセスします。

アクセスすると、次のような画面が表示されます。

右上の言語タブを「English」から「日本語」に変えておきましょう。

Worker を作成する

真ん中の青いボタンを押して、Worker を作成します。

サブドメインとなるラベル名を spreadsheet-api とでもしておきます。

コードを編集する

「コードを編集する」ボタンを押して、コードの編集画面に移ります。

そこで、次のコードを貼り付けます。

• apiUrl の行は、自身の JSON API URL に差し替えてください。

worker.js

export default {
  async fetch(request, env, ctx) {
    const cacheUrl = new URL(request.url);
    const forceUpdate = cacheUrl.searchParams.get('force-update') === 'true';
    cacheUrl.search = ''; // remove query-string

    const cache = caches.default;
    const cacheKey = new Request(cacheUrl.toString(), request);

    if (!forceUpdate) {
      const response = await cache.match(cacheKey);
      if (response) return response;
    }

    return fetchAndCache(cacheKey, cache, ctx);
  }
};

async function fetchAndCache(cacheKey, cache, ctx) {
  const apiUrl = 'https://script.google.com/macros/s/AKfycbzgqTYLZYqcVLYZvqJndtL7BEx6aXRuimDTjhFEvhlUnepnEq5N61_6owKy2CTbDwd7bw/exec';
  const ttl = 86400 * 7;

  let response = await fetch(apiUrl);

  if (!response.ok) {
    return new Response('Failed to fetch API', { status: 500 });
  }

  response = new Response(response.body, response);
  response.headers.set('Cache-Control', `public, max-age=${ttl}, s-maxage=${ttl}`);
  response.headers.set('Content-Type', 'application/json; charset=utf-8');
  response.headers.set('Expires', new Date(Date.now() + (ttl * 1000)).toUTCString());
  ctx.waitUntil(cache.put(cacheKey, response.clone()));

  return response;
}

右上の「デプロイ」ボタンを押します。

Cloudflare Worker API にアクセスする

ご自身の Cloudflare Worker のページの API URL を確認します。

• https://spreadsheet-api.debiru.workers.dev/

その URL にアクセスすると、最初は JSON レスポンスに数秒かかるものの、2回目以降はすぐにレスポンスが変えるはずです。

ターミナルから確認したければ、次のようなコマンドを実行します。

curl -i 'https://spreadsheet-api.debiru.workers.dev/'

cf-cache-status: HIT と表示されていれば成功です。

JSON の内容を変更する

• スプレッドシートの post シートの内容を書き換えます
• https://spreadsheet-api.debiru.workers.dev/?force-update=true にアクセスします
  • 上記の API URL は私のサンプル API です。ご自身の API URL の末尾に ?force-update=true を付与してアクセスしてください

以上で、Cloudflare Worker を用いたキャッシュ API の実装は完了です。

https://spreadsheet-api.debiru.workers.dev/ のようなご自身の URL を使って、Web サイト上で JavaScript から fetch するなどして JSON を取得し、表示することで Spreadsheet から JSON API を経由して Web サイトへ情報を表示することができるようになります。

おわりに

今回のテクニックは、Spreadsheet (GAS) の取得系 API において、Cloudflare Workers でキャッシュ API を経由することにより、高速に JSON を取得できるようにするものでした。

KVS のための更新系 API と組み合わせることにより「お問い合わせフォーム」の実装ができ、今回の取得系 API を用いることで「ニュース」などの小さな更新系処理部分を動的に実装することができるようになります。

これらを活用して Web サイトを実装すれば、Spreadsheet を更新してキャッシュをアップデートすることによってサイトの内容を保守・運用できるので、ニュース部分の管理などをその担当の人に任せたりすることもできます。

不明点や疑問、困ったことなどがあれば @debiru_R までお気軽にご連絡ください。