GoogleAppsScript(Typescript)のみを使ってSpreadSheetの内容をJSONで取得・作成するWebAPIを作った話(後ほど記事更新)

今回 Google Spread Sheetをデータベースとして使用し、その内容をGoogle Apps Script(通称 GAS)を使ってWebAPIにすることでアプリの中で活用可能になる仕組み(テンプレート)を作成したのでその内容について紹介します。

作ったもの

• プロジェクトはこちらになります。github-actions-examples (このプロジェクトは他にも Github Actions を活用した自動化の検証用としても使用しています。)
• サンプルとして作成したSpreadSheetのデータの内容はこちらを参照してください github-actions-examples
• 上記のサンプルデータをWebAPIとしてこちらよりJSONの形にて取得することができます github-actions-examples-webapi
• 上記のWebAPIをCDN(コンテンツデリバリネットワーク)(今回はGithub Pages) にデプロイしたものについてはこちらよりJSONを取得できます mst.json
• Google Spread Sheetの内容をJSONに変換しているソースコードはこちらを参照してください GoogleAppScript (ここでは webpack + Babel + Typescript にて GASのスクリプトを作成し、作成したものをBuildした後 clasp でpush + deployしています。)
• GAS の内容を自動的に反映させている Github Actions の内容についてはこちらを参照してください google-app-scripts-deploy.yml
• Github Pagesに自動的にGASで作成したJSONを取得して、反映させる方法についてはこちらを参照してください react-web-ci-cd.yml

制限

SpreadSheetの制限

SpreadSheetをデータベースとして使う場合、設定できるデータ量に以下にような制限があります。

• 最大合計500万セル
• 1シート最大40000行
• 1シート最大18278列
• 最大200シート
• 1セル最大50000文字
  他

この制約内

参考

ルール

今回、SpreadSheetの内容をJSONの形にする場合のデータのルールは以下のような構造になります

{
  "シート名(以下の画像の3に相当する部分)":[
    {
      "1行1列目の値(以下の画像の1に相当する部分)": 2行目1列目の値(以下の画像の2に相当する部分),
      "1行2列目の値(以下の画像の1に相当する部分)": 2行目2列目の値(以下の画像の2に相当する部分),
      ...
    },
    {
      "1行1列目の値(以下の画像の1に相当する部分)": 3行目1列目の値(以下の画像の2に相当する部分),
      "1行2列目の値(以下の画像の1に相当する部分)": 3行目2列目の値(以下の画像の2に相当する部分),
      ...
    },
    ...
  ],
  ...
}

これはMySQLなどのRDBMSにおけるテーブル名とカラム名に相当する部分をSpreadSheetに模倣させて、その内容をJSONのデータ構造に適用させたものになります。

なお、上記の内容をJSONに変換するWebAPIではURLクエリを指定することで各種調整することができます。
それぞれ指定できるクエリとその意味は以下になります

• keys_column_row: MySQLのカラム名に相当する部分の行数(デフォルト: 1)
• start_row: MySQLのデータ部分の開始行数(デフォルト: 2)

URLの例は以下のようになります

https://script.google.com/macros/s/AKfycbwEmyz0Y2IUiN-EPqLoFoaUbQ5z5fAcnUWlGAdhr8ujAUu4Dizl74NKqkKxK6TqKWTuSg/exec?keys_column_row=1&start_row=2

開発環境

今回の環境を構築するために使用したツールやライブラリは以下になります

• CDN
• Github Pages
• CI/CD
• Github Actions
• Database
• Google Spread Sheet
• Google Apps Script(Web API)
• typescript
• clasp
• webpack
• babel

またマスターデータの場合におけるシステム構成のイメージは以下のようになります

モチベーション

ホームページやブログやポートフォリオなどのwebサイトを作成する時に大した件数のデータ量になるわけでもないし頻繁に更新されるわけでもないwebサイトなのにデータベースを用いて開発・運用するというケースが多くあります。(データ件数の目安は50000件以下かどうか)
また、ゲームやWebサービスにおいてマスターデータの配信を行いたいケースというケースが多くあります。
Wordpress や Rails などのツールやWebフレームワークを用いた場合、標準でMySQLなどのデータベースを使用されますがこれによりデータベースの運用・管理コストやサーバー費用というのが高くなってしまう要因となります。
本来このような場合において実現したいことというのは以下の内容になります。

1. Excel や GoogleSpreadSheet にてデータを作成・管理
2. Webサイトやアプリの中で静的なJSONデータとして取得

そしてこのようなことを実現し、さらにCDNで配信することにより、

1. 運用・管理コストの低下
2. サーバーの運用費用の低下(0円も可能)
3. 高速で表示
4. 大量アクセスへの負荷耐性が高い

というメリットを含んだWebサイトを作成することができます。
今回、以上のような要件を全て満たすような環境が構築することができそうであることがモチベーションです。

解説

Google Apps Scriptについて

Google Apps ScriptとはGoogleが提供しているプログラミング環境です。GAS（ガス）という略称でも呼ばれています。
Googleの各種サービスと連携したり、拡張したりすることができるものがGASになります。

Google Apps Scriptでできること

• webアプリケーション(WebAPI, Webサイトなど)として公開
• SpreadSheetの操作(データの挿入・削除・編集など)
• イベントハンドリング(SpreadSheetのデータを編集したら実行されるスクリプトなど)

など

詳細についてはこちら

clasp

本来、GASの作成は以下のような専用のEditor上で入力して管理します。

claspとはGASのスクリプトをローカルで管理、操作できるGoogle謹製のCLIツールになります。(実態は AppsScript WebAPIをうまく活用していたもの。そのためWebサイトとして同様の操作を行うようなものを作成することは可能)
また、一般的にはGASは.gsファイルというJavascriptっぽい専用の言語ファイルにて開発を行います。
しかし、最新のClaspでは Javascript(.js) および TypeScript(.ts) で記述されてい流物でも開発することが可能となっています。
今回のサンプルではTypeScriptのみで開発を行いました

claspの基本的な使い方

claspは以下のコマンドでインストールできます

npm install -g @google/clasp

そして、使用可能な状態にするために以下のコマンドを用いてGoogleアカウント認証を行います

clasp login

以下のようにすれば、プロジェクトの初期化をすることができます。

clasp create --title title

claspのプロジェクトでは基本的に .clasp.jsonファイルとappsscript.json ファイルで管理(制御)しているので、これらのファイルがあれば開発ができます。
.claspignore ファイルはGASの内容をDeployする時に参照したくないファイルを指定する時に使用します。

.clasp.json ファイルではスクリプトファイルとappsscript.jsonファイルが置いてある場所(rootDir)やscriptIdや紐づいているprojectIdのが指定されています。
appsscript.json ではGAS用のpluginやpermission、公開範囲の指定などの情報を記述しています。

参考

• GAS のGoogle謹製CLIツール clasp

webpack + babel

GAS(clasp)の開発においてはスクリプトファイルを分割したり、npmパッケージなどのライブラリの使用、dotenvなどの開発における環境変数の適用といったことを利用することができません。
そこで、これらを使った開発を可能にするためにwebpack(とついでにBabel)を使って開発します。
これらを活用することで一般的なJavascript, Typescript環境でのの開発に近い環境下でGASの開発を行えるようにしています。
webpack + babel でGASのプロジェクトをBuild可能な状態にしている設定はwebpack.config.js と .babelrcを参照してください。

参考

• clasp + webpackでGAS(Google App Script)のコードをTypeScriptで書く
• GAS を npm パッケージ + Webpack + TypeScript で開発する

webapi deploy

各種、拡張させたコマンドについてはpackage.json 内の "scripts":{} の部分を参照してください。
上記webpackによるBuildはwebpackコマンドで行います。
Buildして生成されたファイルをclasp push コマンドでGASのエディタに反映させて、clasp deploy で「ウェブアプリケーションとして公開」しています。この時新しいdeployId が自動的に生成されます。

参考

• 今から10分ではじめる Google Apps Script(GAS) で Web API公開

webapi GET Request

前説

ゲームやサービスを作成した時、よくマスターデータを作成します。(マスターデータ: ゲームだとアイテムやキャラクターやモンスターの情報にあたります)
マスターデータは基本的にExcelなどで作成されます。
そして、作成されたマスターデータをアプリ内で使用するためにはJSONなどに変換し、それをアプリ側でダウンロードすることで使用しています。

マスターデータの管理構成について

• マスターデータはその特徴より頻繁に更新されるものではありません。そのため静的ファイルとして配信することが望ましいため、Webサーバーにて配置するのではなくCDNサーバーにて配信されることの方が望ましい。
• マスターデータは基本的に Excel や Google Spread Sheet に作成される
• Google Apps Script(通称 GAS)を使用してWebAPIとして後悔することが可能。そのため、Google Spread Sheet と GAS を連携することで Google Spread Sheet の内容をJSONに変換し、WebAPIとしてデータを取得することができる。

Webサーバーのサービス例

• AWS EC2
• firebase functions
  など

CDNサーバーのサービス例

• AWS Cloudfront
• firebase hostings

など