如何修改Apps Script实现Google表单三级联动下拉菜单？

问题背景 #

• 数据源：Google Sheet包含3列数据，分别为部门编码（列1）、城市名称（列2）、机构名称（列3）
• 需求：通过Apps Script生成带分段的Google表单，实现三级联动下拉：选择部门编码后仅显示对应城市，选择城市后仅显示对应机构
• 现状：已实现二级联动（部门→城市）的代码，但添加第三级机构选择时，出现数组为空的报错

二级联动可用代码 #

const FORM_ID="1OkH_GNjrvOCkiBAXKqmRQq3e1uYxEpE8baqEDVokFso"
let form=FormApp.openById(FORM_ID)
function resetForm(){
let items=form.getItems()
items.forEach(item=>form.deleteItem(item))
}
function createConditionalForm(){
resetForm()
let ws=SpreadsheetApp.getActiveSpreadsheet()
let ss=ws.getSheetByName("Feuille 4")
let table=ss.getDataRange().getValues()
table.shift()
let dptOptions=table.map(row=>row[0])
let singleDptOptions=[]
dptOptions.forEach(option=>{
if(singleDptOptions.indexOf(option)==-1)
singleDptOptions.push(option)
})
let departementQuestion = form.addMultipleChoiceItem().setTitle("Select Region")
let finalDptOptions = []
singleDptOptions.forEach(dptOption=>{
let section=form.addPageBreakItem().setTitle(dptOption)
section.setGoToPage(FormApp.PageNavigationType.SUBMIT)

let cityOptions=table.filter(option=>option[0]==dptOption).map(row=>row[1])
let singleCityOptions=[]
cityOptions.forEach(option=>{
if(singleCityOptions.indexOf(option)==-1)
singleCityOptions.push(option)
})
let cityQuestion = form.addMultipleChoiceItem().setTitle("Select City")
cityQuestion.setChoiceValues(singleCityOptions)
let dptChoice = departementQuestion.createChoice(dptOption, section)
finalDptOptions.push(dptChoice)
})
departementQuestion.setChoices(finalDptOptions)
}

报错的三级联动尝试代码 #

const FORM_ID="Form_ID"
let form=FormApp.openById(FORM_ID)
function resetForm(){
let items=form.getItems()
items.forEach(item=>form.deleteItem(item))
}
function createConditionalForm(){
resetForm()
let ws=SpreadsheetApp.getActiveSpreadsheet()
let ss=ws.getSheetByName("Feuille 4")
let table=ss.getDataRange().getValues()
table.shift()
let dptOptions=table.map(row=>row[0])
let singleDptOptions=[]
dptOptions.forEach(option=>{
if(singleDptOptions.indexOf(option)==-1)
singleDptOptions.push(option)
})
let departementQuestion = form.addMultipleChoiceItem().setTitle("Select Dpt code")
let finalDptOptions = []
singleDptOptions.forEach(dptOption=>{
let section=form.addPageBreakItem().setTitle(dptOption)
let cityOptions=table.filter(option=>option[0]==dptOption).map(row=>row[1])
let singleCityOptions=[]
cityOptions.forEach(option=>{
if(singleCityOptions.indexOf(option)==-1)
singleCityOptions.push(option)
})
let cityQuestion = form.addMultipleChoiceItem().setTitle("Select City")
let finalCityOptions=[]
singleCityOptions.forEach(cityOption=>{
let section2=form.addPageBreakItem().setTitle(cityOption)
section2.setGoToPage(FormApp.PageNavigationType.SUBMIT)
let etabOptions=table.filter(option=>option[0]==cityOption).map(row=>row[2])
let singleEtabOptions=[]
etabOptions.forEach(option=>{
if(singleEtabOptions.indexOf(option)==-1)
singleEtabOptions.push(option)
})
let etabQuestion=form.addMultipleChoiceItem().setTitle("Select Place")
etabQuestion.setChoiceValues(singleEtabOptions)
let cityChoice = cityQuestion.createChoice(cityOption, section2)
finalCityOptions.push(cityChoice)
})
cityQuestion.setChoices(finalCityOptions)
cityQuestion.setChoiceValues(singleCityOptions)
let dptChoice = departementQuestion.createChoice(dptOption, section)
finalDptOptions.push(dptChoice)
})
departementQuestion.setChoices(finalDptOptions)
}

错误原因及修复方案 #

核心错误点 #

尝试代码中筛选机构的逻辑完全错误：

let etabOptions=table.filter(option=>option[0]==cityOption).map(row=>row[2])

此处用「部门编码等于城市名称」作为筛选条件，无法匹配到任何数据，导致机构数组为空。正确逻辑应该是同时匹配当前部门编码和当前城市名称，才能获取对应机构列表。

修复后的完整代码 #

const FORM_ID="你的表单ID"
let form=FormApp.openById(FORM_ID)

function resetForm(){
  let items=form.getItems()
  items.forEach(item=>form.deleteItem(item))
}

function createConditionalForm(){
  resetForm()
  const ws=SpreadsheetApp.getActiveSpreadsheet()
  const ss=ws.getSheetByName("Feuille 4")
  const table=ss.getDataRange().getValues()
  table.shift() // 移除表头

  // 用Set快速去重部门编码
  const singleDptOptions=[...new Set(table.map(row=>row[0]))]
  
  const departementQuestion = form.addMultipleChoiceItem().setTitle("选择部门编码")
  const finalDptOptions = []

  singleDptOptions.forEach(dptOption=>{
    // 创建部门对应的分页
    const dptSection=form.addPageBreakItem().setTitle(`部门：${dptOption}`)
    
    // 获取当前部门下的去重城市列表
    const cityList=table.filter(row=>row[0]===dptOption).map(row=>row[1])
    const singleCityOptions=[...new Set(cityList)]
    
    // 创建城市选择问题
    const cityQuestion = form.addMultipleChoiceItem().setTitle("选择城市")
    const finalCityOptions=[]

    singleCityOptions.forEach(cityOption=>{
      // 创建城市对应的分页
      const citySection=form.addPageBreakItem().setTitle(`城市：${cityOption}`)
      citySection.setGoToPage(FormApp.PageNavigationType.SUBMIT)
      
      // 同时匹配部门和城市，筛选对应机构
      const etabList=table.filter(row=>row[0]===dptOption && row[1]===cityOption).map(row=>row[2])
      const singleEtabOptions=[...new Set(etabList)]
      
      // 创建机构选择问题
      const etabQuestion=form.addMultipleChoiceItem().setTitle("选择机构")
      etabQuestion.setChoiceValues(singleEtabOptions)
      
      // 关联城市选项到对应分页
      const cityChoice = cityQuestion.createChoice(cityOption, citySection)
      finalCityOptions.push(cityChoice)
    })

    // 设置城市选择的联动选项
    cityQuestion.setChoices(finalCityOptions)
    
    // 关联部门选项到对应分页
    const dptChoice = departementQuestion.createChoice(dptOption, dptSection)
    finalDptOptions.push(dptChoice)
  })

  // 设置部门选择的联动选项
  departementQuestion.setChoices(finalDptOptions)
}

额外优化点 #

1. 使用[...new Set(arr)]替代循环去重，代码更简洁高效
2. 变量命名语义化，增强可读性
3. 分页标题添加层级前缀，区分逻辑节点
4. 移除冗余的setChoiceValues调用（setChoices已包含选项值）

内容的提问来源于stack exchange，提问作者OWEN MARSH