基于Google Apps Script优化重复收件人单周报表邮件发送逻辑需求

修改核心思路 #

1. 扩展状态过滤条件，同时匹配New和Ongoing
2. 先将符合条件的记录按收件人邮箱分组，同一收件人的所有相关记录整合到一个数组中
3. 遍历分组后的邮箱列表，为每个收件人生成包含所有对应记录的汇总表格，仅发送单封邮件

修改后的完整代码 #

function macro() {
  // 获取Sheet3的数据区域
  var table = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3").getRange("A1").getDataRegion();
  var header = table.getValues();
  var resourceHeader = header[0][1];
  var statusHeader = header[0][3];
  var dueDateHeader = header[0][5];
  var linkHeader = header[0][10];
  
  // 过滤Status为New或Ongoing的记录
  var filterTable = new TableWithHeaderHelper(table) 
                .getTableWhereColumn("Status").matchValueRegex(/(New|Ongoing)/);
  
  // 1. 按邮箱分组数据：key=邮箱，value=该用户的所有记录数组
  var groupedData = {};
  for(var i=0; i < filterTable.length() ; i++){
    var email = filterTable.getWithinColumn("Email").cellAtRow(i).getValue();
    var resource = filterTable.getWithinColumn("Resource").cellAtRow(i).getValue();
    var status = filterTable.getWithinColumn("Status").cellAtRow(i).getValue();
    var dueDate = filterTable.getWithinColumn("Due Date").cellAtRow(i).getValue();
    var link = filterTable.getWithinColumn("Link").cellAtRow(i).getValue();
    
    // 如果邮箱不存在于分组对象中，初始化空数组
    if(!groupedData[email]) {
      groupedData[email] = [];
    }
    // 将当前记录加入对应邮箱的数组
    groupedData[email].push([resource, status, dueDate, link]);
  }
  
  // 2. 遍历每个分组，发送汇总邮件
  for(var email in groupedData) {
    var userRecords = groupedData[email];
    var htmlTemplate = HtmlService.createTemplateFromFile("email");
    
    // 传递表头和汇总数据到模板
    htmlTemplate.resource = resourceHeader;
    htmlTemplate.status_r = statusHeader;
    htmlTemplate.due_date_r = dueDateHeader;
    htmlTemplate.link_r = linkHeader;
    htmlTemplate.new_data = userRecords; // 传入多条记录的数组
    
    var htmlForMail = htmlTemplate.evaluate().getContent();
    var subjectMail = "Automation Support Services Actions Items";
    
    // 发送汇总邮件
    MailApp.sendEmail({
      to: email,
      subject: subjectMail,
      htmlBody: htmlForMail
    });
  }
}

关键修改点说明 #

• 状态过滤扩展：将正则表达式从/(New)/改为/(New|Ongoing)/，同时匹配两种目标状态
• 数据分组逻辑：新增groupedData对象，遍历过滤后的记录时，将同一邮箱的所有记录存入对应数组，实现按收件人归类汇总
• 邮件发送逻辑调整：从逐条发送改为遍历分组后的邮箱，每个邮箱仅发送一次邮件，邮件包含该用户的所有相关记录
• 模板数据传递：原代码中new_data是单条记录的数组，修改后传入多条记录的数组，需确保email.html模板支持遍历数组生成多行表格

模板适配提示 #

如果原模板仅支持单条记录，需修改为遍历数组生成多行表格，示例模板片段：

<table border="1">
  <tr>
    <th><?= resource ?></th>
    <th><?= status_r ?></th>
    <th><?= due_date_r ?></th>
    <th><?= link_r ?></th>
  </tr>
  <? for(var i=0; i < new_data.length; i++) { ?>
  <tr>
    <td><?= new_data[i][0] ?></td>
    <td><?= new_data[i][1] ?></td>
    <td><?= new_data[i][2] ?></td>
    <td><a href="<?= new_data[i][3] ?>">查看链接</a></td>
  </tr>
  <? } ?>
</table>

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