如何在Google Sheets中实现任务逾期数量提醒功能？

前置准备：补充必要列 #

首先需要补充两个关键列来实现逾期判断逻辑：

• A列（创建日期）：表头设为「创建日期」，用于记录任务的起始时间。已有任务手动填写对应日期，新任务可在A2单元格输入=TODAY()后下拉填充，自动获取当天日期。
• C列（完成状态）：表头设为「完成状态」，选中C2及以下所有任务行，通过菜单栏「数据」→「数据验证」设置下拉选项为「已完成,未完成」，方便快速标记任务状态。

1. 实时计算逾期任务数量 #

在表格空白区域（比如G1）添加表头「逾期任务数量」，在G2单元格输入以下公式：

=COUNTIFS(C:C, "未完成", A:A, "<="&TODAY()-E:E)

该公式会自动统计所有未完成且创建日期 + 期限天数 < 当前日期的任务总数，结果会随日期和任务状态自动更新。

2. 设置自动邮件提醒（Google Apps Script） #

要实现定时自动发送逾期提醒，需要借助Google Apps Script编写脚本并设置触发规则：

1. 打开你的Google表格，点击菜单栏「扩展程序」→「Apps Script」
2. 删除编辑器中的默认代码，粘贴以下脚本：

function checkOverdueTasks() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // 替换为你的工作表实际名称
  const data = sheet.getDataRange().getValues();
  let overdueCount = 0;
  const today = new Date();

  // 遍历数据行（跳过表头行）
  for (let i = 1; i < data.length; i++) {
    const createDate = new Date(data[i][0]);
    const status = data[i][2];
    const deadlineDays = data[i][4];

    const deadlineDate = new Date(createDate);
    deadlineDate.setDate(createDate.getDate() + deadlineDays);

    if (status === "未完成" && today > deadlineDate) {
      overdueCount++;
    }
  }

  // 存在逾期任务时发送提醒邮件
  if (overdueCount > 0) {
    const recipient = Session.getActiveUser().getEmail();
    const subject = `逾期任务提醒：共有${overdueCount}项任务未按时完成`;
    const body = `当前有${overdueCount}项任务已超过完成期限，请及时处理。`;
    MailApp.sendEmail(recipient, subject, body);
  }
}

// 创建每日定时触发器
function createTrigger() {
  ScriptApp.newTrigger("checkOverdueTasks")
    .timeBased()
    .everyDays(1)
    .atHour(9) // 可修改为你需要的提醒时间（24小时制）
    .create();
}

3. 将脚本中的"Sheet1"替换为你的工作表实际名称（即表格底部的标签名）
4. 点击编辑器工具栏的「运行」按钮，首次运行需按照提示完成权限授权（允许脚本访问表格数据和发送邮件）
5. 运行createTrigger函数，完成每日自动检查的触发器设置

注意事项 #

• 确保A列的日期为有效日期格式，E列的期限值为纯数字（2、5、7等）
• 若需调整提醒时间，修改脚本中的atHour(9)为目标时间（如14代表下午2点）
• 如需停止自动提醒，可在Apps Script的「触发器」页面删除对应触发规则

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