通过Google Sheets向单个邮箱发送多行数据的技术问题求助

原代码核心问题 #

1. 循环内每次调用MailApp.sendEmail，导致每行数据单独发送一封邮件
2. message变量在循环外初始化且用+=累加，每循环一次就带上之前所有行的内容，出现重复累加的情况
3. 逐行设置单元格标记效率极低，容易触发Google服务配额限制

方案1：所有未发送数据合并为单封邮件（固定收件人） #

将所有未发送的行数据汇总成一份内容，一次性发送到指定邮箱，同时批量标记已发送状态：

function sendEmail() {
  const ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const StartRow = 2;
  const LastRow = ActiveSheet.getLastRow();
  const RowRange = LastRow - StartRow + 1;
  const WholeRange = ActiveSheet.getRange(StartRow, 1, RowRange, 17);
  const AllValues = WholeRange.getValues();
  let message = "<h3>待处理记录汇总：</h3>";
  const rowsToMark = []; // 记录需要标记为已发送的行索引

  // 循环收集所有未发送的行数据
  for (let i = 0; i < AllValues.length; i++) {
    const CurrentRow = AllValues[i];
    const EmailSent = CurrentRow[16];
    if (EmailSent !== "email_fwd") {
      // 拼接当前行内容，用分隔线区分不同记录
      message += `
        <hr>
        <p><b>Body: </b>${CurrentRow[1]}</p>
        <p><b>XYZ ASSIGNEE:</b>${CurrentRow[2]}</p>
        <p><b>XYZ CATEGORY:</b>rews</p>
        <p><b>XYZ TYPE:</b>ua space</p>
        <p><b>XYZ ITEM:</b>audit exception</p>
      `;
      rowsToMark.push(i);
    }
  }

  // 有内容才发送，避免空邮件
  if (message !== "<h3>待处理记录汇总：</h3>") {
    // 替换为你的目标收件邮箱
    const SendTo = "your-target-email@example.com";
    const Subject = "多行数据汇总邮件";
    MailApp.sendEmail({
      to: SendTo,
      cc: "",
      subject: Subject,
      htmlBody: message,
    });

    // 批量标记已发送，提升效率
    const markRange = ActiveSheet.getRange(StartRow, 17, LastRow - StartRow + 1, 1);
    const markValues = markRange.getValues();
    rowsToMark.forEach(index => {
      markValues[index][0] = "email_fwd";
    });
    markRange.setValues(markValues);
  }
}

方案2：按收件人分组发送（同一收件人合并为单封） #

如果需要给不同收件人发送各自的待处理数据，可按邮箱地址分组，同一收件人的所有行数据合并为一封邮件：

function sendEmailByRecipient() {
  const ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const StartRow = 2;
  const LastRow = ActiveSheet.getLastRow();
  const RowRange = LastRow - StartRow + 1;
  const WholeRange = ActiveSheet.getRange(StartRow, 1, RowRange, 17);
  const AllValues = WholeRange.getValues();
  const recipientMessages = {}; // 按收件人存储对应邮件内容
  const rowsToMark = [];

  for (let i = 0; i < AllValues.length; i++) {
    const CurrentRow = AllValues[i];
    const EmailSent = CurrentRow[16];
    if (EmailSent !== "email_fwd") {
      const SendTo = CurrentRow[4];
      const rowContent = `
        <hr>
        <p><b>Body: </b>${CurrentRow[1]}</p>
        <p><b>XYZ ASSIGNEE:</b>${CurrentRow[2]}</p>
        <p><b>XYZ CATEGORY:</b>rews</p>
        <p><b>XYZ TYPE:</b>ua space</p>
        <p><b>XYZ ITEM:</b>audit exception</p>
      `;
      // 初始化收件人的邮件内容
      if (!recipientMessages[SendTo]) {
        recipientMessages[SendTo] = `<h3>你的待处理记录汇总：</h3>`;
      }
      recipientMessages[SendTo] += rowContent;
      rowsToMark.push(i);
    }
  }

  // 给每个收件人发送对应汇总邮件
  for (const [recipient, content] of Object.entries(recipientMessages)) {
    const Subject = "待处理记录汇总";
    MailApp.sendEmail({
      to: recipient,
      cc: "",
      subject: Subject,
      htmlBody: content,
    });
  }

  // 批量标记已发送
  if (rowsToMark.length > 0) {
    const markRange = ActiveSheet.getRange(StartRow, 17, LastRow - StartRow + 1, 1);
    const markValues = markRange.getValues();
    rowsToMark.forEach(index => {
      markValues[index][0] = "email_fwd";
    });
    markRange.setValues(markValues);
  }
}

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