如何实现Google Forms自动匹配Google Sheets序列号并反馈结果？

一、Google Sheets表单提交时自动运行脚本的可行性 #

完全可以实现，通过可安装的onFormSubmit触发器就能达成实时响应，用户提交表单后脚本会自动执行，无需手动触发。

二、完整实现步骤 #

1. 前期准备 #

• 把10000+条序列号存在Google Sheets的指定工作表（比如命名为序列号库），序列号放在A列（A2开始，A1设为表头）。
• 将Google Forms关联到该Sheets，表单仅保留一个“序列号”输入框，表单响应存储在另一工作表（默认是表单响应1）。

2. 编写匹配与反馈脚本 #

打开关联的Sheets，点击扩展程序 > Apps脚本，替换默认代码为以下内容：

function onFormSubmit(e) {
  // 获取提交的序列号和用户邮箱
  const submittedSerial = e.values[1]; // 假设表单响应第2列是序列号（第1列为提交时间）
  const userEmail = e.response.getRespondentEmail();
  
  // 读取序列号库并过滤空值
  const serialSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("序列号库");
  const serialList = serialSheet.getRange("A2:A").getValues().flat().filter(val => val !== "");
  
  // 判断是否匹配
  const isMatch = serialList.includes(submittedSerial);
  const result = isMatch ? "是" : "否";
  
  // 在表单响应表中记录匹配结果
  const responseSheet = e.range.getSheet();
  responseSheet.getRange(e.range.getRow(), responseSheet.getLastColumn() + 1).setValue(result);
  
  // 自动发送结果邮件给用户（需表单开启收集邮箱）
  if (userEmail) {
    const subject = "序列号匹配结果通知";
    const body = `您提交的序列号：${submittedSerial}\n匹配结果：${result}`;
    MailApp.sendEmail(userEmail, subject, body);
  }
}

页面实时显示结果：可在Google Forms的设置中开启“提交后显示自定义消息”，将内容设为「您的序列号匹配结果将发送至您的邮箱，请查收」，结合邮件通知实现双重反馈；若需更精准的实时页面展示，可将表单提交后跳转至一个Google Sites页面，通过脚本把匹配结果同步到该页面，但邮件通知是更直接的方案。

3. 设置可安装触发器 #

• 在Apps脚本编辑器左侧点击触发器图标（闹钟样式）。
• 点击添加触发器，按以下配置：
  • 选择函数：onFormSubmit
  • 事件源：电子表格
  • 事件类型：表单提交
• 保存并完成授权（需授予脚本访问表格和发送邮件的权限）。

三、仅用Google Sheets实现时避免数据覆盖的方案 #

如果不想用Forms，仅靠Sheets实现用户输入，可通过两种方式限制：

• 范围保护：仅开放一个固定输入单元格（比如Sheet1的B1）给用户编辑，其他单元格设置锁定。操作路径：数据 > 保护工作表和范围，设置允许编辑的范围为目标单元格，指定可编辑用户。
• 脚本自动存档：编写onEdit触发器，用户输入内容后自动将数据存档到新行，清空输入单元格：

function onEdit(e) {
  const inputCell = e.range;
  // 限定输入单元格为Sheet1的B1
  if (inputCell.getA1Notation() === "B1" && inputCell.getValue() !== "") {
    const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("输入记录");
    const lastRow = targetSheet.getLastRow();
    // 记录提交时间和序列号
    targetSheet.getRange(lastRow + 1, 1).setValue(new Date());
    targetSheet.getRange(lastRow + 1, 2).setValue(inputCell.getValue());
    // 清空输入框
    inputCell.clearContent();
  }
}

注意事项 #

• 必须在Google Forms设置中开启收集电子邮件地址（路径：设置 > 常规 > 收集电子邮件地址），才能获取用户邮箱发送通知。
• 序列号库条目较多时，脚本中filter(val => val !== "")会过滤空行，保证匹配效率。
• 脚本授权需使用拥有表格编辑权限的账号，避免权限不足导致执行失败。

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