基于Google Apps Script实现表单注册码匹配对应用户名展示

我有一个面向订阅者的Google表单，希望在表单字段的setHelpText中，根据输入的注册码显示对应的订阅者姓名。注册码与姓名存储在Google表格中，A列为Reg.Code（注册码），B列为Name（姓名）。目前编写的代码会展示B列所有姓名，需要修改为仅显示与输入注册码匹配的对应姓名。

原代码 #

function setPattern(e) {
  // Get the firm item
  const form = FormApp.openById('FormID')
  const item = form.getItemById('ItemID').asTextItem()

  // Get the list of values
  const spreadsheet = SpreadsheetApp.openById('SheetID')
  const psheet = spreadsheet.getSheetByName('subscriber')
  const values = psheet.getRange(1, 1, psheet.getLastRow(), 1).getValues().flat()
  const match = psheet.getRange(1, 2, psheet.getLastRow(), 1).getValues().flat()
  //var idRange = psheet.getRange(1, 1, psheet.getLastRow(),1).getValues().flat();
  
  /** // iterate over all your IDs
  for(i=0;i<idRange.length;i++){
    // if one ID is the same as the incoming one from the form response
    if(idRange[i] == values){
      // get its value to the form
      psheet.getRange(i+1, 2);
    }
  } **/
 const pattern = `^(${values.map(_escapeRegex).join('|')})$`
  var validation = FormApp.createTextValidation()
  .setHelpText(match+ " Is this your name?")
  .requireTextDoesNotMatchPattern(pattern)
  .build();
  item.setValidation(validation);
  Logger.log(match)
}

示例数据 #

+------+------------------------+
| 1014 | Mohamed Touzani        |
+------+------------------------+
| 3052 | Yildiz Satrel          |
+------+------------------------+
| 3057 | Hind Abou Ridouane     |
+------+------------------------+
| 3076 | Adil Khan              |
+------+------------------------+
| 3068 | Maram Ibrahim          |
+------+------------------------+
| 3075 | Banaz Elias Khider     |
+------+------------------------+
| 3077 | Manal Kasmi Jai        |
+------+------------------------+
| 3078 | Narmin Al-hakari       |
+------+------------------------+
| 3082 | Maroua Chelhi Tadlaoui |
+------+------------------------+
| 1019 | Rainayul Hasnat        |
+------+------------------------+
| 3074 | Lorena Marsavela       |
+------+------------------------+
| 3067 | Mohammed Moosa Raufi   |
+------+------------------------+
| 3086 | Mohamed Idris Lhouty   |
+------+------------------------+
| 1020 | Oussama Banequi        |
+------+------------------------+
| 3087 | Demirdas Muhammed-Ali  |
+------+------------------------+
| 3088 | Chtioui Hafssa         |
+------+------------------------+

核心问题分析 #

1. 未通过触发事件获取用户当前输入的注册码，无法针对性查找姓名
2. 直接将所有姓名数组拼接为帮助文本，导致显示全部姓名
3. 验证规则逻辑错误：requireTextDoesNotMatchPattern会拒绝有效注册码，应该改为要求匹配有效注册码列表
4. 未建立注册码与姓名的映射关系，无法快速匹配

修正后的代码 #

function setPattern(e) {
  // 获取表单及目标字段
  const form = FormApp.openById('FormID');
  const item = form.getItemById('ItemID').asTextItem();

  // 获取表格数据并构建注册码-姓名映射
  const spreadsheet = SpreadsheetApp.openById('SheetID');
  const psheet = spreadsheet.getSheetByName('subscriber');
  const data = psheet.getRange(1, 1, psheet.getLastRow(), 2).getValues();
  const regCodeMap = {};
  const validRegCodes = [];
  
  // 跳过表头（如果第一行是表头，无表头可移除if判断）
  for (let i = 0; i < data.length; i++) {
    const regCode = data[i][0].toString();
    const name = data[i][1];
    if (regCode && name) { // 过滤空行
      regCodeMap[regCode] = name;
      validRegCodes.push(regCode);
    }
  }

  // 获取用户当前输入的注册码
  const inputRegCode = e.response.getResponseForItem(item).getResponse().toString();
  // 匹配对应的姓名，默认显示提示文本
  const matchedName = regCodeMap[inputRegCode] || "未找到匹配的订阅者";
  const helpText = `${matchedName} Is this your name?`;

  // 构建正则验证规则：仅允许输入有效注册码
  const pattern = `^(${validRegCodes.map(_escapeRegex).join('|')})$`;
  const validation = FormApp.createTextValidation()
    .setHelpText(helpText)
    .requireTextMatchesPattern(pattern)
    .setErrorMessage("请输入有效的注册码")
    .build();

  item.setValidation(validation);
}

// 正则转义辅助函数
function _escapeRegex(str) {
  return str.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
}

关键说明 #

1. 映射关系构建：将表格的注册码和姓名转换为对象键值对，实现快速查找
2. 事件获取输入：通过e.response获取用户当前输入的注册码，确保帮助文本与输入匹配
3. 验证规则修正：使用requireTextMatchesPattern确保输入的是有效注册码，同时添加错误提示
4. 空行过滤：跳过表格中的空行或无效数据，避免干扰匹配

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