Google Forms AutomationDifficulty: Intermediate⚡ onEdit or Hourly Trigger

Populate Google Forms Dropdowns Dynamically from Spreadsheet

Reads dynamic inventory, available event time slots, or staff names from Google Sheets and automatically updates dropdown choices in an active Google Form.

Required OAuth Scopes: https://www.googleapis.com/auth/forms • https://www.googleapis.com/auth/spreadsheets
Code.gs (Apps Script V8 Runtime)
function syncFormDropdownWithSheet() {
  const formId = 'PASTE_FORM_ID_HERE';
  const form = FormApp.openById(formId);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Options');
  
  // Read choices from Column A
  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues();
  const choices = data.map(r => r[0]).filter(String);
  
  // Find target dropdown question by title
  const items = form.getItems(FormApp.ItemType.LIST);
  items.forEach(item => {
    if (item.getTitle() === 'Select Product / Service') {
      const listItem = item.asListItem();
      listItem.setChoiceValues(choices);
    }
  });
}

Line-by-Line Code Explanation

Line 2-4: Resolves form and spreadsheet data tab.
Line 7-8: Maps non-empty values from Column A into an array of string choices.
Line 11-17: Locates the dropdown question in the form and updates choice values.

Step-by-Step Setup & Deployment

  1. Create a dropdown question in Google Forms titled "Select Product / Service".
  2. Put available choices in Column A of an "Options" sheet tab.
  3. Set an hourly trigger to keep dropdown choices synchronized.

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

  • Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
  • Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
  • OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More Google Forms Automation Snippets