GoogleスプレッドシートのデータをJSONで取得する【GAS】

Google Apps Scriptにてウェブアプリを作る場合、通常doGet()でHTMLを表示し、HTML側からスプレッドシートのデータをリクエストした時に、スプレッドシートデータを普通は二次元配列形式でデータを受け取ります。その後、配列を例えば ss[2][3]といった具合に配列の場所を指定して、データを処理します。

しかし、この手法だと行を指定する部分は数字でも良いのですが、列を指定する部分まで何列目として指定しなければならず、少々面倒です。列指定はタイトルの文字で指定できたら楽ですよね。そこで、スプレッドシートのデータをJSON形式でHTML側へ送ってあげて、JSONデータとして扱えば、HTML側の処理が少し楽になります。

今回はこれに加えて、スプレッドシートのデータをJSON形式として、Web APIとして配信もしてみたいと思います。

今回使用するスプレッドシート

スプレッドシートサンプル

今回使用するスプレッドシートのサンプルには3つのプロジェクトが入っています。それぞれ、JSONでデータを処理するサンプルと、JSONデータを配信するサンプルとなっています。

注意事項

今回のスプレッドシートを使用する為には、スプレッドシートをコピーした後に一度セットアップが必要です。このセットアップは2つのプロジェクトがあるので、それぞれセットアップを実行が必要です。

  1. スプレッドシートを開くとメニューに「JSON配信セットアップ」と「JSON処理セットアップ」が表示されます。
  2. それぞれの中にある「スタート」を実行してセットアップは完了です。
  3. この処理で、それぞれのプロジェクトプロパティにスプレッドシートのIDが格納され、スクリプト内で利用されます。

図:セットアップを実行しましょう

これまでの処理方法

これまでは、スプレッドシートのデータをガッツリ取得してそのまま、HTML側へ渡してあげてました。その為データは2次元配列の形式で取得する事になります。二次元配列ですので、データはdata[行番号][列番号]で指定する事で、そのセルの値を取り出す事が出来ます。

GAS側のサンプルコード

単純に取得したデータを最後にJSON.stringifyにて返してます。

HTML側のサンプルコード

HTML側表示時に、sheetdataを実行して返ってきた値をjson[0][3]として指定し、値を取り出しています。今回はpointの列の値を表示しているので、4列目(スクリプトでは0から数えるので3を指定する)の値をこれで取り出せています。pointが4列目にあることを知っていなければならないのです。ここがちょっと面倒な部分ですね。

JSONで処理する方法

JSON形式で返して上げてそこから値を取り出してみましょう。JSONの場合のHTML側での値の取得方法は、json[0].pointにて値を取り出す事が可能です。ですので列番号ではなく列の名前で取得が出来るのが特徴です。addressの列の値が欲しいならば、json[0].addressとなります。また[0]はレコードの番号なので、2レコード目ならば、json[1].addressとなります。

GAS側のサンプルコード

取得したデータをJSON形式に加工してあげています。そのデータをJSON.stringifyにてHTML側へ渡しています。その為別途タイトル行のデータはtitleとして取得させています。これがポイントです。

HTML側のサンプルコード

HTML側表示時にbackjson()を実行して、JSONデータを取得しています。データの1行目のpointデータは、json[0].pointとして直接取得出来ます。pointの列を直接列の名前で指定が出来るので楽ちんですね。コードがわかりやすくなります。こうする事で、今後のGoogle Apps Scriptでのウェブアプリケーション作成が捗ります。

ポイント

特にスプレッドシートの列を入れ替えたりした場合には、コードの修正が不要になる点と、列を追加しても追加部分の対応だけコードを修正すれば済むので、列の位置を意識する必要がないので、アプリを拡張しやすくなります。配列では全てのコードを修正しなければならないので、結構これが苦労します。

また、申請フォームのような場合、HTML側でJSON形式にするコードで整形してあげられれば、GAS側でデータ書き込み時に配列を組む時にも、コードがわかりやすくなります(データの追加などは配列形式でなければならない為)。

JSONで配信する方法

Google Apps Scriptでフォームを作らず、別の外部アプリケーションから利用出来るようにWeb APIとして配信する事も可能です。スプレッドシートのデータを他のアプリから参照がしやすくなるので、便利です。この場合、doGet()だけでなくdoPost()なども利用する事で、Web APIがより設計しやすくなります。

また、Web APIなので通常はdoGet()などに引数を渡して、特定の行でマッチするレコードの特定の値を返せ といった事も可能になります。詳細についてはまた別途doGetとdoPostの項目で取り上げたいと思います。今回は単純に全データを返すようにしています。

GAS側のサンプルコード

今回はHTML側が存在しません。doGetでアクセスした場合、HTML ServiceではなくContent Serviceクラスを使って文字列を出力する仕組みになっています。出力をする時に予め作っておいたデータをJSON.stringifyにてアウトプットしています。その為、backjson()の中では、JSON.stringifyで渡す必要はありません。

この時、.setMimeTypeにてJSONのMIME TYPEを指定する必要があります。JSONのMIME TYPE指定は「ContentService.MimeType.JSON」となります。

図:JSON形式でWeb APIとして配信するとこう見えます

SheetAsJsonを使ってみる

結構前からリリースされてる、綺麗に整形されたJSONファイルとして出力してくれるものが、SheetAsJsonです。使い方はとても簡単で以下のような手順です。現在はGithubでもメンテされています。

  1. スプレッドシートのスクリプトエディタ画面で、SheetAsJsonのコードを貼り付ける
  2. ウェブアプリケーションとして導入し、公開する
  3. ウェブアプリケーションのURLにGETパラメータをつなげてURLを叩く
  4. JSON形式でデータが返って来る

そのGETのパラメータですが、以下のようなURLを組み立てて送信します。公開URLにスプレッドシートのIDと対象のシート名をつなげて投げてあげると、JSONに変換されて返ってくるという前項とは少しタイプの違う仕組みですね。

https://script.google.com/macros/s/ウェブアプリケーションのID/exec?id=スプレッドシートのID&sheet=シート名

よって、ウェブアプリケーションとして導入をしただけでは、何もしてくれませんので、必ずパラメータを後ろにつなげて送ります。WebAPIのようなものですね。すると、JSONデータが返って来る仕組みです。これをNode.jsなどでfetchして、値を取り出すわけです。返ってくるデータは以下のような感じになります。

関連リンク

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)