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

今回 [Google Spread Sheet](https://www.google.com/intl/ja_jp/sheets/about/)をデータベースとして使用し、その内容を[Google Apps Script(通称 GAS)](https://developers.google.com/gsuite/aspects/appsscript?hl=ja)を使ってWebAPIにすることでアプリの中で活用可能になる仕組み(テンプレート)を作成したのでその内容について紹介します。

## 作ったもの
* プロジェクトはこちらになります。[github-actions-examples](https://github.com/TakuKobayashi/github-actions-examples) (このプロジェクトは他にも [Github Actions](https://github.co.jp/features/actions) を活用した自動化の検証用としても使用しています。)
* サンプルとして作成したSpreadSheetのデータの内容はこちらを参照してください [github-actions-examples](https://docs.google.com/spreadsheets/d/1u5wMGFrVvvbhrl8I6rYjpDq5lZ33NF2cQwJ6XzGX8TA/edit?usp=sharing)
* 上記のサンプルデータをWebAPIとしてこちらよりJSONの形にて取得することができます [github-actions-examples-webapi](https://script.google.com/macros/s/AKfycbz6v1CdjnhPqDawNgBfyF1OkZ6AJ_nyl9NvNZLpwv-f/exec)
* 上記のWebAPIを[CDN(コンテンツデリバリネットワーク)](https://ja.wikipedia.org/wiki/%E3%82%B3%E3%83%B3%E3%83%86%E3%83%B3%E3%83%84%E3%83%87%E3%83%AA%E3%83%90%E3%83%AA%E3%83%8D%E3%83%83%E3%83%88%E3%83%AF%E3%83%BC%E3%82%AF)(今回は[Github Pages](https://pages.github.com/)) にデプロイしたものについてはこちらよりJSONを取得できます [mst.json](https://takukobayashi.github.io/github-actions-examples/mst.json)
* [Google Spread Sheet](https://www.google.com/intl/ja_jp/sheets/about/)の内容をJSONに変換しているソースコードはこちらを参照してください [GoogleAppScript](https://github.com/TakuKobayashi/github-actions-examples/tree/master/GoogleAppScript) (ここでは [webpack](https://webpack.js.org/) + [Babel](https://babeljs.io/) + [Typescript](https://www.typescriptlang.org/) にて [GAS](https://developers.google.com/gsuite/aspects/appsscript?hl=ja)のスクリプトを作成し、作成したものをBuildした後 [clasp](https://github.com/google/clasp/) でpush + deployしています。)
* [GAS](https://developers.google.com/gsuite/aspects/appsscript?hl=ja) の内容を自動的に反映させている [Github Actions](https://github.co.jp/features/actions) の内容についてはこちらを参照してください [google-app-scripts-deploy.yml](https://github.com/TakuKobayashi/github-actions-examples/blob/master/.github/workflows/google-app-scripts-deploy.yml)
* [Github Pages](https://pages.github.com/)に自動的にGASで作成したJSONを取得して、反映させる方法についてはこちらを参照してください [react-web-ci-cd.yml](https://github.com/TakuKobayashi/github-actions-examples/blob/master/.github/workflows/react-web-ci-cd.yml)

## 制限

### SpreadSheetの制限

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

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

この制約内

#### 参考
* []()

## ルール

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

```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のデータ構造に適用させたものになります。

![db_schema_rule.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/72596/f8d6baa9-dc6e-5c0f-2d04-5545dfe3ae07.png)

なお、上記の内容を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

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

![gas-master-data-system.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/72596/13645a96-f9f6-09f6-0afb-a1eae0646572.png)


## モチベーション

ホームページやブログやポートフォリオなどの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のデータを編集したら実行されるスクリプトなど)

など

詳細については[こちら](https://developers.google.com/gsuite/aspects/appsscript?hl=ja)

### clasp

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

![GAS_Editor.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/72596/8fa0869c-a5d3-0666-2d0a-dd80fa24a55b.png)

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

### claspの基本的な使い方

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

```sh
npm install -g @google/clasp
```

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

```sh
clasp login
```

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

```sh
clasp create --title title
```

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

![important_clasp_file.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/72596/51ba5c10-1692-59a0-c24a-163b13dbd9ba.png)

![GAS_important_file2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/72596/e08cdb32-c2ff-2efc-cd96-2c5517d2889d.png)

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

#### 参考
 * [GAS のGoogle謹製CLIツール clasp](https://qiita.com/HeRo/items/4e65dcc82783b2766c03)

### webpack + babel

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

#### 参考

* [clasp + webpackでGAS(Google App Script)のコードをTypeScriptで書く](https://wp-kyoto.net/clasp-gas-typescript-webpack/)
* [GAS を npm パッケージ + Webpack + TypeScript で開発する](https://qiita.com/shohei_ot/items/7b26461359068a192b96)

### webapi deploy

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

#### 参考
 * [今から10分ではじめる Google Apps Script(GAS) で Web API公開](https://qiita.com/riversun/items/c924cfe70e16ee3fe3ba)

### webapi GET Request






## 前説

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


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

* マスターデータはその特徴より頻繁に更新されるものではありません。そのため静的ファイルとして配信することが望ましいため、Webサーバーにて配置するのではなく[CDN](https://www.idcf.jp/words/cdn.html)サーバーにて配信されることの方が望ましい。
* マスターデータは基本的に `Excel` や [Google Spread Sheet](https://www.google.com/intl/ja_jp/sheets/about/) に作成される
* [Google Apps Script(通称 GAS)](https://developers.google.com/gsuite/aspects/appsscript?hl=ja)を使用してWebAPIとして後悔することが可能。そのため、[Google Spread Sheet](https://www.google.com/intl/ja_jp/sheets/about/) と [GAS](https://developers.google.com/gsuite/aspects/appsscript?hl=ja) を連携することで [Google Spread Sheet](https://www.google.com/intl/ja_jp/sheets/about/) の内容をJSONに変換し、WebAPIとしてデータを取得することができる。

##### Webサーバーのサービス例
* AWS EC2
* firebase functions
など

##### [CDN](https://www.idcf.jp/words/cdn.html)サーバーのサービス例
* AWS Cloudfront
* firebase hostings
* 
など