【GAS】Googleフォームで回答したデータから勤務時間を計算し、[h]:mm形式に変換する

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

Googleフォームで回答した内容をもとに勤務時間を集計し、スプレッドシートに出力するフローを構築しようとして、つまずいてしまったことがあったため、備忘も兼ねて記録に残すことにしました。

Googleアドセンス

◆お知らせ◆

Googleフォームで回答したデータから勤務時間を計算し、[h]:mm形式に変換する

勤務時間の計算までは上手くいく

まず、下図のようにGoogleフォームで
・開始時刻
・終了時刻
・休憩時間
を作成し、それぞれのタイプを時刻（hh:ｍｍ）に設定。

その上で、上記３つを回答した結果から、まずは休憩時間（restTime）をミリ秒として取得。勤務時間を算出します。

GAS

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

その上で、勤務時間（workingTime）を下記計算式で算出して、ミリ秒として取得します。

GAS

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

上記の内容については、以前つまずいたことがあり記事にまとめてありますので、詳細については下記記事を参考にしてくださいね。

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

24時間以上を表示させる時分（[h]:mm）に変換する方法

次に、ミリ秒のデータを[h]:mm形式で表現するためには、スプレッドシートの表示形式も変更しておく必要があります。
そうしないと、たとえば25:11というデータは自動的に01:11という時刻形式に変換されてしまいますので。

表示形式の選択

まずは、時分を表示させるセルを選択して、[表示形式] – [数字] – [カスタム数値形式]を選択。

カスタム数式で「[h]:mm」と入力し、『適用』をクリックする

スプレッドシートの設定が完了したら、あとはGASで時分に変換するだけです。
ここであれこれ考えこんでしまいましたが、シンプルに時間と分を別々に計算。
その上で、文字列として結合するという方法を選択しました。

workingTimeはミリ秒なので、1000で割るということを忘れずに。

GAS

  // 時・分で換算する
  const hour = Math.floor(workingTime / 60 / 60 / 1000);
  const minute = Math.floor((workingTime - hour * 3600000) / 60 / 1000);

  // [h]:MM形式で文字列として設定する
  const workingTimeStr = hour + ":" + minute + ":00.000";

workingTimeStrで最後に“:00.000”という文字列をくっつけているのは、分が1桁の場合に44:0のように表示されてしまうことがあるため。
スプレッドシートの表示形式に合わせる形で、“:00.000”を最後にくっつけています。

あとは、workingTimeStrを対象セルにsetValueすれば完了です。