NOTICE
この記事にある、Google Spread Sheet APIは、v3になります。 v3は、2021/08/02時点でshut downとなっていますので、v4への移行での実装を進めてください。 この記事での実装をしても、正しい結果が得られません。 (2021/08/17 追記) v4への移行についてのGoogle Cloud公式ドキュメントは、こちらから。 v4でのFileMakerからのデータ取得については、こちらに記事を書きましたので、参考にしてください。
データだけ欲しいに対応する
FileMakerからAPIを使う機会は本当に増えましたが、実際のデータを取得するまでの手段が面倒臭くて最近はもうお腹いっぱい。。。な感じです。
なので、
「更新しないからデータだけくれ」
という場合に十分な実装方法を残します。
わりと他の言語では当たり前のように書かれている、Google SpreadSeetのスプレッドシートIDを流用する方法です。
ちなみに、更新を伴う処理には、やっぱりGoogleのClientIDとAPIKeyが必要になり、めんどくさいOAuth2.0認証などの設定や処理を実装します。が、
Claris Connectやn8nなど、今時はもう少し楽に実装できるツールもあります。
FileMakerバージョン
この記事で説明しているFileMakerのバージョンは、ver.16以降で機能します。
使用するスクリプトステップである"URLから挿入"はver.12以降で利用可能ですが、受信したJSONデータを加工する場合、ver.16以降で提供されているJSON関数を利用しているため、ver.15以下では何かしらの工夫した実装が必要になります。
Google Spread Sheet
一般的なスプレッドシートとその見え方
例えば、取引会社とのデータのやり取りで「データは開示するけれど、データの更新や削除は行わせない」というパターンです。
取引会社や、公の施設利用に関する予約状況の開示とか、中の人が更新するやつですね。
今回、説明用に単純なデータを用意しました。
これを、ウェブ公開すると以下のように表示のみでブラウザに表現されます。
文字列のコピーも可能ですが、入力不可なのでコピーしかできません。
このデータを、FileMakerに持ってきます。
Google Spread Sheetの設定
検索すれば色々出て来ると思いますので、ここではざっとやり方の説明のみをします。
1.公開するシートのメニューで、ウェブ公開を選択する
2.公開する
3.URLバーに表示されているスプレッドIDを記録する
2のウェブ公開で表示されるURLは使わず、こちらのURLバーもにあるスプレッドシートIDを使うので、お間違えなく。
ここまでが、準備です。
FileMakerの実装
テーブル設計
FileMaker側の設計についてです。
FileMakerには、データを受信して、受信したデータをFileMakerでテーブルに保存する、という形で実装していきます。
ぶっちゃけ、スプレッドシートIDをスクリプトにベタ書きするのであれば、データ受信に関するフィールドはなくても構いません。
が、やはりアクセスするシートをスクリプトに埋め込んでしまうとスプレッドシートが非公開になった時に修正する手間が増えてしまうので、何かしらのフィールドに保管しておくことにします。
今回は、"googlespreadsheetdata"という受信したJSONデータを保存するフィールドと、スプレッドシートIDを保存する"spreadID"というフィールドを作成しました。
一つのスクリプト内で受信データを処理するだけであったり、FileMakerへ展開・保存したデータは不要、ということであれば、受信データフィールドは不要です。
ここでは受信データを確認するために、フィールドを作成しています。
"fixedSpreadSheet"は、受信したJSONデータを見やすく整形するためのみのフィールドです。
検証用は不要、とのことであれば、このフィールドも不要です。
ボタンの設置
１行スクリプトをボタンに貼り付けます。
エラー処理などごちゃごちゃしている部分は省いていますが、何かしら受信できなかった時のエラー処理は実装しておいた方がいいと思います（が、省く）。
この１行スクリプトで重要なのは、引数に"alt=json"を付加することです。
これがないと、JSONで取得できません。
URLは、そのまま使えます。
https://spreadsheets.google.com/feeds/list/{スプレッドシートID}/od6/public/values?alt=json
という形です。
→この形で賄えないケースがあります。このページの追加情報を参考にしてください。
テスト受信してみる
spreadIDフィールドにコピーしておいたスプレッドシートIDを設定して、ボタンをクリックして受信してみます。
こんな受信結果出ました。
実際のデータは、"feed.entry"以下の配列データになります。
また、データ件数、シート名、最終更新日もついてきますので、最新更新日やデータ件数の確認用に活用できます。
受信データを格納するテーブルの作成
受信したJSO形式のデータを、テーブルに保存していきます。
作成するフィールドは、スプレッドシートの項目名（１行目）と同じ分だけ用意します。
名前を一致させる必要はありません。
実データのJSON形式
１行分のデータは、以下のような形式です。
"feed.entry[n].[名前空間]＄[フィールド名].＄[データ形式]"
名前空間は、googleが決めたものなので、「こういうものだ」と思っててOKです。
（XMLなどで操作するときは、namespaceのアドレス指定をしないといけないと思いますが、今回はJSONなので決めうちでOKです）
"n"は、0(ゼロ)オリジンの行番号です。
受信データの展開
受信データを展開するスクリプトです。
総件数分LOOPを回して、受信したデータを１行ずつ処理しています。
ここまでが、FileMaker側の実装になります。
実行してみる
FileMakerに受信したJSONデータを、スクリプトを実行して展開してみましょう。
↓、Google SpreadSheetにある４件分、正常にFileMakerに展開できていることがわかります。
あとは、FileMaker上で煮るなり焼くなり好きなだけデータを加工します。
Google SpreadSheetには反映しないので、自分仕様にデータの加工が行えるため、安心して操作できますね！
注意点
Google SpreadSheetをウェブ公開してもらわないと話が進まない（実装できない）ので、取引先などでウェブ公開がされているかどうかを確認するのがまず準備の最初になります。
ウェブ公開に限らずURLの公開では、公開しているシートが悪意ある人にみられてしまうこともありますので、リスクがゼロではないことをきちんと納得した上でご利用ください。
追加情報
（更新:2021/07/26）
以下のケースでは、grid id(シートID)を指定する方式で取得しないと、正しくデータが取得できません。
・Google Formの回答として出来上がったSpread Sheet(grid idが自動で割り振られるため)
・同じGoogle Spread Sheetのファイル内の２シート目以降
この場合は、grid idを指定した、以下の方法で取得ができます。
https://spreadsheets.google.com/feeds/list/{スプレッドシートID}/{シート番号}/public/full?alt=json#gid={grid id}
gidは、URLで確認できます。
シート1枚目がgidが0の場合でも書き方は同様になりますので、書き方を統一する場合は、この追加情報の書き方も使ってみてください。