【IFTTT と Google スプレッドシート】日付({{OccurredAt}})を時間データとして使う方法

スマートホーム

結論を先に書きますと…
IFTTT の『THEN THAT』の Google スプレッドシート画面、『|||』をセルの区切りとして使用して記述するテキストボックス欄に、『{{OccurredAt}}』の代替として

=DATEVALUE(SUBSTITUTE(GOOGLETRANSLATE(LEFT("{{OccurredAt}}",FIND(" at ","{{OccurredAt}}")),"en","ja")," ",""))+TIMEVALUE(RIGHT("{{OccurredAt}}",LEN("{{OccurredAt}}")-FIND(" at ","{{OccurredAt}}")-3))

を入力すると、IFTTT の日付の記述『August 18, 2024 at 07:00PM』を Google スプレッドシート上でスプレッドシート上で扱える形式にすることができます。


IFTTT、便利ですよね。
こういうサービスがあるのは本当にありがたいです。
ただ、ある時を境に有料じゃないとあんまり使えなくなっちゃったこともあり、一時は退会していました。
今はまた課金しています。
『Pro』プランですが、年額で $34.99 か~!
今のレートだと5,000円以上。なかなかしますねー。
仕方ないので使い倒したいですね。

IFTTT でなにかの『IF』(MacroDroid でいうところの『トリガー』)を実行した後に行う『THEN THAT』(MacroDroid でいうところの『アクション』)に、『Google スプレッドシート』を指定することができます。
『Google スプレッドシートの特定のファイルにある、一番最初のシート(シートタブの一番左)に、行を追加する』という『THEN THAT』を作れるんですが、それがなにかのログとかを取るのにとても便利です。
『{{OccurredAt}}』という文字列を入れると、実行したときのタイムスタンプを記録する事もできます。

ただ問題なのが、このときに Google スプレッドシートのタイムスタンプの形式が、IFTTT 独自の書式『August 18, 2024 at 07:00PM』のようにと記載されてしまうので、これを Google スプレッドシート上でシリアル値として使用できないんですね。
シリアル値として利用できるということは、日付同士の足し算引き算とか、グラフに使うとか、そういうことができるということです。
「できなくていい」と思ってらっしゃるなら、こんなページまで来てくださらないと思いますので…。

GAS を使う方法もあるみたいなんですが、(私は)気持ち的に面倒くさいです。
関数でなんとかできるならそうしたい。
ググったり Copilot さんに聞いてみたりすると、

IFTTTからスプレッドシート書き出し時の日付タイムスタンプのフォーマット修正 - Qiita
サービス間連携で便利なIFTTTですが、Webhookなどのログを記録する際にスプレッドシートへタイムスタンプを残す際の日時フォーマットが汎用的なものではなく、スプレッドシート側で日時として認識でき…

こちらページがヒットします。『Qiita』のサイトです。
以前私もこのページを見つけて、この通りに記述したらちゃんとシリアル値として使えるようになったので、とてもありがたかったです。
ただ、このページは2018年のもの。
かなり前になってしまいました。
そして、現在このまま記述してもエラーになってしまいます。
考えた結果、

=DATEVALUE(SUBSTITUTE(GOOGLETRANSLATE(LEFT("{{OccurredAt}}",FIND(" at ","{{OccurredAt}}")),"en","ja")," ",""))+TIMEVALUE(RIGHT("{{OccurredAt}}",LEN("{{OccurredAt}}")-FIND(" at ","{{OccurredAt}}")-3))

(このページの上の方にあるものの再掲です)
と記述することで、ちゃんとシリアル値として扱えるようになりました。
一応、以下に解説を載せます。

『DATEVALUE』関数と『TIMEVALUE』関数の2つの部分に分けて見てみます。
日付のデータというのは、1日を『1』として1900年1月1日(くらい)からの経過日数を数えている感じです。
『1』が1日なので、『0.5』は半日分のお昼の12時です。

余談なんですが、Excel で『1』と入力して日付形式にすると『1900/1/1』となります。
同じことを Google スプレッドシートでやると『1899/12/31』となります…。
でも、最近の日付のシリアル値は同じ…?
よくわからんけど…まぁ、いいや。

まず、わかりやすい後半の『+』以降、『TIMEVALUE』関数を使っている部分です。

+TIMEVALUE(RIGHT("{{OccurredAt}}",LEN("{{OccurredAt}}")-FIND(" at ","{{OccurredAt}}")-3))

この部分は上の『Qiita』のサイトにあるものから変わっていません。
『{{OccurredAt}}』の代わりに『August 18, 2024 at 07:00PM』という文字列が入っていると思っていただければわかりやすいかと思います。
(そのまま Google スプレッドシートで確認できるように、最初の『+』を『=』に変えました)

=TIMEVALUE(RIGHT("August 18, 2024 at 07:00PM",LEN("August 18, 2024 at 07:00PM")-FIND(" at ","August 18, 2024 at 07:00PM")-3))

