如何解决Google Apps Script中Google Sheets日期格式转换失效问题？

需求说明 #

• 检查单元格输入值，若是日期则格式化为dd/MM/yy格式
• 若不是日期则将输入值转换为大写

原问题代码 #

原脚本中日期转换功能失效，代码如下：

function onEdit(e) {
  var tab = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  if (tab != "backup")  { // 日期转换部分失效
    if(isDate(e.range.getValue())) {
      var date = new Date(e.range.getValue());
      var formattedDate = Utilities.formatDate(date, "GMT-3", 'dd/MM/yy');
      e.range.setValue(formattedDate);
    } else { // 文本转大写功能正常
        e.range.setValue(e.value.toUpperCase()); 
      }
  }
}

function isDate(date) {
  return(Object.prototype.toString.call(date) === '[object Date]');
}

问题原因 #

原代码通过setValue将格式化后的日期字符串写入单元格，会把原本的日期类型转为纯文本，同时可能因格式转换的兼容性问题导致日期识别或显示异常。

最简解决方案 #

改用setNumberFormat直接设置单元格的数字格式，既保留日期类型，又能正确显示指定格式，修正后的代码如下：

function onEdit(e) {
  var tab = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  if (tab != "backup")  { 
    if(isDate(e.range.getValue())) {
      e.range.setNumberFormat("dd/MM/yy");
    } else { 
        e.range.setValue(e.value.toUpperCase()); 
      }
  }
}

function isDate(date) {
  return(Object.prototype.toString.call(date) === '[object Date]');
}

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