Dependent Drop-Down Lists For Googe Sheets v2022

Updated the script for dependent lists, and now it supports new options. New for 2022:

• Disable the auto-cleanup feature
• Disable auto-complete feature
• Delete following dependent values if the main is ‘bad’

Sample Settings:

// Advanced
    {
      sheet: 'Advanced',
      dict: "'Ducks_Data'!A2:C",
      first_row: 3,
      first_column: 'A',
      stop_autocleanup: true
    },
    {
      sheet: 'Advanced',
      dict: "'Ducks_Data'!A2:C",
      first_row: 3,
      first_column: 'E',
      stop_autocomplete: true
    },
    {
      sheet: 'Advanced',
      dict: "'Ducks_Data'!A2:C",
      first_row: 3,
      first_column: 'I',
      clear_notfound: true
    },
    {
      sheet: 'Advanced',
      dict: "'Ducks_Data'!A2:C",
      first_row: 3,
      first_column: 'M'
    }

Source

1. code: https://script.google.com/home/projects/1wsKkvdvnTkjMesolqwxc9nIow3QSbSn54ko8X0V8MustLgP6CKl-Lx25/edit
2. sheet: https://docs.google.com/spreadsheets/d/1FVg-IBLTWhZJzRSivogCLexP2z2oorH-pg1LITjtduE/edit#gid=1278075736
3. Github: https://github.com/Max-Makhrov/GoogleSheets/blob/master/other/dvl2022.gs
4. Original story: https://max-makhrov.medium.com/dependent-dropdown-lists-in-google-sheets-v2021-eab0bd1d7670
5. My page with projects: https://www.cooltables.online

Tell me if you find a better solution for dependent (cascade) drop-down lists 😉

Notes for search engine

Dependent drop-down lists can be used to create a dynamic list of choices in Google Sheets. When the user makes a selection from the first drop-down list, the choices in the second drop-down list will be updated based on the selection. This can be used to create a cascading list of choices.

Classical example: create drop-down lists of countries, states, and cities. You are not limited to the number of nested lists. You may also create 2 dependent rules when the result of drop-down 2 will affect new lists of 2 and more dependent columns.

Dependent lists will be created fast. For 50K rows of data, the speed is less than a second. It can be this fast due to the use of Cache. Data can be read quickly with the Google Cache service.

Flexible settings will allow you to skip columns, end the rule on a specific row, and use range as the last validation source.

You may also use sources from other Spreadsheets. This will allow you to hide the database from the end-users, still allowing them to use the data.