CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
J M
J M

Reputation: 21

Using Dropdown lists on all sheets in a workbook

I have a dropdown list that is in my workbook in the first tab (Tab titled: Reference Don't Edit) and I have a schedule template (aptly titled "Schedule Template").

I am trying to make the dropdown lists on all of the subsequent tabs (which would be daily tabs) instead of creating a new workbook everyday. I tried changing the dropSS and dropSS_ from using a sheet name to SpreadsheetApp.getActiveSpreadsheet().getSheets() and .getSheet(), etc for use on all active sheets, but it just erases the working dropdown and doesn't put it on the other sheets.

function createPrimaryDrpdwon() {

  var dataSS           = "References *Don't Edit*";
  var dropSS =  "Schedule Template";
  var primaryDataRange = "A1:A46";      
  var primaryDropRange = "H4:H";      
  var primaryDropList  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(primaryDataRange).getValues();
  var primaryDropRange = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(primaryDropRange);
  var validationRule   = SpreadsheetApp.newDataValidation().requireValueInList(primaryDropList).build();
  
  primaryDropRange.setDataValidation(validationRule);
}

function createSecondaryDrpdwon(e){

  var dataSS       = "References *Don't Edit*";        
  var dropSS = "Schedule Template";
  var dropSS_      = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS);
  var allDataRange = "A1:B46";       
  var primaryDDCol = 8;              
  
  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;
}

Upvotes: 0

Views: 53

Answers (1)

Cooper
Cooper

Reputation: 64100

Try this:

function createPrimaryDrpdwon() {
  var dataSS = "References *Don't Edit*";
  var dropSS = "Schedule Template";
  var primaryDataRange = "A1:A46";
  var primaryDropRange = "H4:H";
  const ss = SpreadsheetApp.getActive();
  var primaryDropList = ss.getSheetByName(dataSS).getRange(primaryDataRange).getValues().map(r => r[0]).filter(e => e);
  var primaryDropRange = ss.getActiveSheet().getRange(primaryDropRange);
  var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(primaryDropList).build();
  primaryDropRange.setDataValidation(validationRule);
}

function getDependentList(dropDData, primarySelected) {
  var dependenList = dropDData.map(r => {
    if (r[0] == primarySelected) {
      return r[1];
    } else {
      return '';
    }
  }).filter(e => e);
  return dependenList;
}

Upvotes: 1

Related Questions