Dependent Dropdown Lists in Google Sheets. V2021

Max Makhrov
5 min readAug 27, 2021



The next version is now available:


Hello, please copy the sample file and use it immediately:

What it does in short:


⚡ Speed
Cache life = maximum = 6 hours
Read cache by unique dictionary key
Tasker: 1) find & assign tasks, 2) execute

⚙️ Settings
Skip columns
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

👪 Dependencies
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 onOpen
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

🧲 Compatibility
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

Enter data:

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.

sample lists have 5 levels

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:
OR here:

Install into an existing file. Copy-paste the code into the Script Editor:

Tools > Script Editor

Prepare your data source:

the sample data source for 5 levels

Adjust settings to your needs:

One set is one object, like this:

dict: "'Dictionary'!A2:E",
sheet: "Dropdowns",
// first_row: 2,
// first_column: 'A'

One set creates one dependent rule.

You may have as many rules as you want.

Rule samples:


// 1️⃣ 4-level lists, starting from column A, row 2
// on sheet Dropdowns, data on 'Dictionary'!A2:E
dict: "'Dictionary'!A2:E",
sheet: "Dropdowns",

↑ 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
dict: "'Countries'!A2:C",
sheet: "Dropdowns",
first_row: 2,
display_values: true,
columns: [13, 14, 18] // ['M', 'N', 'R']

Use columns 13, 14, and 18 for rules. This skips some columns.

display_values: true

“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
dict: "'Dictionary'!A2:E",
first_row: 6,
last_row: 26,
sheet: 'Dropdowns+'

“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",
sheet: 'Dropdowns++',
first_row: 6,
last_is_range: true

“last_is_range: true” — the last value is a range reference for the next lists:


// 5️⃣ One list depends on another
dict: "'Cross'!A2:B",
sheet: 'Dropdowns',
columns: ['N', 'U'],
last_is_range: true
dict: "'Cross2'!A2:B",
sheet: 'Dropdowns',
columns: ['M', 'W']

Two more rules depend on rule #2:

#🎁 Use another file

Bonus rule.

Caution: it will only work if you:

  1. rename the function onEditto onEditMy (or somehow).
  2. install onEditMy as a trigger
dict: "'Sheet1'!A2:D",
dict_file: '1ZDtY26f5VIIfkfo1CqCkKWxY2Su1D2DH0yOtuNKIFb4',
sheet: "WORK",
display_values: true,

↑ The rule uses the file id of another file.

dict_file: '1ZDtY26f5VIIfkfo1CqCkKWxY2Su1D2DH0yOtuNKIFb4'

The other file is used as a data source for the main file.


The engine updates memory for rules in 3 cases:

  1. File refresh
  2. Edit on a reference sheet
  3. 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?



Max Makhrov

Google Sheets Developer, master of Online Accounting