2D Dependent DropDown lists for Google Sheets

I’ve tried to create a classy sample script for 2D dependent dropdowns:

1. Superfast
2. Easy to install
3. Easy to maintain the code

The solution works better if your data has few categories and many values by each category.

Here’s the code:

Notes on installation

1. Copy sample file or copy/paste the code to your file
2. Go to the code: Tools > Script Editor
3. Change settings in the code as follows:

Notes about settings:

1. The settings object is array []. Add more dependent rules to your spreadsheet if needed. You may also combine rules and create 3D, 4D rules, intersecting in any order.
2. The single set is javascript object {}. It contains lib: the list of ranges for dependent dropdowns. I’ve wanted to make the code simple and fast, that’s why I’ve hardcoded these values. You may think of it as a point for improvements: read settings from sheet, or use CacheService for faster execution.
3. The rest of the sets defines sheet, row, col1, col2.
4. sheet — the name of sheet where the rule works
5. row — the first row of data
6. col1 — the column with categories
7. col2 — the column to put a dependent dropdown

Set the first level

Set the first level of drop-down lists without code, use menu:

Data > Data Validation

The code was designed as a starting point. It may be used as is in some cases. Modifications and suggestions of the code are welcome.

Dependent dropdown lists. Drawbacks

This topic was discussed so many times, and still, it bothers me. A lot of developers came up with solutions and shared their code, video, articles on the Web. The result: now we have a ton of solutions, and each of them has drawbacks:

1. 🐌 Code works slow. The time on creating a new list may slow down if the code relies on formulas, or the code needs to read some data from a sheet. The current script will work faster as it only uses values hardcoded into the script.
2. 🤯 Lists are limited to 500 items. Google Sheets has a limit of 500 items per list, created by script array []. This code uses a range validation rule and has no limit on the number of items.
3. 🤕 A value in the list may change. Use open ranges like ‘lists’!A2:A. It will create an open range of values in a dependent list. If you add new values to your lists sheet, dependent lists will have new values too.

User interactions

There are many possible ways in which users can break the rules in sheets. Sheets are priorly a democratic place where users have maximum freedom of action. In my opinion, this is the reason #1 why Spreadsheets is still so popular tool.

Code will show a floating message if a user enters an invalid value

To improve dependent lists, a developer may consider these scenarios:

1. ✏️✏️✏️ edit several cells at once. Expects script to create dependent lists for all edited rows.
2. ❌ delete values. Expects deletion of rules of dependent lists.
3. 🧐 use the only value in the dependent list. Expects auto-insert next value if it is the only possible value. And if there are further levels, repeat the action.
4. ➕ add new categories: level #1 of lists. Expects the script to see it and adjust settings.
5. 🛢️ data from external sources. The data will look like a solid table, not a wide table like in my sample.
6. 🔛change the column order in the main sheet. Expects the script to see it and adjust settings.
7. 🔝 add rows into the header. Expects the script to see it and adjust settings.
8. 🚦 make lists to work until a certain row, and then the rule must stop.
9. 🐇🐇🐇 make more than 2 levels of dependent lists. Expects the usage of a single data table for lists.
10. 💩 enter invalid values. Some of them may be a valid entry, some not. Expects script to create dependent lists for valid values, and show warning for invalid values.

Some of these `TODOs` are easy to implement, some require a ton of coding. My dream is to create a perfect solution for dependent dropdown lists. The bad news is it would be certainly not easy to maintain the code. The current code has less than 70 rows. Easy to understand and modify. My perfect code will be much longer. See you when I’ll finish it.

Happy coding!