应用脚本在谷歌表单中创建依赖下拉菜单

您想要根据地区、城市（地区内）和机构（地区/城市内）创建依赖下拉列表。您在获取相关机构时遇到困难。

数据的层次结构反映了脚本内的处理顺序：

• 获取唯一区域列表
  • 对于每个独特的地区，获取独特城市的列表
    • 对于每个独特的地区/独特的城市，获取机构

以下脚本的逻辑遵循此层次结构。

此语句： Logger.log("DEBUG: Region = "+dptOption+"\ncity name = "+uniqueCityName+"\nestablishments = "+regionCityEstablishment)未注释；它允许 OP 扫描/查看脚本生成的结果。

值得一提的是，通过与源数据的比较，已经确认了样本结果。

注 1：脚本不会创建表单 - 这是一项单独的任务。但是，确定每个区域、区域内的特定城市以及每个区域内每个特定城市的机构后，此步骤会得到简化。

注 2：最终表单的有效性和响应性值得质疑。表单中有 8 个地区、471 个独立城市和 1,641 个机构。就用户体验而言，在许多情况下，选项数量会扩展到数百个。就表单的响应性而言，必须承认包含数百个部分的表单的性能可能不可接受。也许，OP 可以对此进行反思并考虑可能的选项。

地区/城市及机构分析

const FORM_ID="insert your form ID"
let form=FormApp.openById(FORM_ID)

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

function createConditionalForm(){
  // reset form
  resetForm()

  let ws=SpreadsheetApp.getActiveSpreadsheet()
  let ss=ws.getSheetByName("Feuille 4_sorted") // Feuille 4 sorted by department, city and establishment
  let table=ss.getDataRange().getValues()
  // drop the headers
  table.shift()

  // create array of column A - departments
  let dptOptions=table.map(row=>row[0])
  // get only the unique departments in Column A
  let singleDptOptions=[]
  dptOptions.forEach(option=>{
    if(singleDptOptions.indexOf(option)==-1)
    singleDptOptions.push(option)
  })
  // Logger.log("DEBUG: Unique departments:"+singleDptOptions)

  // create question - "select region"
  let departementQuestion = form.addMultipleChoiceItem().setTitle("Select Region")
  
  // create a temporary array for the cities in each region
  let finalDptOptions = []

  // for each dpt/region do something
  singleDptOptions.forEach(dptOption=>{
    // for each region, create a section
    let section=form.addPageBreakItem().setTitle("Region:"+dptOption)
    section.setGoToPage(FormApp.PageNavigationType.SUBMIT)

    // create an array of ALL cities cities in each region
    let cityOptions=table.filter(option=>option[0]==dptOption).map(row=>row[1])

    // create a temporary array for the unique cities in each region
    let singleCityOptions=[]
    // create a temporary array for the establishements in each region/dpt city
    let regCitEst = []

    // create an array of UNIQUE cities in each region
    cityOptions.forEach(cityName=>{
      if(singleCityOptions.indexOf(cityName)==-1)
      singleCityOptions.push(cityName)
      //Logger.log("DEBUG: Region = "+dptOption+", city name = "+cityName)
    })

    // create an array of establishments for each unique city
    singleCityOptions.forEach(uniqueCityName=>{
      let regionCityEstablishment = table.filter(option=>option[0]==dptOption && option[1]==uniqueCityName).map(row=>row[2])
      regCitEst.push(regionCityEstablishment)
      Logger.log("DEBUG: Region = "+dptOption+"\ncity name = "+uniqueCityName+"\nestablishments = "+regionCityEstablishment)
    })
  })   
}

这是为了指出原始代码中存在什么问题。

当测试脚本时，似乎Exception: Array is empty: values出现了错误，我相信这就是你所说的意思keep geting error messages saying that the array is empty。

检查代码后，错误原因是

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

该脚本正在过滤Département，这是option[0]，不是Nom_commune，这是option[1]。

您可以将其更改为：

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

完整代码如下：

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 5");
  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");
    let finalCityOptions = [];
    singleCityOptions.forEach(cityOption => {
      let section2 = form.addPageBreakItem().setTitle(cityOption);
      section2.setGoToPage(FormApp.PageNavigationType.SUBMIT);
      let etabOptions = table.filter(option => option[1] == 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);
    let dptChoice = departementQuestion.createChoice(dptOption, section);
    finalDptOptions.push(dptChoice);
  });
  departementQuestion.setChoices(finalDptOptions);
}

笔记：

• 警惕Google 服务的配额。我已根据您提供的一小部分样本对此进行了测试，以确保其正常运行，因为原始数据大约需要11 分 20 秒才能完成。
• 在 JavaScript 中，分号是可选的。不过，作为最佳实践，我仍然将它们添加到脚本的行末。
• { }条件语句末尾没有花括号。此语法完全有效，但如果您使用花括号来分隔代码块并使用多行和缩进，则更容易理解代码。