サービス間連携で便利なIFTTTですが、Webhookなどのログを記録する際にスプレッドシートへタイムスタンプを残す際の日時フォーマットが汎用的なものではなく、スプレッドシート側で日時として認識できずにその後のグラフ化などに支障があるため、汎用的に使える日時書式に書き換える方法を編み出したのでその知見を残しておきたいと思います。
課題
IFTTTのイベント発生タイムスタンプ書式 {{OccurredAt}} で書き出される日時
May 20, 2018 at 03:30PM
がスプレッドシートで日付として認識できない(DATEVALU関数で取り扱えない)
解決方法
出力の" at "を境に日付部分と時間部分に別けた上で、日付をGOOGLETRANSLATE関数に食わせて日本語に変換
May 20, 2018 → 2018年5月20日
これでめでたくスプレッドシートが日時として認識し、DATEVALUE関数に食わせる事ができました。
数式と出力例
| 数式 | 出力例 |
|---|---|
| {{OccurredAt}} | May 20, 2018 at 03:30PM |
| =GOOGLETRANSLATE(left("{{OccurredAt}}",find(" at ","{{OccurredAt}}")),"en","ja") | 2018年5月20日 ※1 |
| =RIGHT("{{OccurredAt}}",len("{{OccurredAt}}")- find(" at ","{{OccurredAt}}")-3) | 03:30PM ※1 |
| =DATEVALUE(GOOGLETRANSLATE(left("{{OccurredAt}}",find(" at ","{{OccurredAt}}")),"en","ja"))+TIMEVALUE(RIGHT("{{OccurredAt}}",len("{{OccurredAt}}")- find(" at ","{{OccurredAt}}")-3)) | 2018/05/20 15:30:00 ※2 |
| ※1 GOOGLETRANSLATEにかけただけだと書式の変更はかけられない。 | |
| ※2 DATEVALUE、TIMEVALUE関数に食わせる事でシリアル値となり、書式設定が可能になります。 |
具体的なIFTTT上での設定数式
出力例
| {{OccurredAt}} | {{EventName}} | {{Value1}} | 日付 | 時間 | 日時 |
|---|---|---|---|---|---|
| May 20, 2018 at 03:30PM | Temp_push | 24.82 | 2018年5月20日 | 03:30PM | 2018/05/20 15:30:00 |
{{EventName}}と{{Value1}} はWebhookでPOSTしているイベント名とJSONで送っているvalue情報です。
##背景
マイコンボードのArduinoから測定した温度をIFTTTのWebhookを通してスプレッドシートにロギングしていく仕組みを作っていました。
センサーであるArduino自体には時間情報を持っていないため、データを受け取った処理側で時間情報を書き込む必要がありました。
同じ課題を解決しているGithubの英語ユーザーによるIFTTTDate.mdがありましたが、これではなぜか月の英語表記を日時解釈することがスプレッドシートの日本語環境ではできないため、苦肉の策として翻訳関数であるGOOGLETRANSLATEをかますことで解決できた。という裏技的な解決方法ではあります。
成功したときちょっと笑ってしまった。
##注意点
翻訳関数という曖昧な仕組みを利用しているため利用はご自身の判断でお願いします。
ログに数式が入りまくっているという気持ち悪さはありますがリアルタイムでの可用性重視でこの形となっています。
適宜数式を取り除く処理をすれば良いかな。
p.s.初めてQiita投稿しました。