// Sheets · Apps Script

Set a dropdown list from a range in Google Sheets.

Use Apps Script to attach a data-validation dropdown to any cell that pulls its options from a named range — and stays in sync as that range grows.

“I want to create a dropdown validation rule in Google Sheets via Apps Script that reads its options from a live range, so the list updates automatically when I add new items.”

The script

copy · paste · trigger

setDropdownFromRange.gs

Apps Script

// Attach a live range-backed dropdown to a target cell.
// The validation rule reads from 'Options'!A2:A — updates automatically.
function setDropdownFromRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName('Data');
  var sourceRange = ss.getSheetByName('Options').getRange('A2:A');

  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInRange(sourceRange, true)
    .setAllowInvalid(false)
    .setHelpText('Choose a value from the Options sheet.')
    .build();

  var targetCell = targetSheet.getRange('B2');
  targetCell.setDataValidation(rule);

  SpreadsheetApp.flush();
  Logger.log('Dropdown applied to ' + targetCell.getA1Notation());
}

Generate yours

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

Walkthrough

requireValueInRange vs requireValueInList — the actual difference

The DataValidationBuilder gives you two dropdown methods and the distinction is not cosmetic. requireValueInRange(range, showDropdown) stores a reference to the range object — it resolves that reference each time a user opens the cell, so adding a row to your Options sheet is reflected immediately. requireValueInList(values, showDropdown) accepts a plain array of strings and bakes them into the rule at the moment you call build(). The list is frozen from that point forward.

This is why dropdowns built by a script 'stop updating': whoever wrote the script used requireValueInList, passed it the current values, and the rule has no idea the source sheet changed. I have watched this bite every team that migrates their dropdown management from the UI to a script without reading both method signatures carefully. The UI's 'List from a range' option maps to requireValueInRange; 'List of items' maps to requireValueInList. Match the method to your intent.

The second argument to requireValueInRange is a boolean that controls whether the dropdown arrow is shown in the cell. Passing true matches the default UI behavior. Passing false still enforces the rule — the user just has to know to type a valid value, which is almost never what you want.

Applying the rule to multiple cells at once

setDataValidation accepts any Range, not just a single cell. If you want the same dropdown on every row in a column, pass a multi-row range to getRange and call setDataValidation once. Calling it in a loop — one cell at a time — works but fires one Sheets API write per iteration, which hits quota faster on large sheets and is noticeably slower.

The pattern that scales: targetSheet.getRange('B2:B500').setDataValidation(rule). One rule object, one write. The rule object itself is reusable across multiple ranges in the same script run; build() is cheap and calling it once is fine.

If your target range is dynamic (the sheet grows), getLastRow() gives you the current bottom row. Combine it with getRange(2, 2, sheet.getLastRow() - 1, 1) to cover exactly the occupied rows without hard-coding 500.

When the source range lives on a different spreadsheet

requireValueInRange requires the source range to be in the same spreadsheet as the target cell. Passing a range from a different SpreadsheetApp.openById() call throws a silent failure — the rule simply does not apply, with no error in the execution log. This is one of those Sheets API limits that is not documented prominently.

The workaround is to import the values into a hidden sheet in the same workbook using importRange() in a formula, or to do it programmatically: read the values from the external sheet with getValues(), write them to a staging range in the current spreadsheet, then point requireValueInRange at that staging range. It adds a sync step, but the dropdown stays live relative to the staging range. The first time I hit this I spent an hour convinced my range reference was malformed before checking the cross-spreadsheet constraint.

If the source list is genuinely static — a fixed set of statuses, a short enum — requireValueInList with a hard-coded array is cleaner than a staging range. Use the live-range approach only when the source data actually changes.

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

Why does my dropdown show the right options today but not after I added a new row to the source sheet?: The rule was built with requireValueInList, which baked in a static copy of the values at script-run time. Switch to requireValueInRange pointing at the source range and re-run the setup function once to replace the rule. After that, new rows appear automatically.
Can I set a dropdown from a named range instead of a sheet-and-address string?: Yes. Retrieve it with ss.getRangeByName('MyNamedRange') and pass the result directly to requireValueInRange. Named ranges survive row insertions and deletions better than hard-coded addresses, which makes them a good default for source lists that shift.
How do I remove a data-validation rule from a cell with Apps Script?: Call cell.clearDataValidations(). This removes the rule entirely — the cell reverts to accepting any input. If you want to replace a rule rather than remove it, just call setDataValidation with the new rule; it overwrites the existing one.
Does requireValueInRange work if the source range contains blank cells?: Blanks in the source range show up as empty entries in the dropdown, which looks broken. Use a contiguous range with no gaps, or end the range at the last populated row using getLastRow() rather than an open-ended column reference like A2:A if your list is sparse.