はじめに
Googleスプレッドシートで以下のように2:11
のような形式で時間を記録していて、それをGoogleデータポータルで集計したいときに一工夫必要だったので残しておきます。
ここでは労働時間を集計したいとします。
これをそのままデータポータルに追加すると、労働時間はテキストと認識されてしまい、うまく集計することができません(下図左:時間の集計ではなく件数として集計されてしまっている)。
タイプを数値に設定してもうまくいきません(下図右:null)。
方法1:スプレッドシート側で数値に変換
スプレッドシート側に変更を加える方法です。
例えば、以下のようにします。
=ARRAYFORMULA(IF(F2:F1000,VALUE(F2:F1000)*24,""))
ポイントはVALUE(F2:F1000)*24
で、例えば2:11
を2.18
(≒ 2 + 11/60)のように時間単位の数値に変換できます。
あとはこれをデータポータルに再接続すれば、数値フィールドとして認識され、時間を集計することができます。
方法2:データポータルの計算フィールドを利用
方法1では、データソースであるスプレッドシートに手を加える必要があります。
データソースはそのままに、データポータル側で対処することもできます。
労働時間
は2:11
のような時間の文字列が入ったフィールドだとします。
このとき、カスタムの計算フィールドで計算式を以下のようにします。
HOUR(PARSE_DATETIME('%H:%M', 労働時間))
+ MINUTE(PARSE_DATETIME('%H:%M', 労働時間)) / 60
中身としては、PARSE_DATETIME('%H:%M', 労働時間)
で2:11
のような時間の文字列をDATETIMEにします。
そのDATETIMEからHOURとMINUTEで時間の部分(=2)と分の部分(=11)を取り出します。
分は時間に変換するために60で割ります(11/60≒0.18)。
時間の部分の数値(2)と分を時間に変換した数値(0.18)を足して、2:11
を時間に変換した数値(2.18)が得られます(方法1で説明した数値と合っていますね)。
これでデータソースに手を加えずに時間を集計することができました。
※形式の異なるデータが混じっていると下のようなエラーが出るかもしれません。
余談ですが計算フィールドにはグラフ固有のものとデータソースのものがあり、それぞれ異なる利点があります。
詳しくは下記を参照のこと。
おわりに
そもそもデータポータルを使わずスプレッドシートで十分ならば方法1だけ知っていれば大丈夫です。
とはいえデータポータルも何かと便利な場面があるので、この記事が誰かのお役に立てば幸いです。