使用new Date()和Utilities.formatDate处理日期时出现异常结果

最近在处理Google Sheets和Apps Script的日期操作时遇到异常，具体情况如下：

1. 获取当前日期时间 #

代码：

var nowtime = new Date().getTime(); 
var nowdate = new Date(nowtime);
var formattedNow = Utilities.formatDate(nowdate, "Australia/Sydney", "dd/MM/yyyy HH:mm");

Logger.log(nowtime);
Logger.log(nowdate);
Logger.log(formattedNow);

执行结果：

1.659269747206E12
Sun Jul 31 22:15:47 GMT+10:00 2022
31/07/2022 22:15

2. 获取10天后的日期时间 #

代码：

var thentime = nowtime + 1000 * 60 * 60 * 24 * 10; //1000毫秒/秒、60秒/分、60分/时、24时/天、共10天
var thendate = new Date(thentime);
var formattedThen = Utilities.formatDate(thendate, "Australia/Sydney", "dd/MM/yyyy HH:mm");

Logger.log(thentime);
Logger.log(thendate);
Logger.log(formattedThen);

执行结果：

1.660133747212E12
Wed Aug 10 22:15:47 GMT+10:00 2022
10/08/2022 22:15

以上步骤结果均正常，但写入单元格后出现异常：

3. 写入日期到单元格的异常 #

代码：

sheet.getRange("A1").setValue(formattedNow);
sheet.getRange("A2").setValue(formattedThen);

Google Sheets中显示结果：

• A1单元格值：31/07/2022 22:15（符合格式）
• A2单元格值：10/8/2022 22:15:00（未遵循dd/MM/yyyy HH:mm格式，月份变为单数字，多出秒数）

4. 从单元格读取日期的异常 #

代码：

var a1 = new Date(sheet.getRange("A1").getValue());
var a1date = Utilities.formatDate(a1, "Australia/Sydney", "dd/MM/yyyy HH:mm");
Logger.log(a1date);

var a2 = new Date(sheet.getRange("A2").getValue());
var a2date = Utilities.formatDate(a2, "Australia/Sydney", "dd/MM/yyyy HH:mm");
Logger.log(a2date);

执行结果：

31/07/2022 22:15 //A1，正确
09/10/2022 09:15 //A2，错误，日期被错误解析

额外情况 #

若改为添加少于1天的毫秒数（如23小时），所有操作均正常。

完整复现代码 #

function onOpen(e) {

  const gsId = "Google Sheet ID"
  const gs = SpreadsheetApp.openById(gsId)
  const sheet = gs.getSheetByName("Sheet1")

  var nowtime = new Date().getTime();
  var nowdate = new Date(nowtime);
  var formattedNow = Utilities.formatDate(nowdate, "Australia/Sydney", "dd/MM/yyyy HH:mm");
  Logger.log(nowtime);
  Logger.log(nowdate);
  Logger.log(formattedNow);

  var thentime = new Date().getTime();
  thentime = thentime + 1000 * 60 * 60 * 24 * 10;
  var thendate = new Date(thentime);
  var formattedThen = Utilities.formatDate(thendate, "Australia/Sydney", "dd/MM/yyyy HH:mm");
  Logger.log(thentime);
  Logger.log(thendate);
  Logger.log(formattedThen);

  Logger.log("----everything above is showing correct results----");

  sheet.getRange("A1").setValue(formattedNow);
  sheet.getRange("A2").setValue(formattedThen);
  
  var a1 = new Date(sheet.getRange("A1").getValue());
  var a1date = Utilities.formatDate(a1, "Australia/Sydney", "dd/MM/yyyy HH:mm");
  Logger.log(a1date);

  var a2 = new Date(sheet.getRange("A2").getValue());
  var a2date = Utilities.formatDate(a2, "Australia/Sydney", "dd/MM/yyyy HH:mm");
  Logger.log(a2date);

}

问题原因与解决方案 #

原因 #

Google Sheets会自动识别单元格内容为日期格式，并默认按区域设置（多为美国MM/dd/yyyy格式）解析转换。10/08/2022会被误解析为10月8日，而非8月10日，同时自动补全秒数；而31/07/2022因31超出月份范围，无法被解析为日期，因此保留为文本格式显示正常。

解决方案 #

有两种可靠解决方式：

1. 直接写入Date对象并设置单元格格式
   跳过格式化字符串步骤，直接写入Date对象，再通过setNumberFormat控制显示格式：

// 写入当前日期并设置格式
sheet.getRange("A1").setValue(nowdate);
sheet.getRange("A1").setNumberFormat("dd/MM/yyyy HH:mm");

// 写入10天后的日期并设置格式
sheet.getRange("A2").setValue(thendate);
sheet.getRange("A2").setNumberFormat("dd/MM/yyyy HH:mm");

此方式让Sheets以日期类型存储数据，显示格式完全可控，避免自动解析错误。

2. 设置单元格为纯文本后写入字符串
   先将单元格格式设为纯文本，再写入格式化后的字符串：

// 设置A1为纯文本并写入
sheet.getRange("A1").setNumberFormat("@");
sheet.getRange("A1").setValue(formattedNow);

// 设置A2为纯文本并写入
sheet.getRange("A2").setNumberFormat("@");
sheet.getRange("A2").setValue(formattedThen);

此方式强制Sheets将内容视为文本，不会自动解析为日期。

内容的提问来源于stack exchange，提问作者aZgauAL9