编辑触发邮件发送异常：日志显示成功但无邮件送达排查

场景与问题 #

• 需求：Google Sheets中，当第5列下拉框选中"Matt"时，自动发送该行所有信息至Matt邮箱，并抄送其上司
• 现状：已关联表格所有者邮箱创建可安装触发器，执行sendEmailEdit函数后日志显示执行成功，但Matt及其上司均未收到邮件，已排查垃圾邮件过滤机制无拦截记录
• 已尝试：修改函数参数、变量类型、编辑范围等操作，问题仍未解决

原代码 #

function sendEmailEdit(e){

  if (e.range.columnStart != 5 || e.value != "Matt") return;
  
    const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
    let opportunity = rData [0][0];
    let job_name = rData [0][1];
    let address = rData [0][2];
    let client =  rData [0][3];
    let estimator =  rData [0][4];
    let due_date = rData [0][5];
    let due_time =  rData [0][6];
    let now = new Date().toLocaleString("en-US");
    let msg = "Opportunity"  + " " + opportunity + " "  + job_name + " "  + address + " "  + client + " "  + estimator + " " + due_date + " "  + due_time + " "  + "assigned on" + " "  + now ;
    var recipient = "matt@test.com";
    var subject = "Opportunity Assigned" + opportunity;
    var body = msg;
    var options = {
    cc: "mattboss@test.com"
  }
 Logger.log(msg);
MailApp.sendEmail(recipient, subject, body, options)
}

排查方向与修复方案 #

1. 邮箱有效性验证

   • 确认matt@test.com和mattboss@test.com为真实可用邮箱，未被邮件服务商或Google Workspace限制接收
   • 临时替换为个人测试邮箱，执行函数验证邮件是否能正常送达，排除收件方配置问题

2. 触发器与执行权限

   • 确认可安装触发器以表格所有者身份创建，事件类型为「编辑时」（On edit）
   • 手动模拟触发：在脚本编辑器中构造e对象参数（如指定range.columnStart=5、e.value="Matt"），直接运行函数，查看是否能发送邮件，排除触发器上下文问题

3. 邮件内容优化

   • 原邮件内容为纯文本堆砌，可能被邮件系统判定为垃圾邮件，建议格式化内容增加可读性：

     const msg = `Opportunity ${opportunity || 'N/A'}
     

Job Name: ${job_name || 'N/A'}
Address: ${address || 'N/A'}
Client: ${client || 'N/A'}
Estimator: ${estimator || 'N/A'}
Due Date: ${due_date ? due_date.toLocaleString("en-US") : 'N/A'}
Due Time: ${due_time || 'N/A'}
Assigned on: ${now}`;
```

• 主题中若opportunity为空会导致主题异常，建议补充默认值：const subject = Opportunity Assigned: ${opportunity || 'Unnamed'};

4. 代码健壮性提升

   • 添加错误捕获，在日志中记录详细错误信息，便于排查隐藏问题
   • 显式指定邮件发送者名称，提升邮件辨识度：

     const options = {
       cc: "mattboss@test.com",
       name: "Sheet Opportunity Notifier"
     };
     

5. 邮件配额检查

   • 确认未超出Google Apps Script邮件发送配额（免费版每日100封，Workspace版依套餐而定）
   • 查看脚本完整执行日志，确认MailApp.sendEmail是否有未捕获的异常

修改后的完整代码 #

function sendEmailEdit(e) {
  if (e.range.columnStart !== 5 || e.value !== "Matt") return;

  try {
    const rData = e.source.getActiveSheet().getRange(e.range.rowStart, 1, 1, 11).getValues()[0];
    const [opportunity, job_name, address, client, estimator, due_date, due_time] = rData;
    const now = new Date().toLocaleString("en-US");

    const msg = `Opportunity ${opportunity || 'N/A'}
Job Name: ${job_name || 'N/A'}
Address: ${address || 'N/A'}
Client: ${client || 'N/A'}
Estimator: ${estimator || 'N/A'}
Due Date: ${due_date ? due_date.toLocaleString("en-US") : 'N/A'}
Due Time: ${due_time || 'N/A'}
Assigned on: ${now}`;

    const recipient = "matt@test.com";
    const subject = `Opportunity Assigned: ${opportunity || 'Unnamed'}`;
    const options = {
      cc: "mattboss@test.com",
      name: "Sheet Opportunity Notifier"
    };

    Logger.log(`Sending email to ${recipient}, CC: ${options.cc}`);
    Logger.log(`Email content: ${msg}`);
    
    MailApp.sendEmail(recipient, subject, msg, options);
    Logger.log("Email sent successfully");
  } catch (error) {
    Logger.log(`Error sending email: ${error.message}`);
    throw error;
  }
}

内容的提问来源于stack exchange，提问作者Logan.Oakley