[GAS] Google Spreadsheet のデータを FileMaker に自動で取り込む

2021年10月20日 10:32

Raspberry Pi に取り付けたセンサーで温度・湿度・気圧を Google Spreadsheet に記録する方法について書いた。

Raspberry Pi で温度・湿度・気圧を Google Spreadsheet に記録する２｜荒川仁志｜note
https://note.com/hitoshiarakawa/n/n43180b80bca8

私は 30 分ごとに記録をしている。となると記録データが１日 48 行、１週間で 336 行、１ヶ月で 1440 行となる。

データをあれこれ加工したりすることを考えると、Google Spreadsheet よりも FileMaker で Raspberry Pi のセンサーデータを扱いたい。

これは Google Apps Script（GAS）を使うことでできると分かったのでやってみた。

最初にまとめておくと以下のようになる。

【１】GAS で Google Spreadsheet のデータから JSON データを生成する。
【２】FileMaker Server 上の FileMaker アプリで１の JSON を取り込む。
【３】GAS で Google Spreadsheet のデータを削除する。

【２】で JSON を取り込む際に、【１】の処理は自動実行され、最新の JSON データが生成される。なので、【２】のFileMaker 側のスケジュールでよ JSON を取り込む作業と、【３】のデータ削除の２つをスケジュールとして設定すればいい。

＊＊＊

（１）Google Spreadsheet のシートが作成済みだとする。シート名を「log」とする。

各列は以下のようになっている（順に、タイムスタンプ、湿度、温度、気圧）。

Timestamp　Humidity　Temperature　Pressure

（２）新規に Apps Script を作成する。プロジェクト名は SpreadsheetToFileMaker とでもしておこう。

My Projects - Apps Script
https://script.google.com/home

（３）以下のようなスクリプトを記入、保存する。

function getData(id, sheetName) {
 var sheet = SpreadsheetApp.openById(id).getSheetByName(sheetName);
 var rows = sheet.getDataRange().getValues();
 var keys = rows.splice(0, 1)[0];
 return rows.map(function(row) {
 var obj = {}
 row.map(function(item, index) {
	obj[keys[index]] = item;
 });
 return obj;
 });
}

function doGet(request) {
 var data = getData('[Spreadsheet ID]', 'log');
 return ContentService.createTextOutput( JSON.stringify(data, null, 2) )
 .setMimeType(ContentService.MimeType.JSON);
}

[Spreadsheet ID] は、１の Spreadsheet の URL である https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxx/edit#gid=0 の、xxxxxxxxxxxxxxxxxxxx 部分だ。

（４）「上の青い Deploy ボタン＞New deployment」をクリック。

「Select type のギアアイコン＞Web app」をクリック。以下のように記入する。

・Description：Spreadsheet to FileMaker（任意）
・Execute as：Me
・Who has access：Anyone

Deploy をクリック。

Authorize access というボタンが表示されるのでクリック。Google アカウントを選択して認証する。

Web app の URL が表示されるのコピーして控えておく。

（５）試しに Mac の Terminal で以下のコマンドを実行すると、JSON 形式で Spreadsheet の内容が取得できるはずだ。

$ curl -L "[４の Web app の URL]"

