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