如何判定Google Sheets多表单响应表中受访者邮箱是否唯一

问题背景 #

• 某Google Sheets关联3个Google Forms，表单提交数据同步至Form Responses 1、Form Responses 2、Form Responses 4三个工作表
• 核心需求：提取所有目标工作表中跨表唯一的邮箱地址（同一邮箱在多个工作表出现时，仅保留一次记录）
• 现有代码缺陷：仅支持单表内邮箱去重，无法实现跨表累计校验

优化后的代码 #

function findUniqueAcrossSheets() {
  const emailCol = 1; // B列（数组索引从0开始）
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const excludeSheets = ["Reference", "Index"];
  const seenEmails = new Set(); // 用Set自动存储唯一值，提升去重效率
  const uniqueEmails = [];

  // 遍历所有工作表
  ss.getSheets().forEach(sheet => {
    // 跳过排除列表中的工作表
    if (excludeSheets.includes(sheet.getName())) return;
    
    // 获取工作表数据，跳过表头（若第一行不是表头则删除.slice(1)）
    const data = sheet.getDataRange().getValues();
    data.slice(1).forEach(row => {
      const email = row[emailCol];
      // 仅处理非空的邮箱字符串
      if (email && typeof email === 'string') {
        if (!seenEmails.has(email)) {
          seenEmails.add(email);
          uniqueEmails.push([email]);
        }
      }
    });
  });

  // 对邮箱按自然字符串排序
  uniqueEmails.sort((a, b) => a[0].localeCompare(b[0]));

  // 【可选】将结果写入指定工作表，取消注释即可启用
  /*
  const targetSheet = ss.getSheetByName("Unique Emails") || ss.insertSheet("Unique Emails");
  targetSheet.clear();
  targetSheet.appendRow(["唯一邮箱"]);
  if (uniqueEmails.length > 0) {
    targetSheet.getRange(2, 1, uniqueEmails.length, 1).setValues(uniqueEmails);
  }
  */

  console.log("跨表唯一邮箱列表：", uniqueEmails);
  return uniqueEmails;
}

关键改动说明 #

• 全局去重集合：将seenEmails和uniqueEmails放在工作表循环外部，确保所有工作表共享同一去重上下文
• Set数据结构：替代原数组嵌套循环的去重逻辑，大幅提升效率，且原生支持唯一值存储
• 表头处理：默认跳过第一行表头数据，可根据实际表结构调整slice(1)参数
• 数据校验：增加非空和字符串类型判断，避免无效数据干扰
• 排序优化：替换原数字排序逻辑，使用localeCompare实现邮箱字符串的自然排序

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