如何基于Google Sheet数据实现Google表单多级下拉列表联动填充

步骤1：整理数据源表格 #

• 打开Google Sheets，新建一张工作表（比如命名为「部门班级映射」）
• 第一列输入部门名称（如D1、D2、D3），第二列输入对应班级（每个班级占一行，比如D1对应C1、C2就分两行输入），第一行可设置表头（如「部门」「班级」），确保无空行

步骤2：编写Apps Script脚本 #

1. 打开目标Google表单，点击右上角「扩展程序」→「Apps 脚本」
2. 清空默认的Code.gs代码，替换为以下内容：

// 从Google Sheets读取部门-班级映射关系
function getDepartmentClassMap() {
  // 替换为你的Google Sheets文档ID和工作表名称
  const spreadsheetId = '你的表格ID';
  const sheetName = '部门班级映射';
  
  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const classMap = {};

  // 跳过表头，遍历数据构建映射
  for (let i = 1; i < data.length; i++) {
    const department = data[i][0].trim();
    const className = data[i][1].trim();
    
    if (department && className) {
      if (!classMap[department]) {
        classMap[department] = [];
      }
      // 避免重复添加相同班级
      if (!classMap[department].includes(className)) {
        classMap[department].push(className);
      }
    }
  }
  return classMap;
}

// 当用户选择部门后，更新班级下拉选项
function updateClassOptions(e) {
  const form = FormApp.getActiveForm();
  // 替换为你表单中部门、班级下拉框的标题
  const deptItemTitle = '部门';
  const classItemTitle = '班级';

  // 获取部门和班级对应的表单控件
  const deptItem = form.getItems(FormApp.ItemType.LIST).find(item => item.getTitle() === deptItemTitle);
  const classItem = form.getItems(FormApp.ItemType.LIST).find(item => item.getTitle() === classItemTitle);

  if (!deptItem || !classItem) return;

  // 获取用户选择的部门
  const selectedDept = e.response.getResponseForItem(deptItem).getResponse();
  const classMap = getDepartmentClassMap();
  const availableClasses = classMap[selectedDept] || ['无对应班级'];

  // 更新班级下拉选项
  const classDropdown = classItem.asListItem();
  classDropdown.setChoiceValues(availableClasses);
}

// 初始化部门下拉选项（可选）
function initDepartmentOptions() {
  const form = FormApp.getActiveForm();
  const deptItemTitle = '部门';
  const deptItem = form.getItems(FormApp.ItemType.LIST).find(item => item.getTitle() === deptItemTitle);
  
  if (!deptItem) return;
  
  const classMap = getDepartmentClassMap();
  const departments = Object.keys(classMap);
  const deptDropdown = deptItem.asListItem();
  deptDropdown.setChoiceValues(departments);
}

3. 替换代码中的你的表格ID（从Google Sheets URL中d/和/edit之间的字符串获取）、部门班级映射（如果你的工作表名称不同），以及deptItemTitle和classItemTitle为表单中对应的下拉框标题

步骤3：设置触发器 #

1. 在Apps Script编辑器左侧点击「触发器」图标（时钟形状）
2. 点击「添加触发器」，按以下配置设置：
   • 选择运行函数：updateClassOptions
   • 事件源：「表单」
   • 事件类型：「表单提交」
3. 点击「保存」，按提示完成脚本权限授权（需允许非Google验证的应用权限）

步骤4：初始化部门下拉（可选） #

• 如果还未设置部门下拉的选项，在脚本编辑器中选择initDepartmentOptions函数，点击运行按钮，部门下拉会自动填充数据源中的所有部门名称

注意事项 #

• 确保数据源表格的权限设置为脚本可访问（比如共享为「任何人可查看」）
• 后续更新部门或班级数据后，重新运行initDepartmentOptions即可更新部门下拉选项
• 该方案基于表单提交触发更新，下一位用户打开表单时，班级下拉会显示对应部门的班级；若需实时在用户选择部门后立即更新，需结合前端脚本，但Google表单原生不支持此功能，当前方案可满足多数场景需求

内容的提问来源于stack exchange，提问作者Dv Hieu