-1

I have some basic code that creates a dependent dropdown menu for a specified sheet. That works perfectly fine, but I create a new tab for every month of the year. Is it possible to have the code apply to all sheets? Currently I have to go in and change var dataSS and var dropSS to the tab name.


 
var dataSS           = "April";         
var dropSS           = "April";      
var primaryDataRange = "K3:K13";       
var primaryDropRange = "D2:D200";       

var primaryDropList  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(primaryDataRange).getValues();
var primaryDropRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS).getRange(primaryDropRange);
var validationRule   = SpreadsheetApp.newDataValidation().requireValueInList(primaryDropList).build();
 
primaryDropRange.setDataValidation(validationRule);
}



function onEdit(){

var dataSS       = "April";         
var dropSS       = "April";      
var allDataRange = "K3:L13";       
var primaryDDCol = 4;               
 
var dropSS_      = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS);
var dropDData    = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(allDataRange).getValues();
var activeCell   = dropSS_.getActiveCell();
var activeColumn = activeCell.getColumn();
var activeRow    = activeCell.getRow();
 
if(activeColumn==primaryDDCol){
  var dep_Col         = primaryDDCol+1;
  var dep_Row         = activeRow;
  var depCell         = dropSS_.getRange(dep_Row, dep_Col);
  var primarySelected = activeCell.getValue();
  var validationRule  = SpreadsheetApp.newDataValidation().requireValueInList(getDependentList(dropDData,primarySelected)).build();
   
  depCell.setDataValidation(validationRule);
}
}

function getDependentList(dropDData,primarySelected){
var dependenList = [];
var j = 0;
if(dropDData != null){
  for(i=0; i<dropDData.length; i++){
    if(dropDData[i][0]==primarySelected){
      dependenList[j] = dropDData[i][1];
      j++;
    }
  }
}
return dependenList;
}
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy