Creating Conditional Selections in Google Forms using Apps Script

This post is about the dynamic process of creating related dropdowns in Google Forms with Apps Script.

·

10 min read

Intro

I mentioned in the previous post that I was working on a project that made me develop Google's Query Language Wrapper.
At the project's end, its owners asked if it was possible to use a Google Form, from where the users will submit their data instead of accessing any of the sheets in a spreadsheet to take an appointment?
My answer was, and still is, that a Google Form is not the best tool for such a task!
The project took another path and a web based application was created to meet its final goal.

Nota Bene: You'll find at the end of this article, in the Resources section, a link to a GitHub repository for the provided examples as well as links to some sections in Apps Script's documentation.

The little man

Those who read my articles know that I have a little man at the back of my head that keep saying that anything is possible with a strong will, a lot of effort and discipline!
So after the project, my first visit was to the Apps Script's Forms Service of course!
I had to be sure, for myself at first, that Google Form was indeed not the right tool for conditional selections, the little man couldn't just let it go and be...

Baby steps

For me, the most effective approach to comprehending a process is to start with small steps aimed at reaching the intended outcome.
By following this strategy, I can fully grab the concept, envision the required architecture and know early if I should take another path, saving valuable time and energy.

First thoughts

After reading Forms Service's documentation and trying some provided examples, I knew that creating conditional selections in Google Forms was possible, but I didn't know if this possibility had limits, and started wondering if I was not wrong...
The only way to be 100% sure was to simulate the project that I was working on!

Simple Example

A basic conditional selections in Google Forms requires:

  1. An item that supports choices (CheckboxItem, ListItem or MultipleChoiceItem).

  2. An item that leads to the selected choice, the PageBreakItem.

The logic goes as follow:

  1. The user selects an option from the item that supports choices on the first page of the form.

  2. The user is redirected to another page of the form depending on his choice on the first page.

Let's create a form that takes a user to page 2 or 3 depending on his choice.
Open up your Google Drive and create a folder named "Form with page break".
In this folder create 2 files:

  1. pageBreakForm → Google Forms

  2. pageBreakScript → Google Apps Script

In pageBreakForm, remove any created item by default.
In pageBreakScript, remove everything in Code.gs and add the following:

// Global variable
const form = FormApp.openById("theIdOf_pageBreakForm")

function pageBreakFunction() {
    // Create a list item on the first page
    const item = form.addListItem().setTitle("Where do you want to go?")

    // Create 2 pages after the first page
    const pages = [form.addPageBreakItem().setTitle("Page two"), form.addPageBreakItem().setTitle("Page three")]

    // Set list item choices and navigate to according page
    item.setChoices([item.createChoice("Page 2", pages[0]), item.createChoice("Page 3", pages[1])])
}

The ID of the form is located in its URL.
Open pageBreakForm and look at the browser's address bar, the URL look something like this:

https://docs.google.com/forms/d/2mLp9qRs4tUwVx3y6zAe5Dc8Fv1Gb7Hn0JiKj2LmNp/edit

The ID is the string of characters following the /d/ and before the next /.
In this case, the ID is:

2mLp9qRs4tUwVx3y6zAe5Dc8Fv1Gb7Hn0JiKj2LmNp

Now that your code is in place, save and run the function pageBreakFunction(), open pageBreakForm, click on the eye icon to preview your form and test it.
The code is working, you can choose a page from the dropdown and click "Next" to land on it.
But if you look closely, you'll notice that :

  1. "Page two" has a "Back" button getting us back to the homepage of the form and a "Next" button leading to "Page three".

  2. "Page three" has a "Back" button getting us back to the page we came from (homepage or "Page two") and a "Submit" button to submit the form.

A more logical approach would involve submitting the form upon reaching any page, as they offer varying content based on the user's homepage selection. Additionally, the 'Back' button on each page should return us to the homepage.
Let's adjust the code to fix this by adding at the end of pageBreakFunction() this line:

// When reaching a selected page, submit upon completion or get back to the first page
pages.forEach((page) => page.setGoToPage(FormApp.PageNavigationType.SUBMIT))

