A status or priority column that accepts any typed text inevitably ends up with inconsistent values like Done, done, and DONE all meaning the same thing, which breaks any formula or filter that expects an exact match.
This tutorial applies a dropdown data validation rule to a Tasks sheet's status and priority columns, restricting input to a fixed list of allowed values and rejecting anything typed outside that list.
requireValueInList is one of several validation rule types SpreadsheetApp supports, and it is the right choice here because the allowed values are a small, known set rather than a range of numbers or a reference to another sheet's data.
Both validation rules are configured with setAllowInvalid(false), which actively blocks a mismatched entry rather than merely showing a warning, keeping the columns clean without relying on anyone remembering to pick from the dropdown voluntarily.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Range | Validation |
|---|---|---|
| Lists | A2:A | Source list Status values |
| Tracker | C2:C | requireValueInRange dropdown |
| Strict | setAllowInvalid(false) | Reject free text |
What it does
addStatusDropdownValidation builds two separate list-based validation rules, one for a status column and one for a priority column, and applies each to its own range on the Tasks sheet.
Prerequisites
requireValueInList takes an array of allowed strings and a boolean controlling whether Sheets shows a dropdown arrow in the cell, which this tutorial sets to true so users see and can click through the available options.
Walkthrough
No special authorization is required beyond normal spreadsheet edit access; validation rules apply the same way whether set through the Data menu or through this kind of script.
Edge cases
setHelpText adds a short tooltip shown when a cell with the rule is selected, which is useful for the priority column especially, where the meaning of each level might not be obvious to every user.
Testing
setAllowInvalid(false) causes Sheets to reject a manually typed value that is not in the list, popping up its own validation warning rather than silently accepting bad input, which is different from the default behavior that only shows a warning icon.
Hardening
After running addStatusDropdownValidation, click into a cell in each configured range to confirm the dropdown arrow appears, and try typing a value outside the list to confirm Sheets rejects it rather than accepting it with just a warning.
Variations
If the list of allowed statuses or priorities is likely to change over time, consider requireValueInRange pointing at a small reference sheet instead of requireValueInList, since updating a range of cells does not require editing and rerunning the script the way changing a hardcoded array does.
Full code: addStatusDropdownValidation()
Run addStatusDropdownValidation once, then confirm both the status and priority columns show a dropdown arrow and reject values outside the allowed list.
function addStatusDropdownValidation() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Tasks');
var statusRange = sheet.getRange('C2:C500');
var priorityRange = sheet.getRange('E2:E500');
var statusRule = SpreadsheetApp.newDataValidation()
.requireValueInList(['Not Started', 'In Progress', 'Blocked', 'Done'], true)
.setAllowInvalid(false)
.setHelpText('Choose a status from the dropdown list.')
.build();
var priorityRule = SpreadsheetApp.newDataValidation()
.requireValueInList(['Low', 'Medium', 'High', 'Urgent'], true)
.setAllowInvalid(false)
.setHelpText('Choose a priority level.')
.build();
statusRange.setDataValidation(statusRule);
priorityRange.setDataValidation(priorityRule);
}- Line 3: Targets the status column's data range.
- Line 4: Targets the priority column's data range separately.
- Line 6: Restricts status input to a fixed, known list of values.
- Line 7: Rejects typed values that are not in the allowed list.
- Line 8: Adds a tooltip explaining what to choose.
- Line 17: Applies both validation rules to their own ranges.
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 you run: add dropdown validation
- 1Allowed value lists match exactly what downstream formulas or filters expect
- 2setAllowInvalid(false) applied everywhere strict enforcement is required
- 3Dropdown arrow argument set to true so users can see the available options
- 4Help text added for any list whose values are not self-explanatory
- 5Validation ranges cover the full extent of rows expected to be used
- 6Rejected input tested by typing a value outside the list
- 7Plan noted for updating the list if allowed values are expected to change
Frequently asked questions
requireValueInList takes a fixed array of strings written directly in the code, while requireValueInRange points at a range of cells elsewhere in the spreadsheet, which is easier to update without touching the script if the allowed values change often.
By default, Sheets shows a small warning indicator on invalid entries but still accepts them; setting allowInvalid to false makes Sheets reject the entry outright with a rejection dialog instead.
It tells Sheets to render a small arrow in validated cells that opens the list of allowed values when clicked, which is the visible dropdown behavior most people expect from this kind of validation.
Yes, as this tutorial shows, building a separate DataValidationBuilder and calling setDataValidation on a separate range for each column works fine within a single function.
Validation only affects new entries or edits going forward; existing values that do not match the list are left untouched until someone edits that specific cell.
Call range.clearDataValidations(), which removes any validation rule from that range without affecting the values already entered in it.