Google Apps Script运行正常但未发送休假审批邮件，请求排查

我搭建了一个多级邮件审批系统，编写了sendLeaveApprovalEmail脚本，脚本运行无报错，但审批人和员工都收不到邮件，无法定位原因，附上脚本代码，请求协助排查。

function sendLeaveApprovalEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();

  // Loop through each row in the sheet
  for (var i = 1; i < data.length; i++) { // Start from row 2 (assuming row 1 is header)
    var row = data[i];

    // Check if the request is pending approval
    if (row[11] === "Pending") { // Assuming "Leave Status" is in column J
      var approver = row[8]; // Approver (Plant Manager or HR Manager)
      var approverEmail = approver === "Plant Manager" ? row[8] : row[9]; // Plant Manager Email Id or HR Email Id
      var employeeEmail = row[3]; // Employee Email Id
      var subject = "Leave Approval Request";
      var message = "Dear " + approver + ",\n\n";
      message += "Employee: " + row[1] + "\n";
      message += "Leave Type: " + row[5] + "\n";
      message += "Start Date: " + row[6] + "\n";
      message += "End Date: " + row[7] + "\n";
      message += "Please approve or reject this leave request by replying to this email.";

      // Send email to the current approver
      GmailApp.sendEmail(approverEmail, subject, message);

      // Update the "Leave Status" to indicate it's pending approval by the next level
      sheet.getRange(i + 1, 9).setValue("Pending HR Approval"); // or "Approved" if this is the final level

      // Optionally, send a confirmation email to the employee
      var confirmationMessage = "Your leave request has been submitted for approval.";
      GmailApp.sendEmail(employeeEmail, "Leave Request Submitted", confirmationMessage);
    }
  }
}

排查关键点： #

• 列索引不匹配：脚本注释说明“Leave Status”在J列，但代码用row[11]判断（数组索引从0开始，J列对应索引是9），导致条件永远不触发，邮件发送逻辑根本没执行。
• 审批人邮箱赋值错误：row[8]是审批人名称（如“Plant Manager”），代码却把它当成邮箱赋值给approverEmail，无效邮箱地址无法发送邮件，需确认邮箱列的正确索引并修正。
• 状态更新列错误：脚本更新第9列（I列）的状态，但“Leave Status”在J列（对应列号10），状态未正确更新会导致后续逻辑异常。
• 邮箱有效性检查：确认表格中员工、审批人的邮箱格式正确，无拼写错误。
• 权限与配额验证：检查脚本是否有Gmail发送权限，同时确认未超出Google Apps Script的邮件发送配额（个人账户每天限100封，工作/教育账户限500封）。
• 添加日志调试：在脚本中插入console.log()输出关键变量（如approverEmail、employeeEmail、是否进入if分支），运行后查看日志确认逻辑执行情况。

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