[
 {
	"Timestamp": "2021/10/03 12:37:44",
	"Humidity": 37.8,
	"Temperature": 29.49,
	"Pressure": 1021.63
 },
 {
	"Timestamp": "2021/10/03 12:39:02",
	"Humidity": 37.83,
	"Temperature": 29.49,
	"Pressure": 1021.62
 },
 {
	"Timestamp": "2021/10/03 12:50:22",
	"Humidity": 38.18,
	"Temperature": 29.53,
	"Pressure": 1021.44
 }

これを FileMaker に読み込めばいいわけだ。

（６）ここから FileMaker での作業だ。FileMaker でファイルを作成した上で、Spreadsheet の内容を書き込むフィールドを作成しておく。私は１で書いたとおり以下のフィールドを作成した。

Timestamp
Humidity
Temperature
Pressure

（７）以下のようなスクリプトを作成する。

# Google Spreadsheet のデータを JSON で取得。変数に格納。
Insert from URL [ Select ; With dialog: Off ; Target: $result ; "[４の Web app の URL]" ; cURL options: "-L" ] 

# JSON の項目数を取得。
Set Variable [ $ItemCount ; Value: ValueCount ( JSONListKeys ( $result ; "" ) ) ] 

# レコードを作成ごとにカウントアップするカウンター。
Set Variable [ $i ; Value: 0 ] 

# JSON に項目数がある場合（JSON データが存在する場合）、JSON から順次レコードを作成していく。
If [ $ItemCount > 0 ] 
	# 
	Loop
		New Record/Request
		Set Field [ Table::Timestamp ; JSONGetElement ( $result ; "[" & $i & "]Timestamp" ) ] 
		Set Field [ Table::Humidity ; JSONGetElement ( $result ; "[" & $i & "]Humidity" ) ] 
		Set Field [ Table::Temperature ; JSONGetElement ( $result ; "[" & $i & "]Temperature" ) ] 
		Set Field [ Table::Pressure ; JSONGetElement ( $result ; "[" & $i & "]Pressure" ) ] 
		# 
		Set Variable [ $i ; Value: $i + 1 ] 
		Exit Loop If [ $i ≥ $ItemCount ] 
	End Loop
End If

このスクリプトを実行することで、Google Spreadsheet のデータを FileMaker に取り込むことができる。

（７）しかし、再び６のスクリプトを実行して FileMaker にデータを取り込んだ痕は、元の Google Spreadsheet のデータを削除する作業が必要だ。

新規に Google Apps Script を作成する。プロジェクト名は DeleteSpreadsheetData とでもしておこう。

My Projects - Apps Script
https://script.google.com/home

（８）以下のようなスクリプトを作成、保存。

function myFunction() {
 var spreadsheet = SpreadsheetApp.openById("[Spreadsheet ID]");
 spreadsheet.deleteRows(2, 60);
}

[Spreadsheet ID] は、１の Spreadsheet の URL である https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxx/edit#gid=0 の、xxxxxxxxxxxxxxxxxxxx 部分だ。

２行目から数えて 60 行削除するようにしている。私は Raspberry Pi のセンサーデータが１日 48 件ずつ記録されていくので、それ全部を削除するため余裕を持って 60 行削除することにした。

（９）Run で実行してみよう。初回は Authorization required というダイアログが表示される。「Review permissions」をクリックして、認証をする。

これで Spreadsheet のデータが削除されたはずだ。

（10）あとはスケジュール設定だ。

私は Raspberry Pi のセンサーデータが毎時０分および 30 分に生成される。そこと重ならないようにスケジュールする必要がある。もちろん、FileMaker の場合、スクリプトの実行を自動化するには FileMaker Server で動かす必要がある。

例えば、FileMaker のスクリプトを 2:10 に実行（Spreadsheet のデータが FileMaker に取り込まれる）。その後 2:20 に GAS で Spreadsheet のデータを削除するようにすればいい。

ただし GAS はデフォルトではスクリプトの実行時刻を細かく設定できない。２時から３時の間、のように幅がある。そこで、スクリプトを書き換えることで実行時刻を細かく指定できるようになる。８のスクリプトを以下のように書き換えよう。

// その日の2時20分にトリガーを設定（2時20分より前に実行）
function setTrigger() {
 var triggerDay = new Date();
 triggerDay.setHours(2);
 triggerDay.setMinutes(20);
 ScriptApp.newTrigger("myFunction").timeBased().at(triggerDay).create();
}

// その日のトリガーを削除する関数（setTriggerでトリガーを設定すると、消さないと残リ続けるため）
function deleteTrigger() {
 var triggers = ScriptApp.getProjectTriggers();
 for(var i=0; i < triggers.length; i++) {
 if (triggers[i].getHandlerFunction() == "myFunction") {
 ScriptApp.deleteTrigger(triggers[i]);
 }
 }
}

// トリガーを削除する関数の実行および実行したいスクリプト本体
function myFunction() {
 deleteTrigger();
 
 // ここに処理を書く
 var spreadsheet = SpreadsheetApp.openById("[Spreadsheet ID]");
 spreadsheet.deleteRows(2, 60);
}

スクリプトが実行されるたびに、その日の実行時刻が設定される仕組みだ。

この上でスケジュールを設定する。

（11）FileMaker Server のスケジュールの設定方法はここでは割愛する。GAS のスケジュール方法は以下のようにする。

スクリプトのページで「左の時計アイコン＞Triggers＞Add Trigger（あるいは create a new trigger）」をクリック。以下のように設定する。

・Choose which function to run：setTrigger
・Choose which development should run：Head
・Select event source：Time-driven
・Select type of time based trigger：Day timer
・Select time of day：1am to 2am（２時 20 分より前の時間帯を設定）

ただし、指定時刻になっても実行されなかった。GAS のスクリプトのページで「左の時計アイコン＞Triggers」で確認してみると、myFunction の Last run 部分が Disabled になっている。「i」アイコンをクリックすると、"This trigger has been disabled for an unknown reason." と表示された。

しかしこの問題は「左のギアアイコン＞General settings＞Enable Chrome V8 runtime」をオフにすることでエラーが発生しなくなる。

以上で、「Raspberry Pi でのセンサー計測 --> Google Spreadsheet へのデータ書き込み --> FileMaker Server へデータの取り込み --> GAS で Google Spreadsheet のデータをクリア」という一連の流れを作ることができた。

参考：
GASを使ってGoogleスプレッドシートからFilemakerにデータを持ってくる方法 - Qiita
https://qiita.com/unkonow/items/60fab55aac218155dde5

GASのdoGetとdoPostをcurlで試してみた - Qiita
https://qiita.com/aromanokarisu/items/ff3076a78335163d2f12

google apps script - Force cell to be interpreted as plain text? - Stack Overflow
https://stackoverflow.com/questions/38428448/force-cell-to-be-interpreted-as-plain-text

formatting - How to set a Google Docs Spreadsheet cell format to bare text? - Web Applications Stack Exchange
https://webapps.stackexchange.com/questions/33202/how-to-set-a-google-docs-spreadsheet-cell-format-to-bare-text

GASのgetActiveSpreadsheetでできる機能まとめ – Google Apps Script(GAS)を用いたシステム開発を支援
https://www.acrovision.jp/service/gas/?p=237

【コピペで使える】GASでスプレッドシートの行や列を削除してみる
https://for-dummies.net/gas-noobs/how-to-delete-rows-and-columns-for-spreadsheets-by-gas/

Google Apps Scriptの日毎のトリガーで時間をもっと細かく設定する - Qiita
https://qiita.com/sumi-engraphia/items/465dd027e17f44da4d6a

この記事が気に入ったらサポートをしてみませんか？