GAS上で、スプレッドシートからシリアル値で得たデータを、日付型データにしたい。

Question

前提

GAS
PC：Mac Book Air

実現したいこと

スプレッドシートに特定の列の日付の変更があったときに、Slackに通知する。

1.スプレッドシートの日付データを上書き
日付データがある列を更新
ex）セルB5
2022/12/01→2022/12/10

↓

2.slackに変更前後の日付を通知
ex）Slackにて

［BOT］
日付が変更されました
更新前2022/12/01
↓
更新後2022/12/10

発生している問題・エラーメッセージ

スプレッドシートから取得できるデータが、シリアル値でしか入手できないため、それをdate型に変更しないといけない。
その方法がうまくいきません。

Utilities.formatDate(更新前データ, 'JST', 'yyyy-MM-dd');を利用しているためdate型で欲しいです。
※他に方法があるならそれでも良いです

※ スプレッドシート上でを文字列データの列を作ることは可能なのですが、したくないです。
理由：スプレッドシートに列の追加が社内運用上難しいため。

※ スプレッドシートは日付データになっておりますがシリアル値で帰ってきます。

該当のソースコード

function onEdit(e) { 
  let activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // スプレッドシート(book)  
  let activeSheet = activeSpreadsheet.getSheetByName("テスト"); // アクティブシート(sheet)

  let activeCell = activeSheet.getActiveCell();                   // アクティブセル
  let _newValue = activeCell.getValue();                        // 更新後の値
  let newValue = Utilities.formatDate(_newValue, 'JST', 'yyyy-MM-dd'); 
  console.log(newValue)  // 結果：2022-12-13
  let _oldValue = e.oldValue;  // 変更前のアクティブセルの値
  console.log(_oldValue)  // 444906
  console.log(typeof _oldValue)  // string

エラー文言

試したこと

https://qiita.com/dondoko-susumu/items/daa4071c1a406c6986f2#%E3%82%B7%E3%83%AA%E3%82%A2%E3%83%AB%E5%80%A4unixtimestamp

こちらをためしたのですが、うまくいかずでした。

うまくいくメソッド、そもそもスプレッドシートの旧データをシリアル値以外で取得できればベストです。

Comments

[Cocode]

> こちらをためしたのですが、うまくいかずでした。

うまくいかなかったコードをご提示いただければ、原因が究明できるかもです。

Answer 1

シリアル値について

• シリアル値は、「1899年12月30日午前0時」からの経過日数で、Excelで使われている形式のようです。
• JavaScriptのdate型に変換するためには、まずはエポックミリ秒というものに変換する必要があるようです。

エポックミリ秒への変換の計算式

(<シリアル値> - 25569) * 86400000

コード例

function onEdit(e) { 
  const newValue = e.value;
  const oldValue = e.oldValue;

  const newUnixTime = (newValue - 25569) * 86400000;
  const oldUnixTime = (oldValue - 25569) * 86400000;

  const newDate = new Date(newUnixTime);
  const oldDate = new Date(oldUnixTime);

  Logger.log(newDate);
  Logger.log(oldDate);
}

date型にできましたので、GASの.formatDate()で整形するなり、あとはいつも通りご自由ご利用いただけます。