『RIGHT』関数で『August 18, 2024 at 07:00PM』から文字を切り出します。
『LEN』関数で『August 18, 2024 at 07:00PM』の文字数(26文字)を取得し、『August 18, 2024 at 07:00PM』の『 at 』までの文字数を『FIND』関数で取得します(16文字)。
16文字目というのは『August 18, 2024 at 07:00PM』の『 at 』の最初の半角スペースまでなので、残りの『at 』の3文字もいらないため、26文字 – (16文字 + 3文字) = 19 となり、『RIGHT』関数で19文字以降を取得すると『07:00PM』だけが残ります。
Excel だとこの『07:00PM』は日時として扱えないみたいなんですが、Google スプレッドシートだと大丈夫みたいなので、この文字を『TIMEVALUE』関数でくくってあげると、『0.791666666666667』という値になります。
これで小数点以下の時間の部分は解決です。

残りの部分について。
上の『Qiita』のサイトにあった前半の部分は

=DATEVALUE(GOOGLETRANSLATE(LEFT("{{OccurredAt}}",FIND(" at ","{{OccurredAt}}")),"en","ja"))

です。
これの『{{OccurredAt}}』の部分を、わかりやすいように『August 18, 2024 at 07:00PM』に置き換えると、

=DATEVALUE(GOOGLETRANSLATE(LEFT("August 18, 2024 at 07:00PM",FIND(" at ","August 18, 2024 at 07:00PM")),"en","ja"))

となります。
『TIMEVALUE』関数の部分と同じように、『LEFT』関数で『August 18, 2024 at 07:00PM』の『 at 』までの文字を抜き出します。
『August 18, 2024』という文字列が抜き出せるので、それを『GOOGLETRANSLATE』関数にかけます。
『GOOGLETRANSLATE』関数とは、指定したテキストを翻訳してくれる関数です。
『”en”,”ja”』の部分から、英語 → 日本語に翻訳するように指示していることがわかります。
『August 18, 2024』を日本語に翻訳すると、『2024 年 8 月 17 日 』。

はい、ここです。
なんかスカスカしているなーと思いましたよね?
そうなんです、スカスカしているんです。
『2024 8 17 』、水色に塗ってある部分に『半角スペース』が入っています。
どうやら、この半角スペースが変なところにまんべんなく入っていることで、『DATEVALUE』関数が効かなくなっているようなんです。
なので、この半角スペースを抜き取ってあげます。
『TRIM』関数だとはじめと終わりのスペースしか取ってくれないので、この場合は『SUBSTITUTE』関数で『 』(半角スペース)を『』(なにもなし)に置換します。
置換したものを『DATEVALUE』関数にかけるので、

=DATEVALUE(SUBSTITUTE(GOOGLETRANSLATE(LEFT("August 18, 2024 at 07:00PM",FIND(" at ","August 18, 2024 at 07:00PM")),"en","ja")," ",""))

となり、先ほどの『TIMEVALUE』の部分とくっつけて

=DATEVALUE(SUBSTITUTE(GOOGLETRANSLATE(LEFT("August 18, 2024 at 07:00PM",FIND(" at ","August 18, 2024 at 07:00PM")),"en","ja")," ",""))+TIMEVALUE(RIGHT("August 18, 2024 at 07:00PM",LEN("August 18, 2024 at 07:00PM")-FIND(" at ","August 18, 2024 at 07:00PM")-3))

となります。
IFTTT に貼り付けられるように、テキストの部分を『{{OccurredAt}}』に変更すると、

=DATEVALUE(SUBSTITUTE(GOOGLETRANSLATE(LEFT("{{OccurredAt}}",FIND(" at ","{{OccurredAt}}")),"en","ja")," ",""))+TIMEVALUE(RIGHT("{{OccurredAt}}",LEN("{{OccurredAt}}")-FIND(" at ","{{OccurredAt}}")-3))

(このページの上の方にあるものと同じです)
となります。
これで、日付部分・時間部分、どちらも解決ですね。

長くなってしまいましたが、これを IFTTT の Google スプレッドシートの『|||』をセルの区切りとして使用して記述するテキストボックス欄に書くと、Google スプレッドシートに日付のシリアル値が記録されるようになります。
あとは、書式設定で書式を『日時』とかに変えると『2024/08/18 19:00』などのように表示できるようになりますし、他の日付と足し算引き算したりすることができるようになります。

セルの数式部分には

=DATEVALUE(SUBSTITUTE(GOOGLETRANSLATE(LEFT("August 18, 2024 at 07:00PM",FIND(" at ","August 18, 2024 at 07:00PM")),"en","ja")," ",""))+TIMEVALUE(RIGHT("August 18, 2024 at 07:00PM",LEN("August 18, 2024 at 07:00PM")-FIND(" at ","August 18, 2024 at 07:00PM")-3))

のように長い長い数式が入っています。
それが「やだ、カッコ悪い…」と思うのであれば、GAS とかでなんとかかんとかするのがいいんだと思います。
私はこれでいいかな、と。

いやぁ、これを書くにあたって IFTTT の料金を見て、毎年これだけ払っているんだということがわかったので、これからはもっとたくさん使おうと思いました。
円安だから高くなったの?
でも、もともとドルで記載されてたし、気づいたら $24.99が $34.99になっていたので、これからはもっとたくさん使おうと思いました(再掲)。

長々とありがとうございました。

コメント

タイトルとURLをコピーしました