Google Sheets脚本：指定关键词触发单次邮件通知故障排查

需求概述 #

当CJ列出现关键词Complete时触发邮件通知，当前实现流程：

• validate_complete函数检测CJ列的Complete值，若存在则在同行CK列写入Send Email；
• Send Email标记触发Send_notification函数向指定邮箱发送邮件；
• 通过触发器监听CK列的Send Email标记，执行validate_complete函数。

当前问题 #

触发器执行耗时过长，易出现超时，且会一次性发送多封重复邮件。需要实现：仅为CK列指定行的Send Email发送单封邮件，发送后立即清除该行标记，避免多行触发时重复发送。

现有代码 #

function validate_complete(){

  const ss = SpreadsheetApp.getActive();

  const sh_01 = ss.getSheetByName('Index');   
  const data = sh_01.getRange('CI2:CK'+sh_01.getLastRow()).getValues();

  var columnNumberToWatch = 88; // column A = 1, B = 2, etc.
  var valueToWatch = 'Complete';

 data.forEach(r=>{
  var range = sh_01.getActiveCell();
  if (range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    range.offset(0, 1).setValue('Send Email');

  }

  });
    sendnotificationEmail();
}



function sendnotificationEmail() {
  const ss = SpreadsheetApp.getActive();

  const sh_02 = ss.getSheetByName('Index'); 
  const date_range = sh_02.getRange('BW2').getValues();
  const data_02 = sh_02.getRange('CI2:CK'+sh_02.getLastRow()).getValues();

  var recipient = "testemail1@email.com,testemail2@email.com";
  data_02.forEach(r=>{
 
     let overdueValue = r[2];  
     if (overdueValue === "Send Email"){
         let name = r[0];
         let message = ' Test: ' + name ;
         let subject = 'Test: '+ name + ', ' + date_range + ' Inspections are available' ;
         MailApp.sendEmail(recipient, subject, message); 
     }
       var range_02 = sh_02.getRange('CK2:CK100');
       range_02.clear();

  });  
}

当前触发器配置截图 #

优化解决方案 #

核心优化思路

1. 直接监听CJ列的编辑事件，避免全量遍历数据，仅处理触发变更的行；
2. 发送邮件后立即清除当前行的Send Email标记，而非批量清除；
3. 减少函数嵌套调用，简化执行流程；
4. 缩减Spreadsheet API调用次数，提升执行效率。

优化后代码

// 监听CJ列（第88列）的编辑事件，值变为Complete时触发
function onCjColumnEdit(e) {
  const sheet = e.source.getActiveSheet();
  // 仅处理Index工作表和CJ列的编辑操作
  if (sheet.getName() !== 'Index' || e.range.getColumn() !== 88) return;
  
  const targetRow = e.range.getRow();
  // 跳过表头行（假设表头在第1行）
  if (targetRow < 2) return;

  if (e.value === 'Complete') {
    // 在同行CK列（第89列）写入标记
    sheet.getRange(targetRow, 89).setValue('Send Email');
    // 针对当前行发送邮件并清除标记
    sendSingleNotification(targetRow, sheet);
  }
}

// 针对指定行发送单封邮件并清除标记
function sendSingleNotification(row, sheet) {
  const recipient = "testemail1@email.com,testemail2@email.com";
  const dateValue = sheet.getRange('BW2').getValue();
  const name = sheet.getRange(row, 87).getValue(); // CI列对应第87列

  const subject = `Test: ${name}, ${dateValue} Inspections are available`;
  const message = `Test: ${name}`;
  
  // 发送邮件
  MailApp.sendEmail(recipient, subject, message);
  // 清除当前行CK列的标记
  sheet.getRange(row, 89).clearContent();
}

触发器配置调整

1. 删除原有触发器；
2. 创建可安装编辑触发器：
   • 选择函数：onCjColumnEdit
   • 事件源：从电子表格
   • 事件类型：编辑

调整后，每次只有CJ列被编辑为Complete时才会触发逻辑，仅处理当前变更行，发送邮件后立即清除标记，彻底解决重复发送和超时问题。

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