Dependent drop-down with multiple range in same sheet

20 views
Skip to first unread message

Una Qunn

unread,
Aug 10, 2022, 6:55:16 AM8/10/22
to Google Apps Script Community

I'm gonna make 10 sheets with identical format, so I need the dropdown to be able to duplicate on other sheets. 

In one sheet, there'll be 3 identical tables. In first table, I need to make primary dropdown in (F3:F17,K3:K17,P3:P17,U3:U17,Z3:Z17,AE3:AE17,AJ3:AJ17,AO3:AO17,AT3:AT17,AY3:AY17,BD3:BD17), and the dependent on the right side of the primary dropdown. For example, the dependent of F3:F17 is in G3:G17. 

And because there's 3 tables, the dropdown start again after 11 rows, for example after F3:F17, the next dropdown in the same column is in F28:F42 and 10 rows for third table, F52:F66.


I use this before, but it's only available for 1 range

function createPrimaryDrpdwon() { 

  /* SET FOLLOWING VARIABLES */ 

 var dataSS = "Daftar Nama Surat"; //Name of the sheet that contain data for dropdown lists 

 var dropSS = SpreadsheetApp.getActiveSheet().getName(); //Name of the sheet which dropdown list to be created 

  var primaryDataRange = "A3:A66"; //Data range for primary dropdown var primary

DropRange = "F3:F17"; //Range which primary dropdown set 

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


  //CREATE SECONDARY DROPDOWN LIST function onEdit(){ 

/* SET FOLLOWING VARIABLES */ 

var dataSS = "Daftar Nama Surat"; //Name of the sheet that contain data for dropdown lists 

var dropSS = SpreadsheetApp.getActiveSheet().getName(); //Name of the sheet which dropdown list to be created 

var allDataRange = "A3:B66"; //Data range for dropdown list (both primary and dependent) 

var primaryDDCol = 6; //Column number of the primary drop down 

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

Reply all
Reply to author
Forward
0 new messages