Dependent Dropdown Lists in Google Sheets. V2021

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?