如何通过Google Apps Script实现Google Form的部分匹配关联下拉

前置准备 #

• 你的Google Sheet需包含两列：A列存Owner Name（所有者姓名），B列存Property ID（物业ID），将该工作表命名为Data便于脚本调用。
• 在目标Google Form中创建3个问题：
  • 短答题：标题设为「输入所有者姓名关键词」
  • 下拉选择题：标题设为「选择匹配的所有者姓名」
  • 下拉选择题：标题设为「选择对应物业ID」

编写Google Apps Script #

1. 打开Google Form，点击右上角「扩展程序」→「Apps Script」进入脚本编辑器，替换默认代码为以下内容：

// 全局配置：修改为你的Sheet名称和表单问题标题
const SHEET_NAME = "Data";
const NAME_INPUT_TITLE = "输入所有者姓名关键词";
const NAME_DROPDOWN_TITLE = "选择匹配的所有者姓名";
const PROPERTY_DROPDOWN_TITLE = "选择对应物业ID";

// 根据输入的关键词更新姓名下拉选项
function updateNameDropdown(inputText) {
  const form = FormApp.getActiveForm();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const allNames = sheet.getRange("A2:A" + sheet.getLastRow()).getValues().flat().filter(Boolean);
  
  // 模糊匹配（不区分大小写）
  const matchedNames = allNames.filter(name => name.toLowerCase().includes(inputText.toLowerCase()));
  
  const nameDropdown = form.getItems(FormApp.ItemType.LIST).find(item => item.getTitle() === NAME_DROPDOWN_TITLE);
  if (nameDropdown) {
    const listItem = nameDropdown.asListItem();
    listItem.setChoiceValues(matchedNames.length ? matchedNames : ["无匹配结果"]);
  }
}

// 根据选中的姓名更新物业ID下拉选项
function updatePropertyDropdown(selectedName) {
  const form = FormApp.getActiveForm();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const data = sheet.getRange("A2:B" + sheet.getLastRow()).getValues();
  
  // 筛选对应所有者的物业ID
  const propertyIDs = data.filter(row => row[0] === selectedName).map(row => row[1]).filter(Boolean);
  
  const propertyDropdown = form.getItems(FormApp.ItemType.LIST).find(item => item.getTitle() === PROPERTY_DROPDOWN_TITLE);
  if (propertyDropdown) {
    const listItem = propertyDropdown.asListItem();
    listItem.setChoiceValues(propertyIDs.length ? propertyIDs : ["无对应物业ID"]);
  }
}

// 安装表单 onChange 触发器
function installTriggers() {
  // 清理旧触发器
  ScriptApp.getProjectTriggers().forEach(trigger => {
    if (trigger.getHandlerFunction() === "handleFormChange") {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  // 创建新触发器监听表单变化
  ScriptApp.newTrigger("handleFormChange")
    .forForm(FormApp.getActiveForm())
    .onChange()
    .create();
}

// 处理表单输入/选择变化
function handleFormChange() {
  const form = FormApp.getActiveForm();
  const latestResponse = form.getResponses().at(-1); // 获取最新提交的响应
  if (!latestResponse) return;
  
  const itemResponses = latestResponse.getItemResponses();
  
  // 更新姓名下拉选项
  const nameInputResp = itemResponses.find(resp => resp.getItem().getTitle() === NAME_INPUT_TITLE);
  if (nameInputResp) {
    updateNameDropdown(nameInputResp.getResponse());
  }
  
  // 更新物业ID下拉选项
  const nameDropdownResp = itemResponses.find(resp => resp.getItem().getTitle() === NAME_DROPDOWN_TITLE);
  if (nameDropdownResp) {
    updatePropertyDropdown(nameDropdownResp.getResponse());
  }
}

部署与测试 #

1. 在脚本编辑器中，点击「运行」→「运行函数」→「installTriggers」，按照提示完成授权（首次运行需授权脚本访问你的Google表单和表格）。
2. 测试流程：
   • 在短答题输入部分姓名关键词，切换到下一题，下拉框会显示所有匹配的所有者姓名；
   • 选中目标姓名后，第三题的下拉框将自动加载该所有者对应的所有物业ID。

注意事项 #

• 提前清理Sheet中的数据：用TRIM()函数去除姓名和物业ID的多余空格，避免匹配失效；
• 确保表单中3个问题的标题唯一，否则脚本会匹配到第一个同名问题；
• 触发器基于最新响应更新选项，用户每次输入或选择后，下拉选项都会实时刷新。

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