Google Sheets自定义函数返回日期无法格式化，如何处理？

问题描述 #

我有一个Google表格，使用以下自定义函数接收日期并计算下一个到期日，但函数返回的是2024-06-26这类字符串格式的日期，无法在表格中自定义格式为Wed 26 June。推测是函数没有返回标准日期对象，请问如何修改？

原函数代码：

function nextDueDate(date1, date2, date3, date4, date5, date6, done1, done2, done3, done4, done5, done6) {
  const today = new Date();
  const dates = [new Date(date1), new Date(date2), new Date(date3),new Date(date4), new Date(date5), new Date(date6)];
  var dones = [done1, done2, done3, done4, done5, done6];

  // Completes earlier tasks if subsequent tasks are completed
  var dones = dones.map((val,index) => { if(dones.lastIndexOf('x')>=index ) {return 'x'}});

  // Filter for dates that are not done
  const futureDates = dates.filter((date,index) => !dones[index]);

  // Find the earliest date from the filtered future dates
  const nextDueDate = futureDates.reduce((earliest, date) => {
    return date < earliest ? date : earliest;
  }, new Date('2999-12-31')); // initial date far in the future

  // If the nextDueDate year is still in the far future then all tasks are done (on no dates)
  return (nextDueDate.getFullYear() === 2999) ? 'DELETE' : nextDueDate.toLocaleDateString(); // Returns the next due date in a readable format
}

解决方案 #

核心修改逻辑 #

问题根源在于函数最后返回的是字符串类型的日期（通过toLocaleDateString()转换），而非标准的Date对象。Google Sheets仅能对原生日期对象应用自定义格式，因此只需调整返回逻辑：

• 当存在未完成任务时，直接返回nextDueDate这个Date对象，不做字符串转换
• 所有任务完成时仍返回"DELETE"文本，保持原有业务逻辑

修改后的完整代码 #

function nextDueDate(date1, date2, date3, date4, date5, date6, done1, done2, done3, done4, done5, done6) {
  const today = new Date();
  const dates = [new Date(date1), new Date(date2), new Date(date3),new Date(date4), new Date(date5), new Date(date6)];
  var dones = [done1, done2, done3, done4, done5, done6];

  // Completes earlier tasks if subsequent tasks are completed
  var dones = dones.map((val,index) => { if(dones.lastIndexOf('x')>=index ) {return 'x'}});

  // Filter for dates that are not done
  const futureDates = dates.filter((date,index) => !dones[index]);

  // Find the earliest date from the filtered future dates
  const nextDueDate = futureDates.reduce((earliest, date) => {
    return date < earliest ? date : earliest;
  }, new Date('2999-12-31')); // initial date far in the future

  // If the nextDueDate year is still in the far future then all tasks are done (on no dates)
  return (nextDueDate.getFullYear() === 2999) ? 'DELETE' : nextDueDate; // 直接返回Date对象
}

设置Google Sheets自定义日期格式 #

函数返回日期对象后，选中对应单元格：

1. 点击顶部菜单 格式 > 数字 > 自定义数字格式
2. 在输入框中填入格式代码：ddd dd mmmm
3. 确认后，单元格将自动显示为Wed 26 June的样式

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