Auto populate Google Form options from Sheet via Apps Script

Introduction

Creating a Google Form with dropdown or multiple‑choice questions often requires a long list of options. Manually typing each choice is time‑consuming and error‑prone, especially when the list originates from a constantly evolving data source such as a Google Sheet. Fortunately, Google Apps Script lets you connect a Sheet to a Form and bulk‑populate question options in just a few clicks. In this article you will learn how to prepare your spreadsheet, set up the corresponding form, write a short script that reads the sheet values, and automate future updates so your form always reflects the latest data. By the end, you’ll be able to streamline surveys, quizzes, and registration forms with dynamic, sheet‑driven options.

Preparing the Google Sheet

Before any code is written, the source data must be clean and well‑structured. Follow these steps:

• Use a single column for the list you want to import (e.g., Column A for product names).
• Remove blanks and duplicates – a simple “=UNIQUE(A2:A)” formula can help.
• Name the range (Data → Named ranges) so the script can reference it easily, e.g., Product_List.
• Keep the sheet in the same Drive folder as the Form; this simplifies permissions.

Setting Up the Google Form

Create the form that will receive the options:

• Add a Dropdown or Multiple choice question where the options will appear.
• Give the question a clear title (e.g., “Select a product”).
• Leave the options area empty – the script will overwrite any placeholder values.
• Open the script editor via Extensions → Apps Script while the form is active.

Writing the Apps Script to Pull Data

The core of the solution is a short Apps Script function that reads the named range from the sheet and assigns it to the form question. Below is a ready‑to‑use script:

• Step 1: Connect to the spreadsheet

  var ss = SpreadsheetApp.openById('SPREADSHEET_ID');

• Step 2: Get the values

  var values = ss.getRangeByName('Product_List').getValues();

• Step 3: Flatten the 2‑D array

  var options = values.flat().filter(String);

• Step 4: Locate the target question

  var form = FormApp.openById('FORM_ID');
  
  var items = form.getItems(FormApp.ItemType.MULTIPLE_CHOICE); // or .DROPDOWN
  
  var question = items[0].asMultipleChoiceItem(); // adjust index as needed

• Step 5: Set the choices

  question.setChoiceValues(options);

Replace SPREADSHEET_ID and FORM_ID with the actual IDs from the URL. Save the script and run populateOptions(). The first execution will ask for authorization; grant the required permissions.

Automating Updates and Handling Changes

Data in the sheet may change daily. To keep the form synchronized without manual triggers, add a time‑driven trigger:

• In the Apps Script editor, click Triggers → Add Trigger.
• Select the populateOptions function, choose “Time‑based”, then set the frequency (e.g., “Every hour” or “Midnight‑to‑midnight”).
• Optional: add an onEdit simple trigger if you prefer immediate updates when the sheet is edited.

When the script runs, it automatically clears previous choices and replaces them with the latest list, ensuring respondents always see current options. If the named range is expanded or reduced, the script adapts because it always reads the full range.

Tips, Troubleshooting, and Best Practices

Even a small oversight can cause the script to fail. Keep these pointers in mind:

• Permissions – Both the Form and the Sheet must be owned by the same Google account or shared with edit rights.
• Data type – Ensure cells contain plain text; formulas that return errors will break the script.
• Multiple questions – Duplicate the code block for each question, adjusting ItemType and index accordingly.
• Logging – Use Logger.log() inside the function to debug unexpected values.
• Version control – Save a copy of the script before major changes; Apps Script provides revision history.

Conclusion

Bulk‑adding options to Google Form questions from a Google Sheet transforms a tedious manual task into a seamless, automated workflow. By first preparing a clean, named range in the spreadsheet, then creating a corresponding form question, and finally connecting the two with a concise Apps Script, you gain real‑time synchronization that scales with any data set. Adding time‑driven triggers ensures the form stays up‑to‑date without extra effort, while best‑practice tips help you avoid common pitfalls. Implementing this method not only saves time but also reduces errors, allowing you to focus on crafting effective surveys and questionnaires rather than managing repetitive data entry.