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.
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.
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.
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.
function populateDropdown() {
// call your form and connect to the drop-down item
var form = FormApp.openById("your form ID");
var teamList = form.getItemById("the dropdown ID").asListItem();// identify the sheet where the data resides needed to populate the drop-down
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("The name of the Sheet containing the data");// grab the values in the first column of the sheet - use 2 to skip header row
var sheetValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues();var nbaTeams = [];// convert the array ignoring empty cells
for(var i = 0; i < sheetValues.length; i++)
if(sheetValues[i][0] != "")
nbaTeams[i] = sheetValues[i][0];// populate the drop-down with the array data
teamList.setChoiceValues(nbaTeams);
}
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:
Next, we store all values in the first column of the Sheet into a variable:
var sheetValues = names.getRange(2, 1, names.getMaxRows() — 1).getValues();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.
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.
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.
That’s it! Happy Scripting!