Save and run the function, then preview the form.
What a mess, the code is duplicating the content!
So each time the function runs, Apps Script executes it and adds the newly generated items to the form.
To fix this issue for now add the following to pageBreakFunction() before everything else:

// Delete all items in the form
const items = form.getItems()
items.forEach((item) => {
    form.deleteItem(item)
})

Save and run the function, then preview the form.
The content is not being duplicated anymore and the form works as expected.

Intermediate example

Setting up and running the simple example was easy but does not mimic a real situation.
The simple example was our first baby step to understand the process and move on to the next level.

In a real world example, the choices/options of the dropdown list item would come from a source and we would create them dynamically as well as their related pages.
Looking back at our simple example, we can see that the pages are an array where we add each page as a PageBreakItem to the form and set the page's title.
After that, we establish the choices/options for the dropdown list item. Each choice/option consists of a title and its associated page from the pages array, identified by its index.
To dynamically create those items we would do the following:

  1. Retrieve the choices/options as an array

  2. Create a dropdown list item

  3. Create the pages based on the choices/options

  4. Create choices dynamically with their related pages

  5. Insert the created choices/options into the dropdown list item

So, our logic would be something like:

// Choices array
const choices = ["Option 1", "Option 2", ...]
// Create list item
const dropdown = form.addListItem()
// Create pages based on choices
const pages = choices.map(choice => form.addPageBreakItem().setTitle(choice))
// Create choices dynamically with their related pages
const dropdownChoices = choices.map((choice, index) => {
    return dropdown.createChoice(choice, pages[index])
})
// Insert the created choices into the dropdown
dropdown.setChoices(dropdownChoices)

Now that we know the dynamic process, let's create a form where the user can choose a destination for his next vacation trip.
In your Google Drive create a folder named "Vacation trip destination".
In this folder create 2 files:

  1. vacationTripDestinationForm → Google Forms

  2. vacationTripDestinationScript → Google Apps Script

In vacationTripDestinationForm, remove any created item by default.
In vacationTripDestinationScript, remove everything in Code.gs, create a Script file by clicking the + icon above the Files sidebar, name this new file continentsData (the .gs extension will automatically be added), add to it this data array and save the file.

Were assuming that our source of choices/options is an array of nested objects where each element in the array represents a continent, and each continent object contains an array of countries, and each country object contains an array of touristic destinations.
This structure is fair enough for an intermediate example.

Create a new Script file, name it deleteFormItems, add the following and save:

function deleteFormItems(form) {
    const items = form.getItems()
    items.forEach((item) => {
        form.deleteItem(item)
    })
}

Now add the following in Code.gs:

// Global variable
const form = FormApp.openById("theIdOf_vacationTripDestinationForm")

function vacationTripDestinationFunction() {
    // Delete all items in the form
    deleteFormItems(form)

    // 1. Get the continents as an array
    const continents = continentsData.map((obj) => obj.continent)

    // 2. Create a dropdown list item and set it as required
    const continentsDropdown = form.addListItem()
    continentsDropdown.setTitle("Which continent would you like to visit?")
    continentsDropdown.setHelpText("Choose a continent for your next vacation trip!")
    continentsDropdown.setRequired(true)

    // 3. Create the pages based on continents
    const pages = continents.map((continent) => form.addPageBreakItem().setTitle(continent))

    // 4. Create choices dynamically with their related pages
    const continentsChoices = continents.map((continent, index) => {
        return continentsDropdown.createChoice(continent, pages[index])
    })

    // 5. Set list item choices and navigate to according page
    continentsDropdown.setChoices(continentsChoices)
}

When you save the file and run vacationTripDestinationFunction(), the script takes between 3 and 5 seconds to complete its execution.
I don't know if the Internet's connection speed is a factor, but that's already a huge amount of time for a simple script to be executed!!!

Now, let's proceed to include the countries beneath the continents using the same process.
Now for each continent we will create a dropdown of available countries.
Put the following code at the end of vacationTripDestinationFunction(), after the fifth step:

