I have 2 dependent drop down lists that I'm trying to use in the same spreadsheet tab.

The first drop down I would like to only trigger my onEdit function when it is used in columns 14,21,28,etc.. (Starting on column 14, and every 7 columns after that).

The second drop down, I would like it to apply only to columns 12,19,26,etc... (Starting at column 12, and every 7 columns after that).

Here's a copy of my sheet: https://docs.google.com/spreadsheets/d/181GQe23V1L12ctCHcIdMXK7p3aP2MY51up8vGaGNRuY/edit?usp=sharing

The following code has allowed me to use one set of drop downs, but not the other:

function onEdit() {
  
  var tabLists = "Lists";
  var spreadsheet = SpreadsheetApp;
  var activeSheet = spreadsheet.getActiveSpreadsheet().getActiveSheet();
  var data = spreadsheet.getActiveSpreadsheet().getSheetByName(tabLists);
  var activeCell = activeSheet.getActiveCell();
  var row = activeCell.getRow();
  var col = activeCell.getColumn();

  if (col%7 == 0 && col >= 14  && row > 11 && activeSheet.getSheetName().includes("Programming")) {
    var obj = { 
      "Top Set / Back Off": [1, 2, 3],//These numbers apply to the columns 
      "Top Set / Percentage": [4, 5, 6, 7], 
      "Straight Sets": [8, 9, 10], 
      "Fatigue Sets": [11, 12, 13],
      "Ramp Up / Repeat" :[14, 15],
      "Accessory": [16, 17, 18] 
      };
    var v = activeCell.getValue();
    activeCell.offset(0, 1, 1, 4).clearContent().clearDataValidations();//clears 4 cells to the right of the active cell
    if (!obj[v]) return;
    var lastRow = data.getLastRow();
    var rules = obj[v].map(r => {
      var r = data.getRange(3, r, lastRow - 2, 1);
      return SpreadsheetApp.newDataValidation().requireValueInRange(r).build();
    });
    activeCell.offset(0, 1, 1, rules.length).clearContent().setDataValidations([rules]);

  } // This applies the dropdowns to columns 14,21,28,etc...
  
  if(col >= 12 && row > 11 && activeSheet.getSheetName().includes("Programming")){
    activeCell.offset(0, 1).clearDataValidations(); 
    
    var exerciseCat = data.getRange(1, 1, 1, data.getLastColumn()).getValues();
    var catIndex = exerciseCat[0].indexOf(activeCell.getValue())   1;
    
    if(catIndex != 0){
        var validationRange = data.getRange(2, catIndex, data.getLastRow());
        var validationRule = spreadsheet.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
    
    }  

  } //this applies the dropdowns to columns 12,19,26,etc...

}

As always, thank you in advance.

CodePudding user response：

Triggers comes with an Event Object that contains information about the context that caused the trigger to fire.

For onEdit, you can use the properties of the event object to determine the range, column, row, value and sheet name of the edited cell.

Try this code:

function onEdit(e){
  let range = e.range;
  let col = range.getColumn();
  let row = range.getRow();
  let sheet = range.getSheet();

  if(col%7 == 0 && col >= 14 && row > 11 && sheet.getName() == "Programming"){

    //Insert code for 14,21,28...

  }

  if(col >= 12 && (col-12) % 7 == 0 && row > 11 && sheet.getName() == "Programming"){
    
    //Insert code for 12,19,26...

  }
}

Note: I tested this by making copy of your sheet, add print function to the if else blocks above and edited columns 14,21,28 etc and 12,19,26 etc.