Google Sheets按Rep合并行发送单封邮件的代码优化需求

现有Google Apps Script代码会为Google Sheets每行数据单独发送邮件，需优化为给每个rep发送一封包含其所有对应行数据的格式化表格邮件，同时解决当前邮件无规范表格格式的问题。

Google Sheets 数据示例 #

现有代码 #

function sendEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getRange("A2:G");
  const data = dataRange.getValues();

  for (let i = 0; i < data.length; i++) {
    const row = data[i];
    const number = row[0];
    const type = row[1];
    const owner = row[2];
    const rep = row[3];
    const assistant = row[4];
    const email = row[5];
    const subject = row[6];

    console.log(`Row ${i + 2}: ${number}, ${type}, ${owner}, ${rep}, ${assistant}, ${email}, ${subject}`); // Logging the data

    if (!email) {
      console.log(`Row ${i + 2}: Email address is missing. Skipping this row.`);
      continue;
    }

    const message = createEmailMessage(number, type, owner, rep, assistant);
function createEmailMessage(number, type, owner, rep, assistant) {
  const message = `Dear ${rep},

Please update if you are no longer the Rep of the following types:
  Number    Type        Owner        Rep         Assistant
  ${number} ${type} ${owner}    ${rep}      ${assistant} 

Thank you for your prompt attention to this matter.`;

  return message;
}

    try {
      MailApp.sendEmail(email, subject, message);
      console.log(`Row ${i + 2}: Email sent to ${email}`);
    } catch (error) {
      console.log(`Row ${i + 2}: Error sending email to ${email}. Error message: ${error.message}`);
    }
  }
}

期望邮件效果 #

亲爱的 rep：

请确认以下记录中您是否仍担任Rep，若有变动请及时更新：

number	types	owner	rep	assistant	email	subject
1	square	Alice	ovc	jlc	test1@gmail.com	Update
4	square	Alice	khr	jlc	test2@gmail.com	Update

感谢您的及时处理！

优化后的代码 #

function sendEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getRange("A1:G"); // 包含表头
  const data = dataRange.getValues();
  const header = data[0]; // 获取表头
  const rows = data.slice(1); // 跳过表头，取数据行

  // 按rep+邮箱分组，确保同一rep对应同一邮箱的合并数据
  const groupedData = {};
  rows.forEach((row, index) => {
    const rep = row[3];
    const email = row[5];
    const subject = row[6];
    if (!email) {
      console.log(`Row ${index + 2}: 邮箱地址缺失，跳过此行`);
      return;
    }
    const key = `${rep}_${email}`; // 用rep+邮箱作为唯一键，避免同一rep不同邮箱的情况
    if (!groupedData[key]) {
      groupedData[key] = {
        rep: rep,
        email: email,
        subject: subject,
        rows: []
      };
    }
    groupedData[key].rows.push(row);
  });

  // 遍历分组数据，发送邮件
  Object.values(groupedData).forEach(group => {
    const message = createEmailMessage(group.rep, header, group.rows);
    try {
      // 发送HTML格式邮件，支持表格样式
      MailApp.sendEmail({
        to: group.email,
        subject: group.subject,
        htmlBody: message,
        // 添加纯文本 fallback，兼容不支持HTML的邮箱
        body: createPlainTextMessage(group.rep, header, group.rows)
      });
      console.log(`邮件已发送至 ${group.email}（对应rep：${group.rep}）`);
    } catch (error) {
      console.log(`发送邮件至 ${group.email} 失败，错误信息：${error.message}`);
    }
  });
}

// 生成HTML格式的邮件内容，包含格式化表格
function createEmailMessage(rep, header, rows) {
  // 构建表格行HTML
  const tableRows = rows.map(row => {
    return `<tr>${row.map(cell => `<td>${cell}</td>`).join('')}</tr>`;
  }).join('');

  return `
    <div style="font-family: Arial, sans-serif;">
      <p>亲爱的 ${rep}：</p>
      <p>请确认以下记录中您是否仍担任Rep，若有变动请及时更新：</p>
      <table style="border-collapse: collapse; width: 100%; margin: 15px 0;">
        <thead>
          <tr style="background-color: #f2f2f2;">
            ${header.map(col => `<th style="border: 1px solid #ddd; padding: 8px;">${col}</th>`).join('')}
          </tr>
        </thead>
        <tbody>
          ${tableRows}
        </tbody>
      </table>
      <p>感谢您的及时处理！</p>
    </div>
  `;
}

// 生成纯文本格式的邮件内容，作为HTML的 fallback
function createPlainTextMessage(rep, header, rows) {
  // 计算每个表头的宽度，对齐文本
  const columnWidths = header.map((col, idx) => {
    const maxLength = Math.max(col.length, ...rows.map(row => String(row[idx]).length));
    return maxLength + 2; // 留两个空格间距
  });

  // 格式化表头
  const formattedHeader = header.map((col, idx) => col.padEnd(columnWidths[idx])).join('');
  // 格式化数据行
  const formattedRows = rows.map(row => {
    return row.map((cell, idx) => String(cell).padEnd(columnWidths[idx])).join('');
  }).join('\n');

  return `亲爱的 ${rep}：

请确认以下记录中您是否仍担任Rep，若有变动请及时更新：

${formattedHeader}
${formattedRows}

感谢您的及时处理！`;
}

关键改动说明 #

• 数据分组：通过groupedData对象按rep+邮箱分组，将同一rep的所有数据行合并，避免重复发送邮件。
• HTML表格格式：生成带样式的HTML表格，让邮件内容更规范易读，同时保留纯文本版本作为兼容方案。
• 代码结构优化：将邮件内容生成函数移至循环外部，避免重复定义；增加表头处理，让邮件表格包含完整列名。
• 错误处理完善：保持原有缺失邮箱的跳过逻辑，同时在分组阶段就进行判断。

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