GAS: 「経過時間」をスプレッドシートに入力する

スプレッドシートに、社員の労働時間や、作業の所要時間などを入力する際は、9:00、7:30:15のような、「経過時間」形式でデータを入力すると思います。

スプレッドシートのUI上でこれを入力するのは簡単ですが、Google Apps Scriptからセルに書き込もうとすると、つまづくことがあるかと思いましたので、方法を紹介します。

サンプルとして下の表を用意しました。
A2セルに勤務開始時間、B2セルに勤務終了時間が入力されており、労働時間を計算してC2セルに入力します。

方法1: 数式を書き込む
方法2: hh:mm:ss形式の文字列を書き込む
方法3: Dateインスタンスを使って書き込む

方法1: 数式を書き込む

この例のように、シート上に入力されている日時から算出するので良い場合は、セルに数式を書き込んでしまうのが簡単です。

function sample() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');
  sheet.getRange('C2').setValue('=B2-A2');
}

C2セルに=B2-A2の数式が書き込まれ、期待どおり9:00が表示されました。

複数行同時に書き込むことも可能です。
次の例は、最終行まで数式を書き込むコードです。

function sample() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');
  const lastRow = sheet.getLastRow();                   // 最終行番号を取得
  sheet.getRange(`C2:C${lastRow}`).setValue('=B2-A2');  // C2からC列最終行まで書き込む
}

期待どおり、最終行までC列に数式が入力されています。
なお、数式はちゃんとオートフィルされており、例えばC4セルには=B4-A4がセットされています。

方法2: hh:mm:ss形式の文字列を書き込む

スプレッドシートの数式を使わず、Google Apps Script側で時間を計算してセルに書き込む場合、経過時間を'9:00'のような文字列として、setValueまたはsetValuesで書き込む方法があります。

function sample() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');

  const startTime = sheet.getRange('A2').getValue();         // 勤務開始時刻を取得
  const endTime = sheet.getRange('B2').getValue();           // 勤務終了時刻を取得
  const duration = endTime.getTime() - startTime.getTime();  // 労働時間を算出（ミリ秒単位）
  
  const hours = Math.floor(duration / 3600000);                                      // 時
  const minutes = Math.floor((duration - hours * 3600000) / 60000);                  // 分
  const seconds = Math.floor((duration - hours * 3600000 - minutes * 60000) / 1000); // 秒
 
  sheet.getRange('C2').setValue(`${hours}:${minutes}:${seconds}`);  // セルに書き込み
}

C2セルに時:分:秒の値が書き込まれ、期待どおり9:00が表示されました。
今回は秒も含めましたが、不要な場合はコードの最後をsheet.getRange('C2').setValue(${hours}:${minutes});に変えれば良いです。

方法3: Dateインスタンスを使って書き込む

Google Apps Script側で「経過時間」からDateのインスタンスを作成し、それを使ってスプレッドシートのセルに書き込むことも可能です。

const SS_BASE_TIME = -2209194000000;  // GASとスプレッドシートの基準日付の差（日本時間）

function sample() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');

  const startTime = sheet.getRange('A2').getValue();         // 勤務開始時刻を取得
  const endTime = sheet.getRange('B2').getValue();           // 勤務終了時刻を取得
  const duration = endTime.getTime() - startTime.getTime();  // 労働時間を算出（ミリ秒単位）
  
  // Dateインスタンス（スプレッドシートの基準日付に合わせる）を使って書き込む
  sheet.getRange('C2').setValue(new Date(duration + SS_BASE_TIME));  // セルに書き込み
}

上記のコードでは、SS_BASE_TIME(-2209194000000)を定義して使っています。
これが何なのかを説明します。

Google Apps ScriptのDate型の基準日時は、日本時間で1970年1月1日 9:00:00です。
一方、スプレッドシートの日時の基準日時は、日本時間で1899年12月30日 0:00:00となっています。

このように、スプレッドシートの基準日時はGASの基準日時より約70年前（2209194000000ミリ秒前）の日時になっているため、そのままスプレッドシートに書き込んでしまうと正しい値になりません。

よって、その差分を解消してセルに書き込む必要があるので、SS_BASE_TIMEを反映したDateインスタンスを作成しています。

ちなみに、この差分を考慮せずにそのままセルに書き込むと・・・

こうなりました。働き方改革が必要です。

補足ですが、基準日時の差分が -2209194000000 ミリ秒となるのは、スプレッドシートのタイムゾーンの設定が(GMT+09:00) Tokyoの場合です。

他のタイムゾーンに設定している場合は、それにあわせた差分ミリ秒で計算する必要があります。
時間（ミリ秒）を算出するには、適当なセルに 0:00 と入力し、下記コードを実行するのが簡単です。

// 例: A1セルに0:00を入力した場合
const baseTimeCellValue = sheet.getRange('A1').getValue();
console.log(new Date(baseTimeCellValue).getTime());

方法1: 数式を書き込む

方法2: hh:mm:ss形式の文字列を書き込む

方法3: Dateインスタンスを使って書き込む

コメント