// For each page, create a dropdown list item with the available countries for the selected continent
pages.forEach((page) => {
    // Get the selected continent. It's the page title!
    const selectedContinentName = page.getTitle()

    // 1. Get the selected continent's countries as an array
    const selectedContinent = continentsData.find((obj) => obj.continent === selectedContinentName)
    const countries = selectedContinent ? selectedContinent.countries.map((obj) => obj.country) : []

    // 2. Create a dropdown list item and set it as required
    const countriesDropdown = form.addListItem()
    countriesDropdown.setTitle(`Which country in ${selectedContinentName} would you like to visit?`)
    countriesDropdown.setHelpText(`Choose a country in ${selectedContinentName} for your next vacation trip!`)
    countriesDropdown.setRequired(true)

    // 3. Create the pages based on countries
    const countriesPages = countries.map((country) => form.addPageBreakItem().setTitle(country))

    // 4. Create choices dynamically with their related pages
    const countriesChoices = countries.map((country, index) => {
        return countriesDropdown.createChoice(country, countriesPages[index])
    })

    // 5. Set list item choices and navigate to according page
    countriesDropdown.setChoices(countriesChoices)
})

If you run vacationTripDestinationFunction():

  1. The execution time is terrifying!

  2. The form is a mess!

Addressing the concerning execution time is beyond my capacity, as it is yours. However, we can rectify the disorder.
Add the following after the fifth step:

// Get the index where countriesDropdown was created in the form
const countriesDropdownIndex = countriesDropdown.getIndex()
// Get the index of the actual page where countriesDropdown should be moved to
const pageIndex = page.getIndex()

// 6. Move countriesDropdown from its actual index/position in the form to be after/under its related page
form.moveItem(countriesDropdownIndex, pageIndex + 1)

Save the file, run the function and preview the form.
Now that everything is in order, let's write the code for the final step where the user will choose a destination for his vacation trip.
Add the following after the sixth step that corrected the mess:

// For each country, create a dropdown list item with the available destinations for the selected country
countriesPages.forEach((countryPage) => {
    // Get the selected country. It's the page title!
    const selectedCountryName = countryPage.getTitle()

    // 1. Get the selected country's destinations
    const selectedCountry = selectedContinent.countries.find((obj) => obj.country === selectedCountryName)
    const destinations = selectedCountry.destinations

    // 2. Create a dropdown list item with the destinations as choices and set it as required
    const destinationsDropdown = form.addListItem()
    destinationsDropdown.setTitle(`Which destination in ${selectedCountryName} would you like to visit?`)
    destinationsDropdown.setHelpText(`Choose a destination in ${selectedCountryName} for your next vacation trip!`)
    destinationsDropdown.setChoiceValues(destinations)
    destinationsDropdown.setRequired(true)

    // 3. Move destinationsDropdown from its actual index/position in the form to be after/under its related page
    const destinationsDropdownIndex = destinationsDropdown.getIndex()
    const countryPageIndex = countryPage.getIndex()
    form.moveItem(destinationsDropdownIndex, countryPageIndex + 1)

    // 4. Submit the choice since it's the last step in the form!
    countryPage.setGoToPage(FormApp.PageNavigationType.SUBMIT)
})

Save the file, run the function and preview the form.
Try the form, submit multiple choices, view the summary of the collected responses of the form.
Everything works as expected and this intermediate step is a pretty good example on how to create conditional selections in Google Forms with Apps Script.

Concluding Insights

I intended, at the beginning of this article, to provide an advanced example as well, maybe I'll add it later as a separate article, but for now the intermediate example is more than enough to grasp the process' essence and understand the following:

  1. In a real world example the data source will change without any doubt and we will not be present each time it changes to execute the script and update the form.

  2. We could set a trigger to update the form each time a user opens it, but looking at the script's execution time we know that it's absolutely not a solution!

  3. When the number of conditions and choices grows, we enter in a deeply nested conditional statements zone leading to a nested loop of hell!

  4. When the number of conditions and choices grows, the script's execution time becomes unacceptable for any developer or user!

  5. The confirmation page that displays after submitting a Google Form is controlled by Google's infrastructure and cannot be altered using scripts.

So what is the benefit of creating conditional selections in Google Forms using Apps Script?
I could only say that it saves a lot of time compared to a situation where you have to manually create a form with a considerable amount of conditional choices.

I hope that you'll find this article useful.

Resources

Links:

  1. Form

  2. ListItem

  3. Choice

  4. PageNavigationType

  5. GitHub repository