如何在Google Sheets Apps Script中按DD/MM/YYYY格式排序任务日期？

问题原因 #

Google Sheets中如果日期以文本格式的DD/MM/YYYY存储，直接排序会按字符串规则（先比较日，再月，再年）导致顺序异常。解决方案是在脚本中将文本日期转换为Date对象，再按日期值排序。

完整代码 #

// 按DD/MM/YYYY格式的日期排序任务
function sortTasksByDate() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getDataRange();
  const allValues = dataRange.getValues();

  // 分离表头和数据行（假设第一行是表头）
  const headerRow = allValues.shift();
  
  // 按日期排序：将DD/MM/YYYY文本转为Date对象后比较
  allValues.sort((rowA, rowB) => {
    // 假设日期在B列（索引1，从0开始），任务名称在A列（索引0）
    const dateA = parseDDMMYYYY(rowA[1]);
    const dateB = parseDDMMYYYY(rowB[1]);
    return dateA - dateB;
  });

  // 将表头+排序后的数据写回表格
  dataRange.setValues([headerRow, ...allValues]);
}

// 检查任务到期情况，剩余1天时弹出提示
function checkTaskDeadlines() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const allValues = sheet.getDataRange().getValues();
  
  // 跳过表头，遍历数据行
  for (let i = 1; i < allValues.length; i++) {
    const taskName = allValues[i][0];
    const dateStr = allValues[i][1];
    const taskDeadline = parseDDMMYYYY(dateStr);

    // 重置时间部分，只比较日期
    const today = new Date();
    today.setHours(0, 0, 0, 0);
    taskDeadline.setHours(0, 0, 0, 0);

    // 计算剩余天数
    const timeDifference = taskDeadline.getTime() - today.getTime();
    const daysLeft = Math.ceil(timeDifference / (1000 * 60 * 60 * 24));

    // 剩余1天时触发提示
    if (daysLeft === 1) {
      Browser.msgBox(`Days left:1 on ${taskName}`);
    }
  }
}

// 辅助函数：将DD/MM/YYYY格式的字符串转为Date对象
function parseDDMMYYYY(dateString) {
  const [day, month, year] = dateString.split('/').map(Number);
  // 注意JavaScript的Date月份是0起始（0=1月），所以要减1
  return new Date(year, month - 1, day);
}

使用说明 #

1. 打开你的Google Sheet，点击「Extensions」→「Apps Script」进入脚本编辑器
2. 清空默认代码，粘贴上述代码
3. 确认你的表格结构：
   • A列：任务名称
   • B列：截止日期（格式为DD/MM/YYYY，比如05/10/2024）
   • 第一行是表头（比如A1="任务名称"，B1="截止日期"）
4. 如需排序：点击脚本编辑器的运行按钮，选择sortTasksByDate，首次运行需授权
5. 如需检查到期任务：运行checkTaskDeadlines，剩余1天的任务会弹出提示框
6. （可选）设置自动触发器：点击脚本编辑器左侧的「触发器」图标，添加触发器让checkTaskDeadlines每天自动运行

注意事项 #

• 如果你的日期列/任务名称列不在A/B列，修改代码中的索引（比如日期在C列就把rowA[1]改为rowA[2]）
• 确保日期列的内容是纯文本格式，避免因地区格式差异导致解析错误

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