Dynamic Google Form by dropdown answer using Apps Script

readme.md

Dynamic Google Form by dropdown answer using Apps Script

Prerequisites

1. Create a 2 wheet in the Google Form's connected Google Sheet. In this example the sheet name will be location and others
2. For the sheet location, make the sheet like this:

3. For the sheet others, make the sheet like this

Code

This code is using Apps Script. You can open the Google Sheets Apps Script.

function populateFormV2() {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let form = FormApp.openByUrl(spreadsheet.getFormUrl());

  var sheets = spreadsheet.getSheetByName('location');

  var [header, ...data] = sheets.getDataRange().getDisplayValues();
  
  var location = form.getItems().filter((item) => item.getTitle() == "Location")[0].asListItem()
  location.setChoices([
    location.createChoice('temp', FormApp.PageNavigationType.SUBMIT)
  ])

  // others pagebreak
  var otherSheets = spreadsheet.getSheetByName('other');
  var otherData = otherSheets.getDataRange().getDisplayValues();
  var otherPageBreak = form.addPageBreakItem().setTitle(`Others`).setGoToPage(FormApp.PageNavigationType.SUBMIT);
  var otherPageBreakIndex = otherPageBreak.getIndex()
  var otherItem = form.addListItem().setTitle('Choose the type of fault below');
  var otherChoices = []
  for (let i = 0; i < otherData.length; i++) {
    const otherChoice = otherData[i];
    otherChoices.push(otherItem.createChoice(otherChoice, FormApp.PageNavigationType.SUBMIT))
  }
  otherItem.setChoices(otherChoices);
  form.moveItem(otherItem.getIndex(), otherPageBreakIndex+1)  

  // delete all section except Others section
  var allPageBreaks = form.getItems(FormApp.ItemType.PAGE_BREAK)
  if (allPageBreaks.length > 0) {
    allPageBreaks.filter(function(element) {
      return element.asPageBreakItem().getIndex() != otherPageBreakIndex;
    }).forEach((pageBreak) => {
      console.log(pageBreak.asPageBreakItem().getTitle())
      let indexPageBreak = pageBreak.asPageBreakItem().getIndex()
      let indexQuestionPageBreak = indexPageBreak + 1
      console.log(indexPageBreak, indexQuestionPageBreak)
      form.deleteItem(indexQuestionPageBreak) // the question
      form.deleteItem(indexPageBreak) // the page break
    })
  }

  // populate all section
  var newChoices = [];
  for (let index = 0; index < data.length; index++) {
    const element = data[index];

    if (element[1] == "*Others") {
      newChoices.push(location.createChoice(element[0], otherPageBreak))
      continue
    }

    // create new page break
    var pageBreak = form.addPageBreakItem().setTitle(`Fault category of ${element[0]}`).setGoToPage(FormApp.PageNavigationType.SUBMIT);
    var pageBreakIndex = pageBreak.getIndex()

    var item = form.addListItem().setTitle(`Choose the type of fault below`);
    var arrChoice = element[1].split('||')
    var choices = []
    for (let i = 0; i < arrChoice.length; i++) {
      const choice = arrChoice[i];
      choices.push(item.createChoice(choice, FormApp.PageNavigationType.SUBMIT))
    }
    
    item.setChoices(choices);

    form.moveItem(item.getIndex(), pageBreakIndex+1)

    newChoices.push(location.createChoice(element[0], pageBreak))

    console.log('-----------NOT OTHERS-----------')
  }
  location.setChoices(newChoices)

  spreadsheet.toast('Google Form Updated !!');
}

Don't forget to set the trigger to update the Google Form 😊

Simple Explanation

I create the new section called "Others". All the Location dropdown that have *Others fault types, will redirect into the Others section. If the Location has some fault types, then we create a new section (example: Section ABC) dedicated for the selected location, and we redirect the user to go into the that new section (Section ABC).

Open for suggestion please. Thank you!