修改Google Sheets邮件合并脚本：添加Gmail模板下拉框与自动筛选

完整修改脚本 #

// -------------- 配置区 --------------
// 可选：硬编码模板主题列表，或者注释掉改用表格读取
const HARDCODED_TEMPLATES = [
  "新用户欢迎邮件",
  "付费用户续费提醒",
  "活动邀请-线下沙龙",
  // 按需添加你的12类模板主题
];
// 表格配置：主数据标签名、关键列名
const MAIN_SHEET_NAME = "联系人列表";
const SUBJECT_COLUMN = "Subject";
const EMAIL_COLUMN = "Email";
// 如果用表格读取模板，设置模板标签名
const TEMPLATE_SHEET_NAME = "模板主题";

// -------------- 核心功能 --------------
// 弹出模板选择对话框
function showTemplateSelector() {
  let templateOptions;
  // 选择模板来源：这里切换硬编码/表格读取
  templateOptions = HARDCODED_TEMPLATES;
  // templateOptions = getTemplateSubjectsFromSheet(); // 取消注释改用表格读取

  // 构建HTML对话框
  const html = `
    <div style="padding: 20px; width: 300px;">
      <h3>选择邮件模板</h3>
      <select id="templateSelect" style="width: 100%; padding: 8px; margin: 10px 0;">
        ${templateOptions.map(subj => `<option value="${subj}">${subj}</option>`).join('')}
      </select>
      <button onclick="selectTemplate()" style="width: 100%; padding: 10px; background: #4285F4; color: white; border: none; border-radius: 4px;">确认发送</button>
    </div>
    <script>
      function selectTemplate() {
        const selected = document.getElementById('templateSelect').value;
        google.script.run.processTemplateSelection(selected);
        google.script.host.close();
      }
    </script>
  `;

  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutput(html).setTitle("邮件模板选择"),
    "选择模板"
  );
}

// 处理模板选择，自动筛选并发送邮件
function processTemplateSelection(selectedSubject) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName(MAIN_SHEET_NAME);
  const dataRange = mainSheet.getDataRange();
  const headers = dataRange.getValues()[0];
  
  // 找到Subject列的索引
  const subjectColIndex = headers.indexOf(SUBJECT_COLUMN);
  if (subjectColIndex === -1) {
    SpreadsheetApp.getUi().alert("未找到Subject列，请检查表格配置！");
    return;
  }

  // 清除之前的筛选
  mainSheet.getFilter()?.remove();
  // 应用新筛选：匹配选中的模板主题
  const filter = mainSheet.getRange(1, 1, mainSheet.getLastRow(), mainSheet.getLastColumn()).createFilter();
  filter.setColumnFilterCriteria(subjectColIndex + 1, // 表格索引从1开始
    SpreadsheetApp.newFilterCriteria()
      .whenTextEqualTo(selectedSubject)
      .build()
  );

  // 发送筛选后的邮件
  sendMailsWithTemplate(selectedSubject);
  SpreadsheetApp.getUi().alert(`已完成${selectedSubject}主题的邮件发送！`);
}

// 发送个性化邮件
function sendMailsWithTemplate(targetSubject) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName(MAIN_SHEET_NAME);
  const data = mainSheet.getDataRange().getValues();
  const headers = data[0];
  
  // 找到关键列索引
  const emailColIndex = headers.indexOf(EMAIL_COLUMN);
  const subjectColIndex = headers.indexOf(SUBJECT_COLUMN);

  // 跳过表头，遍历筛选后的行
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    // 只处理匹配主题的行（双重保险，避免筛选失效）
    if (row[subjectColIndex] !== targetSubject) continue;

    const recipient = row[emailColIndex];
    if (!recipient) continue; // 跳过空邮箱

    // 获取Gmail模板内容：这里根据主题查找模板邮件
    const templateThread = GmailApp.search(`subject:"${targetSubject}" is:template`, 0, 1)[0];
    if (!templateThread) {
      SpreadsheetApp.getUi().alert(`未找到主题为${targetSubject}的Gmail模板！`);
      return;
    }
    const templateMessage = templateThread.getMessages()[0];
    let body = templateMessage.getBody();
    let subject = templateMessage.getSubject();

    // 替换个性化占位符：比如{{Name}}替换为表格中的Name列内容
    headers.forEach((header, index) => {
      const placeholder = `{{${header}}}`;
      body = body.replace(new RegExp(placeholder, 'g'), row[index] || '');
      subject = subject.replace(new RegExp(placeholder, 'g'), row[index] || '');
    });

    // 发送邮件
    GmailApp.sendEmail(
      recipient,
      subject,
      templateMessage.getPlainBody(), // 纯文本版本
      { htmlBody: body }
    );
  }
}

// 可选：从表格标签读取模板主题列表
function getTemplateSubjectsFromSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const templateSheet = ss.getSheetByName(TEMPLATE_SHEET_NAME);
  if (!templateSheet) {
    SpreadsheetApp.getUi().alert("未找到模板主题标签，请检查配置！");
    return [];
  }
  // 读取第一列的所有非空值作为模板主题
  return templateSheet.getRange(1, 1, templateSheet.getLastRow()).getValues().flat().filter(subj => subj);
}

关键功能说明 #

1. 模板选择对话框

   • 用HtmlService构建自定义弹窗，替代原生输入框实现下拉选择
   • 支持两种模板来源：
     • 硬编码：直接在HARDCODED_TEMPLATES数组添加主题
     • 表格读取：取消注释getTemplateSubjectsFromSheet()调用，在表格中新建「模板主题」标签，第一列填写所有模板主题

2. 自动筛选收件人

   • 在processTemplateSelection函数中，先清除历史筛选，再根据选中主题创建新筛选规则
   • 发送邮件时双重校验行主题，避免筛选失效

3. Gmail模板匹配与个性化替换

   • 通过GmailApp.search查找对应主题的模板邮件（需确保Gmail中已保存该主题模板）
   • 自动替换邮件占位符：表格列名用{{列名}}格式写在模板中，会自动替换为对应联系人数据

使用步骤 #

1. 打开联系人表格，点击「扩展程序」→「Apps脚本」，粘贴上述代码
2. 修改配置区参数：
   • 调整HARDCODED_TEMPLATES为你的12类主题，或配置TEMPLATE_SHEET_NAME用表格读取
   • 确认MAIN_SHEET_NAME是联系人数据标签名，SUBJECT_COLUMN和EMAIL_COLUMN对应表格列名
3. 保存脚本，运行showTemplateSelector，完成首次权限授权
4. 在弹窗选择模板并确认，脚本自动筛选收件人并发送个性化邮件

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