Google表单姓名验证：如何用Apps Script限制仅名单内用户提交？

一、核心问题分析 #

1. 无法阻止提交：onFormSubmit是提交完成后触发的事件，此时表单数据已写入Sheet，无法回滚或阻止提交；原代码中验证不通过就关闭表单的逻辑也不合理，会影响所有后续用户提交。
2. 姓名索引易出错：e.values的第一个元素是提交时间，后续按顺序对应表单问题，若表单问题顺序调整，responses[1]/responses[2]就会失效，应该按问题标题获取值更可靠。
3. 无效的弹窗提示：SpreadsheetApp.getUi().alert()在表单提交事件中无法触发，因为没有UI上下文支持。

二、可行的实现方案 #

方案1：同步Sheet姓名到表单下拉选项（推荐） #

直接将Sheet中的姓名同步为表单的下拉选项，让用户只能选择已有的姓名，从源头避免无效提交。代码如下：

// 同步Sheet中的姓名到表单下拉选项
function syncNamesToForm() {
  // 配置信息，替换为你的实际ID和标题
  const FORM_ID = "你的表单ID";
  const SHEET_ID = "你的Sheet ID";
  const FIRST_NAME_QUESTION_TITLE = "名";
  const LAST_NAME_QUESTION_TITLE = "姓";
  const NAME_SHEET_NAME = "姓名列表";

  // 获取表单和Sheet数据
  const form = FormApp.openById(FORM_ID);
  const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(NAME_SHEET_NAME);
  const nameData = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues(); // 读取A2:B列的姓名数据

  // 提取唯一的名和姓，去重并过滤空值
  const firstNames = [...new Set(nameData.map(row => row[0].trim()))].filter(name => name);
  const lastNames = [...new Set(nameData.map(row => row[1].trim()))].filter(name => name);

  // 更新"名"问题为下拉选项
  const firstNameItem = form.getItems().find(item => item.getTitle() === FIRST_NAME_QUESTION_TITLE);
  if (firstNameItem) {
    const dropdown = firstNameItem.asListItem();
    dropdown.setChoiceValues(firstNames);
  }

  // 更新"姓"问题为下拉选项
  const lastNameItem = form.getItems().find(item => item.getTitle() === LAST_NAME_QUESTION_TITLE);
  if (lastNameItem) {
    const dropdown = lastNameItem.asListItem();
    dropdown.setChoiceValues(lastNames);
  }

  // 可选：添加名和姓的组合验证，确保配对正确
  const namePairs = nameData.map(row => `${row[0].trim()} ${row[1].trim()}`).filter(pair => pair);
  form.addTextValidation()
    .setHelpText("请确保名和姓的组合在列表中存在")
    .requireTextMatchesPattern(`^(${namePairs.join("|")})$`)
    .build();
}

使用说明：

• 运行syncNamesToForm函数，完成首次同步。
• 可设置定时触发器（比如每日一次），自动同步Sheet中的最新姓名。

方案2：提交后验证并处理无效数据（备选） #

如果必须保留手动输入姓名的方式，可在提交后验证，删除无效数据并通知提交者：

function onFormSubmit(e) {
  // 配置信息，替换为你的实际ID和标题
  const SHEET_ID = "你的Sheet ID";
  const NAME_SHEET_NAME = "姓名列表";
  const RESPONSE_SHEET_NAME = "表单响应";
  const EMAIL_QUESTION_TITLE = "邮箱";

  // 按问题标题获取提交的姓名和邮箱，避免顺序依赖
  const responseItems = e.response.getItemResponses();
  const firstName = responseItems.find(item => item.getItem().getTitle() === "名").getResponse();
  const lastName = responseItems.find(item => item.getItem().getTitle() === "姓").getResponse();
  const email = responseItems.find(item => item.getItem().getTitle() === EMAIL_QUESTION_TITLE)?.getResponse();

  // 验证姓名是否在列表中
  if (!isNameInSheet(firstName, lastName, SHEET_ID, NAME_SHEET_NAME)) {
    // 删除无效的提交行
    const responseSheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(RESPONSE_SHEET_NAME);
    responseSheet.deleteRow(e.range.getRow());

    // 发送邮件通知提交者（需表单收集邮箱）
    if (email) {
      MailApp.sendEmail({
        to: email,
        subject: "提交失败：姓名不匹配",
        body: `你提交的姓名（${firstName} ${lastName}）不在允许的列表中，请核对后重新提交。`
      });
    }
  }
}

function isNameInSheet(firstName, lastName, sheetId, sheetName) {
  const sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
  
  const lowerFirstName = firstName.trim().toLowerCase();
  const lowerLastName = lastName.trim().toLowerCase();

  for (const row of data) {
    const sheetFirstName = row[0].trim().toLowerCase();
    const sheetLastName = row[1].trim().toLowerCase();
    if (sheetFirstName === lowerFirstName && sheetLastName === lowerLastName) {
      return true;
    }
  }
  return false;
}

使用说明：

• 确保表单包含邮箱收集项，才能发送通知。
• 给onFormSubmit函数添加表单提交触发器。

三、原代码的具体修复点 #

若坚持使用原代码逻辑，需修复以下问题：

1. 正确获取姓名：改用e.response.getItemResponses()按问题标题获取值，避免依赖问题顺序：

   const responseItems = e.response.getItemResponses();
   const firstName = responseItems.find(item => item.getItem().getTitle() === "名").getResponse();
   const lastName = responseItems.find(item => item.getItem().getTitle() === "姓").getResponse();
   

2. 移除关闭表单的操作：该操作会影响所有用户，改为删除无效提交或发送邮件通知。
3. 移除无效的弹窗提示：SpreadsheetApp.getUi().alert()在表单提交事件中无法触发，改用邮件通知提交者。

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