1

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

4
  • Hi. two things 1) Volunteers don't have permission to access the data on sheet="GS names" (sourced by a link to another sheet). It might be better if you just copy/paste this for this question. 2) Would you please explain your scenario in your question. Please don't expect that volunteers must read the linked question and answer as well as open and review your spreadsheet to understand your question.
    – Tedinoz
    Aug 10, 2021 at 10:13
  • @Tedinoz 1) Good advice - I have fixed this in my sheet. 2) Apologies, I thought I had explained it clearly in my question.
    – user246475
    Aug 12, 2021 at 2:30
  • Hey, Kate. I only just noticed from the items in "GS names" that you're from the land down under AND you're in education. Both are near and dear to me; let me know if you ever need any help.
    – Tedinoz
    Aug 14, 2021 at 3:15
  • Hi Tedinoz, thanks so much, I would love some help with building my Google Sheets (workbook), hence why I am posting my questions online. I have more questions to come. I help with kids learning from home including those with disabilities during COVID. We make videos for them from content already filmed and I am trying to make a Google Sheet to help the editors keep the new videos organised. I am doing this in my own time but I am not a coder so makes it really hard. I have already posted a reply to the current issue I am having with the code in the other part of this question.
    – user246475
    Aug 15, 2021 at 1:44

2 Answers 2

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.

10
  • ‘if (e.range.rowStart > 1’. Shouldn’t this be ‘>2’, since row#2 is part of the header.
    – Tedinoz
    Aug 12, 2021 at 3:49
  • @tedinoz made correction Aug 12, 2021 at 6:32
  • 1
    This is a much better answer than the one by Tedinoz. Among other things: 1) uses Event Objects, 2) tests for activity on the correct sheet before any code execution; 3) gets the last row for the relevant "options" column const lRow = valSh.getSheetValues(2, cIndex, -1, 1).flat().filter(d => d).length - 1 so that the end-of-column warning is not included as a dropdown option.
    – Tedinoz
    Aug 13, 2021 at 0:53
  • Thank you very much for this solution. I copied the sheet, put in the revised code and tested what would happen when 2 new Creative Class names were added in sheet GS names. See docs.google.com/spreadsheets/d/… The dropdown options for Creative Classes now offered in Column B fall 2 rows short of the full range. Is this because the range needs to reach to the very last row - so needs to include the row called "zz. Insert blank rows above this row - don't use the Add button at the bottom of the sheet?"
    – user246475
    Aug 13, 2021 at 5:06
  • 1
    @Kate As discussed, there's a basic rule of one question, one answer. These new problems, important as they are, need to be addressed elsewhere. May I respectfully suggest that you delete the last few comments that deal with new issues (one that don't directly relate to the scope of the question). May I also suggest that you "accept" Jacob's answer.
    – Tedinoz
    Aug 19, 2021 at 1:15
0

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);
  
        }
  
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.