I get 'You do not have permission to call FormApp.openById' — what's wrong?

The script needs the https://www.googleapis.com/auth/forms scope. Run the function once manually from the Apps Script editor and click through the OAuth consent screen. If you are using a standalone script (not bound to the form), make sure the account running it has edit access to the form, not just view access.

Can I read from a named range instead of a fixed column like A2:A?

Yes. Replace sheet.getRange('A2:A') with SpreadsheetApp.openById(SHEET_ID).getRangeByName('ProjectList'), where ProjectList is a named range you defined in the spreadsheet via Data > Named ranges. The rest of the script is unchanged. Named ranges survive column insertions and are easier to document.

Does setChoiceValues preserve the order of items as they appear in the sheet?

Yes. The array is passed in as-is, and the dropdown renders choices in array order. Sort your sheet column if you want alphabetical order; the script does not sort automatically.

Will existing form responses break if I update the dropdown choices?

No. Recorded responses store the text value the respondent selected at submission time. Changing the choices going forward does not alter or invalidate past responses. If you remove a value that respondents could previously select, it simply no longer appears as an option for new submissions.

// Forms · Sheets · Apps Script

Populate a form dropdown from a spreadsheet in Google Forms.

How to sync a Google Forms dropdown with a spreadsheet column using Apps Script — including the blank-value filter that prevents setChoiceValues from throwing a runtime error.

“I want to pull a list from a spreadsheet column and use it as the choices in a Google Forms dropdown, without manually updating the form every time the list changes.”

The script

copy · paste · trigger

syncDropdown.gs

Apps Script

// syncDropdown.gs — pull column A from sheet and push to form dropdown
// Set FORM_ID, SHEET_ID, ITEM_TITLE before running

const FORM_ID = 'YOUR_FORM_ID';
const SHEET_ID = 'YOUR_SHEET_ID';
const ITEM_TITLE = 'Select a project';

function syncDropdown() {
  var sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
  var raw = sheet.getRange('A2:A').getValues();

  var choices = raw
    .map(function(row) { return String(row[0]).trim(); })
    .filter(function(v) { return v !== '' && v !== 'undefined'; });

  if (choices.length === 0) throw new Error('No choices found — aborting to avoid clearing the form.');

  var form = FormApp.openById(FORM_ID);
  var items = form.getItems(FormApp.ItemType.LIST);
  var target = items.filter(function(i) { return i.getTitle() === ITEM_TITLE; })[0];
  if (!target) throw new Error('List item not found: ' + ITEM_TITLE);

  target.asListItem().setChoiceValues(choices);
  Logger.log('Synced ' + choices.length + ' choices to "' + ITEM_TITLE + '"');
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why getRange('A2:A') returns blank rows, and why that breaks setChoiceValues

When you call getRange('A2:A').getValues() on a sheet, Apps Script returns a two-dimensional array that extends to the last row of the sheet's grid — not the last row with data. A sheet with 10 project names in rows 2–11 and a grid ceiling at row 1000 will hand you 999 rows, most of them [''].

setChoiceValues throws the error 'Choice values cannot be empty strings' the moment any element in the array is a blank string. The fix is a filter step before you pass the array in. The script above maps each row to a trimmed string first, then filters out anything that is empty or the string 'undefined' (which String() produces when a cell contains the literal undefined value from a formula gone wrong).

The guard after the filter is equally important: if the sheet column is empty for any reason — wrong sheet tab, stale SHEET_ID, an accidental clear — the function throws early rather than silently replacing your dropdown with nothing. I burned twenty minutes the first time this happened because the form looked fine in the editor but all choices had vanished for live respondents.

Finding the right form item by title instead of index

getItems(FormApp.ItemType.LIST) returns every list-type item in the form. Filtering by title is more stable than slicing by index position, since a collaborator adding a question above yours shifts every index.

ITEM_TITLE must match the question text exactly, including capitalization and trailing spaces. If you have duplicate question titles — a real annoyance in longer forms — the filter picks the first match. Rename one of them before relying on this script.

asListItem() is the cast that exposes setChoiceValues. getItems returns FormItem objects; without the cast you get 'setChoiceValues is not a function'. The ItemType.LIST filter makes the cast safe — every item in that list is guaranteed to be a ListItem.

Automating the sync with a time-driven trigger

Running syncDropdown manually is fine for a one-off import. For a list that changes regularly — a roster, a product catalog, a set of open tickets — add a time-driven trigger so the form stays current without any human action.

In the Apps Script editor, open Triggers (the clock icon), click Add Trigger, choose syncDropdown as the function, select Time-driven, and pick a cadence. Hourly covers most use cases. The trigger runs under your account's authorization, so the form and sheet both need to be accessible to your Google account.

One quota to know: Apps Script has a daily trigger runtime limit of 6 minutes per day on the free tier (90 minutes on Workspace). syncDropdown typically runs in under one second, so a 24-run-per-day hourly schedule uses roughly 24 seconds of that budget. Not a concern in practice, but worth knowing if you stack many triggers on the same account.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

I get 'You do not have permission to call FormApp.openById' — what's wrong?: The script needs the https://www.googleapis.com/auth/forms scope. Run the function once manually from the Apps Script editor and click through the OAuth consent screen. If you are using a standalone script (not bound to the form), make sure the account running it has edit access to the form, not just view access.
Can I read from a named range instead of a fixed column like A2:A?: Yes. Replace sheet.getRange('A2:A') with SpreadsheetApp.openById(SHEET_ID).getRangeByName('ProjectList'), where ProjectList is a named range you defined in the spreadsheet via Data > Named ranges. The rest of the script is unchanged. Named ranges survive column insertions and are easier to document.
Does setChoiceValues preserve the order of items as they appear in the sheet?: Yes. The array is passed in as-is, and the dropdown renders choices in array order. Sort your sheet column if you want alphabetical order; the script does not sort automatically.
Will existing form responses break if I update the dropdown choices?: No. Recorded responses store the text value the respondent selected at submission time. Changing the choices going forward does not alter or invalidate past responses. If you remove a value that respondents could previously select, it simply no longer appears as an option for new submissions.

// one good script a week