如何通过Google Script实现Google Sheet证书到期日提前30天邮件提醒？

这个需求完全可行，但Google Sheet自带的条件通知功能确实没法直接实现「提前30天触发邮件提醒」——因为条件通知仅在单元格内容实时变更或当前满足条件时触发，无法预设未来日期的提醒规则，所以必须借助Google Script来完成定时检查和邮件推送。

具体实现步骤 #

1. 编写Google Script代码 #

打开你的Google Sheet，点击顶部菜单栏「扩展程序」→「Apps脚本」，清空默认代码后粘贴以下脚本：

function checkExpiryDates() {
  // 替换为你的工作表名称和日期列范围，示例为"Sheet1"和"B2:B"（日期在B列，从第2行开始）
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const dateRange = sheet.getRange("B2:B").getValues();
  const today = new Date();
  // 计算30天后的日期
  const reminderDate = new Date(today.getTime() + 30 * 24 * 60 * 60 * 1000);
  
  // 遍历日期列数据
  for (let i = 0; i < dateRange.length; i++) {
    const expiryDate = dateRange[i][0];
    if (expiryDate instanceof Date) {
      // 跳过已过期的日期
      if (expiryDate < today) continue;
      // 统一日期格式（仅比较年月日，忽略时分秒）
      const expiryDateStr = expiryDate.toISOString().split('T')[0];
      const reminderDateStr = reminderDate.toISOString().split('T')[0];
      
      if (expiryDateStr === reminderDateStr) {
        // 获取对应证书名称（假设在A列，对应行）
        const certName = sheet.getRange(i + 2, 1).getValue();
        // 替换为你的收件邮箱
        const recipient = "your-email@example.com";
        const subject = `证书到期提醒：${certName}即将在30天后过期`;
        const body = `您好，证书「${certName}」的到期日期为${expiryDate.toLocaleDateString()}，距离到期还有30天，请及时处理。`;
        
        MailApp.sendEmail(recipient, subject, body);
      }
    }
  }
}

2. 配置定时触发器 #

• 在脚本编辑器左侧，点击时钟形状的「触发器」图标
• 点击「添加触发器」，按以下参数配置：
  • 要运行的函数：checkExpiryDates
  • 部署类型：「时间驱动」
  • 时间基于：「日计时器」
  • 时间间隔：「每天」，并设置固定运行时间（比如凌晨0点）

3. 脚本参数调整 #

• 将"Sheet1"替换为你的实际工作表名称
• 调整"B2:B"为日期列的实际范围（比如日期在C列则改为"C2:C"）
• 若证书名称不在A列，修改sheet.getRange(i + 2, 1)中的数字1（比如D列改为4）
• 将"your-email@example.com"替换为接收提醒的邮箱地址

注意事项 #

• 首次运行脚本时，需授权Google Script访问你的表格和邮箱，按提示完成授权即可
• 确保日期列内容为标准日期格式（而非文本格式），否则脚本无法识别

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