Keeping a dropdown's options in sync with a master list is painful when the list lives in the same cell range as the dropdown itself, so installDepartmentDropdown instead points the validation rule at a separate Lists sheet that can be edited independently.
Editing a data validation rule by hand every time a department is added or renamed means opening the dropdown's configuration dialog, finding the right range, and updating it manually - a step that's easy to forget until someone notices a missing option.
This example builds the validation rule from a live range on the Lists sheet rather than a hard-coded list of strings, so adding a new department to Lists!A2:A automatically becomes available in the dropdown the next time this function runs.
You'll end up with an Employee Directory Department column backed by a single editable source of truth, with the rule itself documented via a note explaining exactly where its options come from.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Location | Sheet | Purpose |
|---|---|---|
| A2:A (dynamic) | Lists | The authoritative list of department names the dropdown pulls from |
| Column D | Employee Directory | Department column where the dropdown validation rule is applied |
| D1 note | Employee Directory | Documents which range on Lists the dropdown is sourced from |
What it does
The function reads how many department names currently exist on the Lists sheet, builds a data validation rule referencing that exact range, and applies the rule to every existing data row in the Department column of Employee Directory, restricting entries to only what's in that range.
- The source range is computed dynamically based on the Lists sheet's current last row
- requireValueInRange with showDropdown true renders an actual dropdown arrow in the cell
- setAllowInvalid(false) blocks manually typed values that aren't in the list
Prerequisites
Because this applies validation to a fixed set of existing rows rather than the whole column, it needs to be re-run (or wrapped in its own trigger) whenever new employee rows are added, or those new rows won't have the dropdown applied automatically.
- A sheet named 'Lists' with department names starting in A2
- A sheet named 'Employee Directory' with a Department column in position D
- Existing rows in Employee Directory that need the validation retroactively applied
Walkthrough
requireValueInRange's second argument, true, enables showDropdown so Sheets renders the familiar dropdown arrow in the cell rather than only validating on entry without any visual affordance.
targetLastRow uses Math.max(targetSheet.getLastRow(), 2) specifically to avoid a negative-length range error when Employee Directory has only a header row and no data yet - without that guard, targetLastRow - 1 could come out to 0 or less.
Edge cases
Deleting a department name from the Lists sheet doesn't retroactively clear any Employee Directory cells that already contain that now-invalid value - existing entries stay as they were typed.
- Renaming a department on Lists doesn't update rows already set to the old name
- Adding new employee rows after this function runs won't have the dropdown unless the function is re-run or triggered automatically
- Sorting the Lists sheet doesn't break the dropdown, since the validation range is positional, not tied to specific string values
Testing
Run the function once, then open the Department column on Employee Directory and confirm a dropdown arrow appears showing exactly the names currently listed on the Lists sheet.
- Add a new department to Lists and re-run the function to confirm it appears in the dropdown
- Try typing a department not in the list and confirm Sheets blocks the entry given setAllowInvalid(false)
- Add a new employee row and confirm the dropdown isn't there until the function runs again
Hardening
Re-running this function manually every time a new employee row is added doesn't scale well past a handful of edits per week, so pairing it with an onChange or onEdit trigger that detects new rows is the natural next step.
- Add an onChange trigger that calls this function whenever rows are inserted into Employee Directory
- Apply the validation to a generously oversized range in advance (e.g., 500 rows) so new entries are always covered without re-running anything
- Add a warning if the Lists sheet ever ends up empty, since an empty source range would produce a dropdown with no valid options at all
Variations
The same requireValueInRange approach supports multiple independent dropdowns fed by different columns on the same Lists sheet, for example adding a second dropdown for Office Location sourced from column B.
- Add a second dropdown for Office Location sourced from Lists!B2:B
- Use requireValueInList instead of requireValueInRange for a genuinely static, rarely-changing set of options
- Combine with conditional formatting to highlight any Department cells that predate the current validation rule
Full code: installDepartmentDropdown()
The function treats the Lists sheet as the single editable source of truth, computing the validation range dynamically so the dropdown always reflects exactly what's currently listed there.
function installDepartmentDropdown() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var listsSheet = ss.getSheetByName('Lists');
var targetSheet = ss.getSheetByName('Employee Directory');
var lastRow = listsSheet.getLastRow();
if (lastRow < 2) return;
var sourceRange = listsSheet.getRange('A2:A' + lastRow);
var rule = SpreadsheetApp.newDataValidation()
.requireValueInRange(sourceRange, true)
.setAllowInvalid(false)
.setHelpText('Choose a department from the Lists sheet.')
.build();
var departmentColumn = 4;
var targetLastRow = Math.max(targetSheet.getLastRow(), 2);
var applyRange = targetSheet.getRange(2, departmentColumn, targetLastRow - 1, 1);
applyRange.setDataValidation(rule);
targetSheet.getRange(1, departmentColumn).setNote('Dropdown values sourced from Lists!A2:A' + lastRow);
}- Line 3: Locates the Lists sheet that holds the authoritative set of department names.
- Line 4: Locates the Employee Directory sheet where the dropdown will actually be applied.
- Line 9: Builds the source range dynamically based on however many departments currently exist on Lists.
- Line 11: Starts building the data validation rule using the dynamic source range.
- Line 13: Blocks manually typed values that don't match anything currently in the source range.
- Line 19: Applies the finished validation rule across every existing data row in the Department column.
- Line 21: Documents on the header cell exactly which range the dropdown's options are sourced from.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before rolling out the dropdown
- 1Lists sheet confirmed to have department names starting in A2
- 2Employee Directory Department column position matches departmentColumn (D)
- 3Function re-run after any bulk addition of new employee rows
- 4setAllowInvalid(false) confirmed appropriate versus allowing free text with a warning
- 5Note on D1 reviewed for accuracy
- 6Considered an onChange trigger for fully automatic coverage of new rows
Frequently asked questions
A range-based rule stays in sync automatically as departments are added, removed, or renamed on the Lists sheet, whereas a hard-coded list of strings in the validation rule itself would need to be edited by hand every time the department list changes.
They keep displaying their existing value, dropdown or not, since data validation only restricts new entries going forward; setAllowInvalid(false) prevents future edits to that cell unless the entered value matches the list.
Yes - call requireValueInRange with the same sourceRange as many times as needed across different target sheets and columns, since the validation rule just references the range, not the sheet it's being applied to.
Extend the applyRange to cover more rows than currently exist, for example the next 500 rows below the last one, so new entries typed into those rows already have the dropdown active without re-running the function.
No - data validation rules move with their cells during a sort just like any other cell formatting, so a sorted Department column keeps its dropdown intact regardless of row order changes.
Call setAllowInvalid(true) instead of false when building the rule; Sheets will still show a dropdown and a warning triangle on non-matching entries, but it won't prevent the user from typing something outside the list.