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
などのデータベースを使用されますがこれによりデータベースの運用・管理コストやサーバー費用というのが高くなってしまう要因となります。
本来このような場合において実現したいことというのは以下の内容になります。
-
Excel
やGoogleSpreadSheet
にてデータを作成・管理 - Webサイトやアプリの中で静的なJSONデータとして取得
そしてこのようなことを実現し、さらにCDNで配信することにより、
- 運用・管理コストの低下
- サーバーの運用費用の低下(0円も可能)
- 高速で表示
- 大量アクセスへの負荷耐性が高い
というメリットを含んだ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、公開範囲の指定などの情報を記述しています。
参考
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
が自動的に生成されます。
参考
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
- など
Author And Source
この問題について(GoogleAppsScript(Typescript)のみを使ってSpreadSheetの内容をJSONで取得・作成するWebAPIを作った話(後ほど記事更新)), 我々は、より多くの情報をここで見つけました https://qiita.com/taptappun/items/ae506d02ddef103a7acb著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .