Dependent Dropdown Lists in Google Sheets. V2021
The next version is now available:
Hello, please copy the sample file and use it immediately:
What it does in short:
Cache life = maximum = 6 hours
Read cache by unique dictionary key
Tasker: 1) find & assign tasks, 2) execute
End rules at a specific row
Use range as the last validation source
Use numbers OR letters as column set
Omit some sets and use defaults
Hidden: use source from another sheet
Auto-fill list if this is the only value
Auto-fill next levels too
Auto-delete values + validation when parent is deleted
Cross-rule autofill → add next validation
Cross-rule clear values → clear next value
👾 Data types
Works with dates
Works with formatted numbers
🍹 Refresh Library
Refresh library with custom menu
Auto-refresh library when Dictionary is edited
Auto-refresh library when the file is opened
😇 Error handling
In debug mode: toast error in a short message
Use onEdit function to test code
Auto-destroy circular dependencies
Works with 2 engines: Chrome V8 + ES5
Works from a mobile device
Select value in the first “main” list as usual. Set it manually from the menu
Data > Data Validation
Next drop-down lists are dependent.
Means level II depends on a level I, level III depends on level II, etc.
Use as many dependent levels as you need.
You may enter data individually cell-by-cell OR you may enter multiple lines at once.
Delete values to clear the next levels.
Please see the code here:
Install into an existing file. Copy-paste the code into the Script Editor:
Tools > Script Editor
Prepare your data source:
Adjust settings to your needs:
One set is one object, like this:
// first_row: 2,
// first_column: 'A'
One set creates one dependent rule.
You may have as many rules as you want.
// 1️⃣ 4-level lists, starting from column A, row 2
// on sheet Dropdowns, data on 'Dictionary'!A2:E
↑ creates dependent lists on sheet Dropdowns.
Lists start from cell A2 and go right to E2.
// 2️⃣ 3-level lists, starting from row 2, columns M, N,... R
// on sheet Dropdowns, data on 'Countries'!A2:C
columns: [13, 14, 18] // ['M', 'N', 'R']
Use columns 13, 14, and 18 for rules. This skips some columns.
“display_values: true” — values are set to true means you may use dates and currency formats as values:
// 3️⃣ last row of validation is 26
“last_row” is set to 26, so your script will stop working on row 27.
// 4️⃣ Use last item as range reference
dict: "'Ranges Dictionary'!A2:C",
“last_is_range: true” — the last value is a range reference for the next lists:
// 5️⃣ One list depends on another
columns: ['N', 'U'],
columns: ['M', 'W']
Two more rules depend on rule #2:
#🎁 Use another file
Caution: it will only work if you:
- rename the function
onEditMyas a trigger
↑ The rule uses the file id of another file.
The other file is used as a data source for the main file.
The engine updates memory for rules in 3 cases:
- File refresh
- Edit on a reference sheet
- From the custom menu: “♟️ dvl”
Settings are in form of an array of objects:
The engine may be used for other cases of row-by-row user interaction:
- insert timestamps
- auto-fill values
The settings are placed inside a script, but it is possible to use settings from a sheet.
What would you suggest?