Status columns full of “approved”, “Approved ”, and “APPROVED!!” break every script that reads them. setDropdownFromList(targetRange, source, opts) applies a dropdown (data validation) to a range from either a string array or the unique, non-blank values of another range, like a Clients list on a Config tab. By default, values not in the list are rejected and the dropdown arrow is shown. Both are options.
When to use
- Locking a Status column to Pending / Approved / Rejected
- Keeping a Client column in sync with the master client list
- Setup scripts that build a new tab with the right validation every time
How to use this snippet
/**
* Apply a dropdown to targetRange from an array or another range's values.
*
* @param {GoogleAppsScript.Spreadsheet.Range} targetRange e.g. sheet.getRange('E2:E')
* @param {string[]|GoogleAppsScript.Spreadsheet.Range} source
* array of options, or a Range whose unique non-blank values become the options
* @param {{allowInvalid?: boolean, showDropdown?: boolean}=} opts
* allowInvalid default false: typing something not in the list is rejected.
* true: allowed, but flagged with a warning triangle
* showDropdown default true: show the arrow / chip picker
* @return {number} number of options applied
*/
function setDropdownFromList(targetRange, source, opts) {
opts = opts || {};
var list = source;
// 1. A Range as source: flatten, trim, skip blanks, de-duplicate in order.
if (source && typeof source.getValues === 'function') {
var seen = {};
list = [];
source.getValues().forEach(function (row) {
row.forEach(function (v) {
var s = String(v == null ? '' : v).trim();
if (s && !seen[s]) {
seen[s] = true;
list.push(s);
}
});
});
}
if (!Array.isArray(list) || !list.length) {
throw new Error('setDropdownFromList: no values to use');
}
// 2. Build and apply the rule. This replaces any existing validation on the range.
var rule = SpreadsheetApp.newDataValidation()
.requireValueInList(list.map(String), opts.showDropdown !== false)
.setAllowInvalid(opts.allowInvalid === true)
.build();
targetRange.setDataValidation(rule);
return list.length;
}
Example
function setupOrdersValidation() {
var ss = SpreadsheetApp.getActive();
var orders = ss.getSheetByName('Orders');
// Fixed list
setDropdownFromList(orders.getRange('E2:E'), ['Pending', 'Approved', 'Rejected']);
// From a Config list (unique, non-blank, in order)
var clients = ss.getSheetByName('Config').getRange('A2:A');
var n = setDropdownFromList(orders.getRange('B2:B'), clients, { allowInvalid: true });
ss.toast(n + ' clients in the Client dropdown', 'Setup', 5);
}
Tips:
- The list is a snapshot. When the Config list changes, re-run setup (or put it on a trigger). For a live link, use
requireValueInRange(range)instead, but it shows blanks and duplicates exactly as they are in the range. setDataValidationreplaces whatever validation the range had. Apply it to the data rows (E2:E), not the header.- Existing cell values aren’t changed. Old “approved!!” values stay until someone fixes them (they get the warning triangle).
- Very long lists make clunky dropdowns. For hundreds of options, a live range plus search-as-you-type is friendlier.
Tip: NitroGAS Co-Pilot can write a re-runnable setupValidation() that uses setDropdownFromList for every status-like column in your sheet.
Happy Coding!
