Help with dynamic dependent dropdown

Question

How do I change this dynamic dependent dropdown list script so it works with my particular Google Sheet.

function onEdit(){

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dropdown database")
  
  var activeCell = ss.getActiveCell();
  
  if(activeCell.getColumn() == 2 && activeCell.getRow() > 1){
  
    activeCell.offset(0, 1).clearContent().clearDataValidations();

      var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
  
      var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;
      
        if(makeIndex != 0) {
  
      var validationRange = datass.getRange(2, makeIndex, datass.getLastRow());
      var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
      activeCell.offset(0, 1).setDataValidation(validationRule);
  
  }
  
}
}

I found this script from a solution posted here: https://support.google.com/docs/thread/61515090/help-with-dynamic-dependent-dropdown?hl=en

I put this script into my sheet but I am not sure what I need to change on it to make it work in mine. I can see I need to change the name of the sheet "Dropdown database" to "GS" to work for my sheet, but I am not sure what else I need to change. The aim is to get the dropdown options in column B to change depending on what is selected from a dropdown in column A. I need to be able to insert rows in this sheet as well and for the script to still work.

Here is a link to my sheet. https://docs.google.com/spreadsheets/d/1xbt-7JTYmkPr0Wo0DnyjacQPnTya2y4Vx48IhS1Jh6A/edit?usp=sharing

Answer 1

With the following code you should be able to do that

Code

const onEdit = (e) => {

  // // before doing anything check if we're in the correct row, column and sheet
  if (e.range.rowStart > 2 && e.range.columnStart === 1 && e.range.getSheet().getName() === 'GS') {

    // set offset (column B)
    const oSet = e.range.offset(0, 1)

    // get validation sheet
    const valSh = e.source.getSheetByName("GS names")

    // get index 
    const index = valSh.getSheetValues(1, 1, 1, 4).flat().indexOf(e.value)

    // check index
    if (index !== -1) {

      // set column index
      const cIndex = index + 1

      // set last row in validation range
      const lRow = valSh.getSheetValues(2, cIndex, -1, 1).flat().filter(d => d).length
      
      // set range
      const valData = valSh.getRange(2, cIndex, lRow)
      
      // build validation
      const valRule = SpreadsheetApp.newDataValidation().requireValueInRange(valData).build()
      
      // set validation
      oSet.setDataValidation(valRule)

      // set focus to offset range
      oSet.activateAsCurrentCell()

    } else {

      // clear data validation
      oSet.clearContent().clearDataValidations()
    }
  }
}

Left the code in your example file and I hope the comments in the code are self-explanatory enough.

Answer 2

Things to modify

1 - Define the spreadsheet

Delete: var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
Substitute": var ss = SpreadsheetApp.getActiveSpreadsheet();

2 - Identify the sheet containing the dropdown values

Delete: var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dropdown database")
Substitute: var datass = ss.getSheetByName("GS names")

3 - Identify the sheet containing the dropdowns

Insert: var gs = ss.getSheetByName("GS")

4 - Update the IF statement for the layout of this spreadsheet.

• the specific sheet name ("GS")
• column number is 1 since the first dropdown column is Column A (compared to the reference in which the first column of dropdowns are in Column 2 (Column B))
• row number > 2 since there are two header rows (compared to the reference in which there was only one header row and the dropdowns begin in row 2)

Delete: if(activeCell.getColumn() == 2 && activeCell.getRow() > 1){
Substitute: if(activeCell.getSheet().getName() == "GS" && activeCell.getColumn() == 1 && activeCell.getRow() > 2){

5 - Update "Makes" and "makeIndex" to something more relevant

There are several lines of code affected. Refer to the summary for a full list.

Summary:

function onEdit(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var datass = ss.getSheetByName("GS names")
  var gs = ss.getSheetByName("GS")
  var activeCell = ss.getActiveCell();
  

  if(activeCell.getSheet().getName() == "GS" && activeCell.getColumn() == 1 && activeCell.getRow() > 2){
  
    activeCell.offset(0, 1).clearContent().clearDataValidations();

      var options = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
  
      var optionIndex = options[0].indexOf(activeCell.getValue()) + 1;
      
        if(optionIndex != 0) {
  
          var validationRange = datass.getRange(2, optionIndex, datass.getLastRow());
          var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
          activeCell.offset(0, 1).setDataValidation(validationRule);
  
        }
  
  }
}