Refresh form dropdowns from a live Sheet

The Northwind client roster moves all the time — new logos signed, old ones archived — and there is a Google Form somewhere that asks “Which client?” with a dropdown of those names. Every time the roster changes, somebody has to remember to edit the form. They don’t, the form drifts out of date, and the data team starts seeing answers like “see message below”.

The fix is to make the spreadsheet the source of truth and have the form pull from it automatically. This script reads a Choices sheet, finds the right dropdown question by title, and replaces its options with whatever is in the sheet today. Edit the sheet, the form updates within the hour — no opening the form editor at all.

What you’ll need

• A Choices sheet with a header in row 1 and one option per row in column A from row 2 down. Blank cells are ignored, so adding an option is one keystroke.
• The form ID (the long string in the form’s edit URL) and the spreadsheet ID of the choices sheet. Paste both into the constants at the top.
• A dropdown question in the form titled exactly Which client? — the script matches on the title, so rename either side to suit.

The script

// IDs for the form being updated and the sheet that holds the options.
// Both come from the URL of the edit page.
const FORM_ID = '1abcFormId';
const CHOICES_SHEET = '1abcChoicesId';

// The exact title of the dropdown question to refresh. Edit this if your
// form uses a different label.
const QUESTION_TITLE = 'Which client?';

/**
 * Reads the live list of choices from the Sheet and writes them onto the
 * matching dropdown question in the Form. Safe to run on a schedule.
 */
function refreshDropdown() {
  // 1. Read column A from row 2 down. flat() turns the 2D range into a
  //    plain list; filter(Boolean) drops blanks so a stray empty row
  //    doesn't become a blank option.
  const choices = SpreadsheetApp.openById(CHOICES_SHEET).getSheets()[0]
    .getRange('A2:A')
    .getValues()
    .flat()
    .filter(Boolean)
    .map(String);

  // 2. Guard: setChoiceValues throws on an empty array. Better to skip
  //    than to wipe the form's options on a bad read.
  if (!choices.length) {
    Logger.log('Choices sheet is empty — leaving the form untouched.');
    return;
  }

  // 3. Walk every dropdown (LIST) item on the form, looking for the
  //    one whose title matches QUESTION_TITLE.
  const form = FormApp.openById(FORM_ID);
  let updated = false;
  for (const item of form.getItems(FormApp.ItemType.LIST)) {
    if (item.getTitle() === QUESTION_TITLE) {
      item.asListItem().setChoiceValues(choices);
      updated = true;
      Logger.log('Updated "' + QUESTION_TITLE + '" with ' + choices.length + ' choices.');
    }
  }

  // 4. If nothing matched, say so loudly — silent no-ops are how forms
  //    drift out of sync in the first place.
  if (!updated) {
    Logger.log('No dropdown titled "' + QUESTION_TITLE + '" found on the form.');
  }
}

How it works

1. The script reads column A of the first sheet in CHOICES_SHEET, starting at row 2 to skip the header. flat() flattens the range and filter(Boolean) drops blank cells.
2. It guards against an empty list. setChoiceValues([]) would throw, and you do not want a temporarily empty sheet to nuke the form’s options.
3. It opens the form, iterates every LIST (dropdown) question, and matches on title. Other question types are ignored, so the rest of the form is safe.
4. setChoiceValues(choices) replaces the dropdown options in one call — simpler than diffing and adequate for lists up to a few hundred items.
5. If no question matches QUESTION_TITLE, the script logs a clear message rather than failing silently — easier to spot when you have renamed the form question and forgotten to update the constant.

Example run

The Choices sheet looks like this on a Monday:

Clients
Acme Joinery
Bramble & Co
Coastal Logistics

A new client is signed, so someone adds a row:

Clients
Acme Joinery
Bramble & Co
Coastal Logistics
Dovetail Bakery

Within the hour, the form’s “Which client?” dropdown reads Acme Joinery / Bramble & Co / Coastal Logistics / Dovetail Bakery. No-one opened the form editor — and the next response is already correctly tagged.

Trigger it

This is a “set and forget” job. Run hourly so changes propagate quickly without hammering quotas:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add trigger, choose refreshDropdown, event source Time-driven, type Hour timer, interval Every hour.
3. Approve the authorisation prompt. Add a test row to the choices sheet, wait for the trigger to fire, and confirm the form picks it up.

For a less chatty schedule, switch to every 6 hours or run it nightly — the form will just be at most that out of date.

Watch out for

• Match by title is exact, including punctuation and trailing spaces. Rename the form question and you must update QUESTION_TITLE to match — the script logs a clear message when it can’t find the question.
• setChoiceValues does not support an empty array. The guard above logs and exits rather than throwing, which means a bad sheet day will not delete every option on the form.
• This script targets FormApp.ItemType.LIST (dropdowns). For radio buttons or checkboxes use MULTIPLE_CHOICE or CHECKBOX and call the matching asMultipleChoiceItem() or asCheckboxItem() cast instead.
• The form only re-reads choices when it is loaded by a respondent. People who had the form already open will see the old list until they refresh.
• If the choices list grows past a few hundred, dropdowns become hard to use. Consider splitting the question into a category + sub-category, or switching to a text question with autocomplete from a sidebar add-on.