Dependent Dropdown Lists in Google Sheets. V2021

Max Makhrov
5 min readAug 27, 2021

New!

The next version is now available:

https://max-makhrov.medium.com/dependent-drop-down-lists-for-googe-sheets-v2022-8dee6ae8e1f0

Try

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

https://docs.google.com/spreadsheets/d/1PF7zj_5laBbamPSVYy5oGTyMxaQsdcJh4pcs9e1wiiI/copy

What it does in short:

Features

⚡ 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

Usage

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.

— —

Installation

Please see the code here:
https://github.com/Max-Makhrov/GoogleSheets/blob/master/other/dvl2021.gs
OR here:
https://script.google.com/home/projects/1YP0-CNbQJMiC91RS7JNX485q9B6k0QSJCnv54O2jj4QkD1LqCdVgibL0/edit

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

// 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

// 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

// 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

// 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

// 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.

Notes

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:

[
{},
{},
{},
...
]

Plans

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?

--

--