こんにちは、おかちゃんせんせいです！
少し前の話になりますが、
GoogleフォームとGoogleスプレッドシートを連携したデータを基にして、請求書を作成するGASを組みました。
その際に、苦戦してしまった２つのポイントがありますので、
その２点について備忘もかねてブログに残します。
目次
つまずきポイント１_なぜかGASで取得した時刻がズレてしまう
この件については原因を特定するために、かなり時間がかかってしまいました。
最終的にはこちらの記事を発見したことで、原因と対策が判明して問題が解決できました。
上記ブログによると、結論どうやらスプレッドシートのバグのようでw
スプレッドシートのタイムゾーンと、スクリプトエディタのタイムゾーンがズレていると、スプレッドシート上で表示されている時刻と、GASで取得したときの時刻がなぜかズレてしまう現象が生じてしまいます。
自分のケースでは、Googleフォームで作成したスプレッドシートで37分時刻がズレてしまう現象が起きていました。
この37分のズレの正体は、Googleフォームで作成したスプレッドシートの場合、スプレッドシートのタイムゾーンが東ティモールの首都Diliになってしまっていることが原因。
日本と同じく世界標準時間+9時間の地域ではありますが、
日本の方が厳密には【37分】分だけ緯度が大きいので、その分だけズレてしまうようです。
スプレッドシート上の時刻と、GASで取得したときの時刻がズレてしまうときには、スプレッドシートのタイムゾーンとスクリプトエディタのタイムゾーンを同じにする。
スプレッドシートのタイムゾーンを確認する
もし居住国の標準時間ではない場合には変更する。
スクリプトエディタのタイムゾーンを確認する
もし居住国の標準時間ではない場合には変更する。
つまずきポイント２_hh:mm形式のデータから経過時間を計算する
Googleフォームで開始時刻、終了時刻、休憩時間を作成して、稼働時間をGASで計算しようと試みました。
すると、単純に下記のような式では意図した結果（3:00:00=3時間）は得られず・・・。
const workingTime = [終了時刻（14:00:00）] - [開始時刻（10:00:00）] - [休憩時間（1:00:00）];上記の式の場合、スプレッドシートの見た目的には時刻だけの計算ですが、
GASで計算する場合には、
1899/12/30 14:00:00 – 1899/12/30 10:00:00 – 1899/12/30 01:00:00
という内容で年月日を含んだ状態で計算されます。
つまり、
終了時刻 – 開始時刻
で年月日は相殺されますが、
休憩時間では年月日が含まれるので望む結果を得られません。
ちなみに、Googleスプレッドシートでのシリアル値0は
1899/12/30 00:00:00
となります。
シリアル値：日時を計算処理するために格納されている数値のこと
ただ、GASで数値として取得するとなぜか-2209194000000になります。。。
じゃあ、GAS上での日付データが0となる日付というと、
1970/01/01 9:00:00
でした。
1970/01/01はUNIX時間の起点となる日付のようで、シリアル値とは異なります。
その知見がなかったので、ずっとシリアル値で考えてしまい迷宮入りしてしまった、というわけですw
解決策はいろいろあるとは思いますが、
私はシンプルに四則演算でできる方法で解決しました。
まず、休憩時間を下記のように日付 – 日付で計算します（ミリ秒）。
const restTime = 休憩時間 - new Date("1899/12/30 00:00:00");その上で、開始時刻と終了時刻から経過時間を計算して、さらに先ほど計算した休憩時間（ミリ秒）を減算して、稼働時間を算出します。
ただ、計算で得られる結果はミリ秒（1秒 = 1,000ミリ秒）なので、時間・分・秒に変換したい場合には除算する必要があるので要注意です。
const workingTime = ((終了時刻 - 開始時刻) - restTime);
【GAS】Googleフォームで回答したデータから勤務時間を計算し、[h]:mm形式に変換する
- 時刻を取得する場合には、スプレッドシートとスクリプトエディタのタイムゾーンを一致させる。
- 休憩時間を含めた稼働時間を計算する際には工夫する必要がある。
↓ 今日も１日ポチッと ↓
↑ いつもボタンクリ応援・口コミ応援ありがとうございます ↑
