使用App Script从表格取邮箱发HTML表格邮件遇收件人错误求助

问题描述 #

我用Google App Script从名为“Oct-22”的Google Sheet提取指定行邮箱，给对应收件人发送仅含该行数据的HTML表格邮件，但遇到三个问题：

• 出现“找不到收件人”错误
• 旧数据混入新邮件
• 无法实现单条数据单独发件

附原代码：

function expiredjobs() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Oct-22");
  var startRow = 2; // First row of data to process
  var numRows = sheet.getLastRow(); // Number of rows to process
  var numColu = sheet.getLastColumn(); // Number of colums to process 

  var dataRange = sheet.getRange(startRow, 1, numRows, numColu);
  var data = dataRange.getValues()
  
  var bodyEmail = "Dear sir, <br/>  Kindly provide the recommendation for the below mentioned near miss, also provide the supporting document to close the incident. <br/> "  

  var table = "<html><body><br><table border=1>"
  var colVal = "";
  for (var i = 1; i < 2; ++i) 
  {
      table = table + "<tr>"
       for (var colNo = 1; colNo <=11; colNo++) 
       {
        colVal = sheet.getRange(i , colNo).getDisplayValue();
        table = table + "<th>" + colVal + "</th>";
        }        
    table = table + "</tr>"
  }

  for (var i = 2; i < data.length; ++i)
  {
    var email = sheet.getRange(i,13).getValue().toString();
    var status = sheet.getRange(i,12).getValue().toString();
    var itemno = sheet.getRange(i,1).getValues().flat().filter(String).pop();
    Logger.log(email)
    Logger.log(status)  
    if(status === "Open")
    {
    Logger.log("1");
        table = table + "<tr>"
       for (var colNo = 1; colNo <=11; colNo++) 
       {
        colVal = sheet.getRange(i , colNo).getDisplayValue();
        table = table + "<td>" + colVal + "</td>";
        }        
    table = table + "</tr>"
    }
  }
  Logger.log("done");
  var le = table.length
  Logger.log(le);
  if (le > 200)
  {
    var subject =
        'ITS Ref No. ' + itemno + ' | ' +  'Report Status- OPEN';
    bodyEmail=bodyEmail + table
    MailApp.sendEmail(email,subject,"",{htmlBody:bodyEmail});
  }
}

问题根源 #

1. 收件人错误：最后发送邮件用的email是循环最后一次的变量，且循环索引逻辑错误（跳过了第2行数据，还可能取到空行的无效邮箱）；频繁调用getRange不仅效率低，还容易出现索引错位。
2. 旧数据混入：table变量在函数开头初始化，每次运行都会把新行数据追加到旧表格里，导致内容累积；且所有符合条件的行都被合并到一个表格，无法单独发件。
3. 单条发件未实现：原逻辑是把所有“Open”状态的行拼成一个大表格，最后只发一次邮件，而非为每个收件人单独发送对应行的内容。

修复后的代码 #

function expiredjobs() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Oct-22");
  var startRow = 2; 
  var lastRow = sheet.getLastRow();
  var numRows = lastRow - startRow + 1; 
  var targetCols = 11; 
  var emailColIndex = 12; // 数组索引从0开始，对应表格第13列
  var statusColIndex = 11; // 对应表格第12列

  // 一次性获取表头和所有数据，减少API调用次数
  var headers = sheet.getRange(1, 1, 1, targetCols).getDisplayValues()[0];
  var allData = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn()).getDisplayValues();

  // 邮件固定内容模板
  var emailHeader = "Dear sir,<br/>Kindly provide the recommendation for the below mentioned near miss, also provide the supporting document to close the incident.<br/>";

  // 遍历每一行数据
  allData.forEach(function(row) {
    var recipientEmail = row[emailColIndex];
    var rowStatus = row[statusColIndex];
    var itemNumber = row[0];

    // 只处理状态为Open且邮箱不为空的行
    if (rowStatus === "Open" && recipientEmail.trim() !== "") {
      // 为当前行单独构建HTML表格
      var htmlTable = "<html><body><br><table border='1'>";
      // 添加表头
      htmlTable += "<tr>";
      headers.forEach(function(header) {
        htmlTable += "<th>" + header + "</th>";
      });
      htmlTable += "</tr>";
      // 添加当前行数据
      htmlTable += "<tr>";
      for (var i = 0; i < targetCols; i++) {
        htmlTable += "<td>" + row[i] + "</td>";
      }
      htmlTable += "</tr></table></body></html>";

      // 组装邮件内容并发送
      var emailSubject = "ITS Ref No. " + itemNumber + " | Report Status- OPEN";
      var fullEmailBody = emailHeader + htmlTable;
      
      MailApp.sendEmail({
        to: recipientEmail,
        subject: emailSubject,
        htmlBody: fullEmailBody
      });
      
      Logger.log("已发送邮件至: " + recipientEmail + ", 关联单号: " + itemNumber);
    }
  });
}

修改说明 #

• 批量获取数据：一次性拉取表头和所有行数据，避免反复调用getRange，提升运行效率同时减少索引错误。
• 单条数据独立处理：遍历每一行时，为符合条件的行单独生成表格和邮件，实现单条数据单独发件。
• 修复收件人逻辑：直接从数据数组中取对应列的邮箱，确保每封邮件的收件人是当前行的对应联系人；添加邮箱非空判断，避免“找不到收件人”错误。
• 避免数据累积：每次循环内重新初始化htmlTable，确保每封邮件只包含当前行的内容，不会混入旧数据。

内容的提问来源于stack exchange，提问作者Motamarri Om Harik