Infinite Dependent Drop Down Lists in в Google Sheets

The article considers the option of creating dependent drop-down lists on the Google Spreadsheet sheet, practically unlimited neither in the number of linked elements, nor in the number of rows on the sheet.

The program script is shown below:

function onEdit(e) {

  let col = e.range.getColumn();
  let list_name = e.source.getActiveSheet().getName();
  let name = e.value;
  
  if(list_name=="Home") {
    fillColumn(col, name);
  }
}

function fillColumn(col, name) {

// find date
  var sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var iMax = sd.getLastRow();
  var col_data = [];
  
  for(var i=2; i<=iMax; i++) {
    var x = sd.getRange(i, col).getValue();
    if(x == name) {
      col_data.push(sd.getRange(i, col+1).getValue());
    }
  }
   
// clear Data_2
  var sd_2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data_2");
  sd_2.getRange(2, col+1, sd_2.getLastRow(), sd_2.getLastColumn()).clearContent();


// insert data
  iMax = col_data.length;

  for(var i=2; i<=iMax+1; i++) {
    sd_2.getRange(i, col+1).setValue(col_data.shift());
  }
}

Please note: sheet names must EXACTLY match the names specified in the script!

Main sheet: "Home"

datasheet: "Data",

intermediate data sheet: "Data_2"

These names in the script are highlighted in red, so, if desired, it will not be difficult to find them to replace with the names of your sheets.

This article has a continuation: Infinite Dependent Dropdowns Lists in Google Sheets (Part 2)

Now, in the new version of the program, it is possible to automatically create all the sheets of the file necessary for the script to work (including data formatting and validation) by pressing just one (!) button from the user menu.

More information you can find in this video (RU voice):