In this article, we will show you how the Google Forms dropdown from Sheet feature works and how to use Google Forms to populate dropdowns from a spreadsheet. Simply follow the steps below.
Here are the steps to integrate a Google Forms dropdown list from a spreadsheet:
Create a new question in your Google Form. Select the 'Dropdown' question type from the options available. This creates a dropdown menu where users can select one option from a list.
To find the Google Sheet linked to your Google Form for recording responses, first navigate to your Google Form and click on the "Responses" tab. In this tab, you will see a green Sheets icon labeled "View in Sheets". Click on this icon to open the linked spreadsheet.
Once in the linked spreadsheet, navigate to the first column (Column A) of a new sheet or an unused part of an existing sheet. List all the options you want to appear in the dropdown menu of your Google Form. Each one should be in a separate row.
The name of the sheet where you listed the options is displayed at the bottom-left corner of the screen. This is usually labeled "Sheet1" by default. Note down this name as you'll need it for your script.
In your Google Form, go to the editing view and check the web address (URL) in your browser's address bar. The Form ID is the long string of numbers and letters located between `/d/` and `/edit` in the URL. Note down this ID for your script.
Navigate to "Extensions" from your Google Sheet and select "Apps Script".
In the script editor, paste the following code:
```
function updateDropdown() {
// Get the form and the spreadsheet
var form = FormApp.openById('<YOUR_FORM_ID>');
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('<SHEET_NAME>');
// Get the data from the spreadsheet
var data = sheet.getRange('A:A').getValues();
var items = data.flat().filter(String);
// Get the dropdown question in the form
var formItems = form.getItems(FormApp.ItemType.LIST);
var listItem = formItems[0].asListItem();
// Update the dropdown options
listItem.setChoiceValues(items);
}
```
Replace `<YOUR_FORM_ID>` with the ID you noted down earlier and `<SHEET_NAME>` with the name of your sheet.
Note that the line in the script: var data = sheet.getRange('A:A').getValues(); will scan all values in column A. You could also make this specific to a range like A1:A23.
It's important to also consider that by default the google form will record answers in sheet1 so it is best to add this dropdown list in a new tab. Sheet2 for example. This will prevent it getting overwritten and your drop down list bbecoming corrupted.
After pasting the script, run the function `updateDropdown`. This executes the script and updates the dropdown choices in your Google Form according to the data listed in Column A of your Google Sheet.
Make sure you click the save icon and then click the run button for the script to execute correctly. Once executed you will see a yellow "execution completed" box at the bottom of the page if the script has run correctly.
Preview and test your Google Form to ensure that the dropdown menu correctly displays all the options you listed in the Google Sheet.
We hope that you now have a better understanding of how to use the Google Forms Dropdown from Sheet feature and how to use Google Forms to populate the dropdown from a spreadsheet.
If you enjoyed this article, you might also like our article on how to make a petition on Google Forms or our article on how to number questions in Google Forms. If you want to know how to send an email notification of each response in Google Forms, we also suggest checking out our detailed guide.
