How to Make Google Form Options Dynamic

Great for making scheduling apps in Google Forms

Photo by Elle Cartier on Unsplash

If you have ever used a Google Form to coordinate an event where you want to limit future selections based on prior user choices, you will find this tutorial incredibly helpful. You could use this for organizing a potluck, parent-teacher conferences, etc. The best part is it’s free.

What am I making?

For this tutorial I am going to make a super simple scheduling app. Once a user selects a day it will automatically update the form so that day will not be available to the next user.

Estimated Time to Complete Tutorial: 15 to 30 minutes

What You Need

• Google Sheets
• Google Form
• Knowledge of JavaScript

Step 1: Create a Google Sheet

Then fill in the Google Sheet with what you want your options to be. For this example I did Monday through Friday.

Step 2: Create a Google Form

In the Toolbar go to Tools ➡️ Create Form

Step 3: Open the Script Editor on the Form

Top right of the screen you should see three dots. Click that for a drop down to appear, and you will be able to select the option Script Editor.

Step 4: Connect the Google Sheet to the Google Form Script

You will need to copy (Command+C or CTRL + C) the URL from the Google Sheet you made earlier and paste that into your code where you see ‘Your URL HERE’

let ss = SpreadsheetApp.openByUrl(‘Your URL Here’)

Make sure your URL is inside the single quotes.

Step 5: Connect the Google Form to the Script

This will allow you to interact with the Google Form in later steps.

let form = FormApp.getActiveForm()

Step 6 : Create the function getCurrentOptions

We will use this to get the currently available options for the Google Form.

const getCurrentOptions = () => {
    let sheet1 = ss.getSheetByName(‘Sheet1’);
    let data = sheet1.getDataRange().getValues().map(x=>x[0]);
    return data
}

Go to the toolbar and select Run, making sure that getCurrentOptions is selected in the toolbar.

The output should look like this:

Step 7 : Create the function getQuestionIDs

To be able to change the selections on a Google Form we need to make sure we know what question we are changing. To select questions Google uses IDs, so we will make a function that will tell us which ID is associated with each question.

const getQuestionIDs = () => {
    form.getItems().forEach(question => {
        console.log(`Question Title: ${question.getTitle()}
        Question ID: ${question.getId()}`);
    })
}

Once you type/copy this you will go to the top toolbar and make sure you see getQuestionIDs on the toolbar. Then select Run.

WARNING: You will most likely get an Authorization required pop-up. To enable this you need to select Review Permissions and then a sign-in with Google pop-up will appear. Select the Google Account of the Google Drive you are using. Click Advanced. Then click Go to Untitled project (unsafe). I know that might seem scary, but since you are the developer of this code you shouldn’t have much to worry about. Google will tell you what you are giving your script permission to do and then you just click Allow.

In your execution log you will see something that looks like this:

Now, copy the Question ID that is in the Execution log since we will be using it in the next step.

Step 8: Create the function setCurrentOptions

Now that we know what question we want to change, its is time to change the available options. We will use the output from our getCurrentOptions function we just made to fill in the options on our form.

const setCurrentOptions = () => {
    let id = ‘YOUR ID HERE’
    let currentOptions = getCurrentOptions();
     
form.getItemById(id).asMultipleChoiceItem().setChoiceValues(currentOptions);
}

Now if you Run that function and take a look at your Google Form you should see the options listed as the available choices.

Step 9: Create a function called onFormSubmit

This is the function that we will have run every time that the form submits. This is important because after a form is submitted we will want to update our form to make sure it reflects what options are still available.

Within this one function we will actually have three functions run. Two of them will be new, and then we will reuse one that we made earlier.

const onFormSubmit = () => {
    let latestResponse = getLatestResponse();
    let optionSelected = latestResponse[1];
    updateOptions(optionSelected);
    setCurrentOptions()
}

Step 10: Create a function called getLatestResponse

This will get the latest response that has been recorded on the Form Responses 1 sheet.

const getLatestResponse = () => {
    let formResponseSheet = ss.getSheetByName(‘Form Responses 1’);
    let latestResponse =  formResponseSheet.getDataRange().getValues().pop()
    return latestResponse
}

Step 11: Create a function called updateOptions

Now that we have the latest form submission, we want to make sure we remove that option from our Google Sheet so that a future form responder will not be able to select that option.

const updateOptions = (optionSelected) => {
    let sheet1 = ss.getSheetByName(‘Sheet1’);
    let row = sheet1.getDataRange().getValues().map(x=>x[0]).indexOf(optionSelected) + 1;
    sheet1.deleteRow(row);
}

Step 12: Set up a trigger for the onFormSubmit Function

Go the sidebar and select Triggers, and then in the bottom right click the button that says Add Trigger.

Set up the trigger to the specifications below

All Done!

You now have a form that will update its choices based on what other users have submitted!

If you have questions or need help doing something like this for your own project, be sure to put a comment in the comments section and tag me. I will try to clarify or help guide you in your own project.