G-Sweet (Connecting Google Sheets to Google Forms)

If you subscribe to the Google Suite of products (G-Suite), chances are you will have come across any number of Google’s handy apps, like Google Sheets, Scripts or Forms. The latter, in particular, can be an easy way to collect data, like user-feedback, or distribute quizzes to a group of students. Google Forms offers users the ability to drag and drop questions in the form of text boxes, multiple choice options, dropdown lists and more. This makes it easy for you to create simple forms with a few questions for users to answer. In a perfect world, that would be enough. Unfortunately, you’ll find you may end up in a situation where you want to offer users the ability to choose from a dropdown list of hundreds of options. Manually adding each option to the Google Form can be a daunting task. Rather than becoming a robot, you could, instead have your dropdown list read the options from a Google Sheet. In this way, too, if you ever need to modify the list of options, you can do so by modifying the Google Sheet and not the actual Form.

Let’s see how you would do that.

First off, you’re going to need a Google Form. For the purpose of this article, we’re going to make a form for people to choose their favorite NBA teams. Ensure that you select the Dropdown option for your question. We won’t, however, be manually adding any options.

Image for post
Image for post

Next, we’ll need a Google Sheet with a column that stores our options. This sheet is what we’ll programmatically link to our form in order to feed our Dropdown.

A listing of all 30 NBA teams, in a Google Sheet.
A listing of all 30 NBA teams, in a Google Sheet.
A listing of all 30 NBA teams, in a Google Sheet.

Now that we have our form and our sheet, we can proceed to work real magic and link the two. To do so, we’ll be creating a Google Script for our Google Sheet.

Image for post
Image for post

As shown above, on your Google Sheet, go to Tools->Script editor, which will open a Google Script editor for you to code in. We’ll be replacing the default function with the below code.

Above we have a function called, populateDropdown(). The actual name doesn’t matter. First, we connect to your Google Form, using the unique ID. This ID can be obtained from the URL shown while editing the form. Example: https://docs.google.com/forms/d/<Google Form ID>/edit

We then create a variable to feed data into our Dropdown List. This is done using the dropdown ID. To obtain the dropdown ID, go to the Google Form edit page, right click->Inspect Element, then use CTRL+f to Search for an element. Search for data-item-id, and ensure the element matches your Question on the form. It should look something like this:

Obtaining data-item-id for Dropdown
Obtaining data-item-id for Dropdown
Obtaining data-item-id for Dropdown

Next, we store all values in the first column of the Sheet into a variable:

2 is used to start at the second row, since our data has headers. 1 signifies that the data is in the first column of the Sheet. We iterate over the values, skipping all empty strings and adding the valid values to another array. Finally, we use the setChoiceValues() function to populate our Dropdown (teamList) with the values from the nbaTeams array.

Image for post
Image for post

Now that the coding is done, while in Google Scripts, go to Edit->Current project’s triggers. This will open a new tab where we can ad . a Trigger for when the script should run to populate the list. Two basic triggers are “Time-based”, which updates the listing every minute, and “From Spreadsheet-On Change”, which will update the listing if the Google Sheet is modified.

Image for post
Image for post
Two basic Triggers for the Google Script

Considering you copied the code from above, and your data-item-id, form ID and Google Spreadsheet Sheet name (not spreadsheet name) are correct, your form should be properly populated with the options from your Google Sheet.

Image for post
Image for post

That’s it! Happy Scripting!

Interested in Computer Science, education and world domination.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store