Pick Category = Hardware in column B and column C should only offer Laptop, Monitor, Dock. Change B to Software and C should switch to License, Seat, and the old Laptop pick should go away. setDependentDropdown(cell, lookup, opts) does that from an edit trigger: it takes the edited cell and a map of { firstPick: [options] }, rebuilds the validation rule on the next column (or opts.offset columns over), and clears the dependent value if it's no longer one of the options.
When to use
- Category → item, country → region, client → project pickers
- Any two-level choice where the second list depends on the first
- Sheets where people keep picking a “Laptop” license
How to use this snippet
/**
* Rebuild the dependent dropdown next to an edited cell.
*
* @param {Range} cell the edited cell (e.range from onEdit)
* @param {Object<string, string[]>} lookup { firstPick: [allowed options] }
* @param {Object=} opts { offset: 1 (columns to the right), allowInvalid: false }
* @return {{options: string[], cleared: boolean}}
*/
function setDependentDropdown(cell, lookup, opts) {
opts = opts || {};
var target = cell.offset(0, opts.offset || 1);
var pick = String(cell.getValue());
var options = lookup[pick] || [];
var current = String(target.getValue());
if (!options.length) {
target.clearDataValidations();
} else {
var rule = SpreadsheetApp.newDataValidation()
.requireValueInList(options, true)
.setAllowInvalid(!!opts.allowInvalid)
.build();
target.setDataValidation(rule);
}
var cleared = false;
if (current && options.indexOf(current) === -1) {
target.clearContent();
cleared = true;
}
return { options: options, cleared: cleared };
}
Example
var ITEMS = {
Hardware: ['Laptop', 'Monitor', 'Dock'],
Software: ['License', 'Seat'],
Service: ['Setup', 'Training']
};
function onEdit(e) {
var r = e.range;
if (r.getSheet().getName() !== 'Requests' || r.getColumn() !== 2 || r.getRow() < 2) return;
if (r.getNumRows() > 1 || r.getNumColumns() > 1) return;
setDependentDropdown(r, ITEMS);
}
Tips:
- A simple
onEditis enough here: data validation and clearing a cell don't need authorization. - Keep the lookup on a Lists tab if non-developers maintain it, and read it into an object at the top of
onEdit. - Clearing the dependent cell is on purpose: a stale pick that no longer matches the category is worse than an empty cell.
onEditdoesn't fire for script writes, so if another script sets column B, callsetDependentDropdownfrom that script too.- Static single-level lists? setDropdownFromList is simpler.
Tip: NitroGAS Co-Pilot can turn your Lists tab into the lookup map and wire up the onEdit. Paste the Lists tab headers and the sheet name.
Happy Coding!
