自分を活かして 相手を活かして 今を活かす

【解決】GASでGoogleフォームから作成したスプレッドシートのデータを活用する際につまずいた2つのポイント

こんにちは、おかちゃんせんせいです!

少し前の話になりますが、
GoogleフォームとGoogleスプレッドシートを連携したデータを基にして、請求書を作成するGASを組みました。

その際に、苦戦してしまった2つのポイントがありますので、
その2点について備忘もかねてブログに残します。

◆お知らせ◆

【まとめ記事】

現在、これまで書いてきた記事をテーマ別にまとめています。

詳しくはこちらから

2つのつまずきポイント

つまずきポイント1_なぜかGASで取得した時刻がズレてしまう

この件については原因を特定するために、かなり時間がかかってしまいました。
最終的にはこちらの記事を発見したことで、原因と対策が判明して問題が解決できました。

上記ブログによると、結論どうやらスプレッドシートのバグのようでw
スプレッドシートのタイムゾーンと、スクリプトエディタのタイムゾーンがズレていると、スプレッドシート上で表示されている時刻と、GASで取得したときの時刻がなぜかズレてしまう現象が生じてしまいます

自分のケースでは、Googleフォームで作成したスプレッドシートで37分時刻がズレてしまう現象が起きていました。
この37分のズレの正体は、Googleフォームで作成したスプレッドシートの場合、スプレッドシートのタイムゾーンが東ティモールの首都Diliになってしまっていることが原因。

日本と同じく世界標準時間+9時間の地域ではありますが、
日本の方が厳密には【37分】分だけ緯度が大きいので、その分だけズレてしまうようです。

スプレッドシート上の時刻と、GASで取得したときの時刻がズレてしまうときには、スプレッドシートのタイムゾーンとスクリプトエディタのタイムゾーンを同じにする。

スプレッドシートのタイムゾーンを確認する

[ファイル] – [設定]をクリックする

全般タブのタイムゾーンを確認する

もし居住国の標準時間ではない場合には変更する。

スクリプトエディタのタイムゾーンを確認する

Apps Scriptで「プロジェクトの設定」をクリック
全般設定のタイムゾーンを確認する

もし居住国の標準時間ではない場合には変更する。

つまずきポイント2_HH:mm形式のデータから経過時間を計算する

Googleフォームで開始時刻、終了時刻、休憩時間を作成して、稼働時間をGASで計算しようと試みました。
すると、単純に下記のような式では意図した結果(3:00:00=3時間)は得られず・・・。

GAS
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/01UNIX時間の起点となる日付のようで、シリアル値とは異なります。
その知見がなかったので、ずっとシリアル値で考えてしまい迷宮入りしてしまった、というわけですw

解決策はいろいろあるとは思いますが、
私はシンプルに四則演算でできる方法で解決しました。

まず、休憩時間を下記のように日付 – 日付で計算します(ミリ秒)。

GAS
const restTime = 休憩時間 - new Date("1899/12/30 00:00:00");

その上で、開始時刻と終了時刻から経過時間を計算して、さらに先ほど計算した休憩時間(ミリ秒)を減算して、稼働時間を算出します。
ただ、計算で得られる結果はミリ秒(1秒 = 1,000ミリ秒)なので、時間・分・秒に変換したい場合には除算する必要があるので要注意です。

GAS
const workingTime = ((終了時刻 - 開始時刻) - restTime);

おわりに

GASでGoogleフォームから作成したスプレッドシートを活用する場合のポイント
  • 時刻を取得する場合には、スプレッドシートとスクリプトエディタのタイムゾーンを一致させる。
  • 休憩時間を含めた稼働時間を計算する際には工夫する必要がある。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA