google-apps-scriptgoogle-forms

Apps script to create dependant Dropdown in google form

Based on an google sheet containing 3 columns :

col 1 contains department codes, col 2 contains city names, col 3 contains establishment names,

Here is the link to the google sheets that I'm using as a source for the form

I am trying to have a code that would generate a google form breaked into sections containing questions with answer items filtered depending on the answer to previous question, i.e., I would like that depending on the department code selected, only cities of this department are available in the next section asking to select a city, and depending on the selected city, only the establishments of the city are avaiblable in the last section asking to select an establishement.

I have this code that works for departments and cities

see the google form that is generated with the bellow code

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)
}

but I don't manage to add another section with establishements after the city selection.

Here is what where I am so far, but I keep geting error messages saying that the array is empty.

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)
}

Any clue how I can adapt the inital code ?

Thanks for your help !

Solution

  • This is to point out what the problem is in the original code.

    When the script is tested, it seems that the error Exception: Array is empty: values shows up, which is what I believe you meant by the statement keep geting error messages saying that the array is empty.

    Upon reviewing the code, the cause of the error is

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

    The script is filtering Département, which is option[0], not Nom_commune, which is option[1].

    You may change that to:

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

    The full code would become:

    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);
}

    Notes: