Dependent dropdown

Question

I've created this code (used the validation creation section from here) which creates dependent dropdown lists based on data which is in a pivot table style arrangement.

The idea is that picking a client gives the options of what projects relate to that client, and picking the project gives options of milestones based on the project - a cascading dependency I suppose if I can call it that.

Here is the final input sheet where the drop downs are used as an example:

Input sheet

These drop downs in Col A and B are based on the following data sets - the drop downs in Col A is a simple list validation, and B feeds off whatever is in Col A.

Clients > Projects

The dropdown in Col C is based off the input in Col B and comes from this data set.

Projects > Milestones

The following is the code that does the work. Due to my level it's pretty heavy so takes longer than I would like to load as each cell is edited. As you can see, because there two validation lists being created from separate sources depending on which column cell you're editing, I ended up creating two versions of the code, each section being activated depending on which column you edit. This was instead of running constant checks in the code itself to check the column being edited. I have been told that duplicated code is a waste, but am struggling to think of a cleaner solution. Is this the correct/most optimised approach?

You can see the sheet here.

function onEdit(e) {
 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = SpreadsheetApp.getActiveSheet();
 var myRange = SpreadsheetApp.getActiveRange();
 var projectSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Projects");
 var milestoneSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Milestones");
 var option = new Array();
 var stopFlag = true;
 var startRow = null;
 var endRow = null;
 var numRows = null;
 var activeCell = SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getDisplayValue();
 var col = null;
 var fLen = null;
 // ckeck which is the active column
 if( e !== undefined) {
 col = e.range.getColumn();
 } 
 
 //-------------Create data validation in Col B depending on client chosen in A-----------------------
 if (col == 1) {
 
 //craete startRow, endRow and numRows vars
 var range = projectSheet.getSheetValues(1,1,100,1);//(startRow, startColumn, numRows, numColumns)
 var fLen = range.length;
 
 //outputs first row where value appears
 for (i = 0; i < fLen; i++) {
 if (range[i]==activeCell && stopFlag) {
 startRow = i;
 stopFlag = false;
 }
 }
 
 //outputs last row where value appears
 for (i = 0; i < fLen; i++) {
 if (range[i]==activeCell) {
 endRow = i;
 }
 }
 
 //the number of rows in range for client
 numRows = endRow-startRow;
 
 //Creates new validation based on client chosen
 option = projectSheet.getSheetValues(startRow+1,2,numRows+1,1); //startrow, startcol, numrows, numcols
 var dv = SpreadsheetApp.newDataValidation();
 dv.setAllowInvalid(false); 
 //dv.setHelpText("Some help text here");
 dv.requireValueInList(option, true);
 sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).setDataValidation(dv.build()); 
 }//end of client dependent dropdown
 //-------------Create data validation in Col C depending on project chosen in B-----------------------
 if (col == 2) {
 
 //craete startRow, endRow and numRows vars
 var range = milestoneSheet.getSheetValues(1,1,100,1);//(startRow, startColumn, numRows, numColumns)
 var fLen = range.length;
 //outputs first row where value appears
 for (i = 0; i < fLen; i++) {
 if (range[i]==activeCell && stopFlag) {
 startRow = i;
 stopFlag = false; 
 }
 }
 
 //outputs last row where value appears
 for (i = 0; i < fLen; i++) {
 if (range[i]==activeCell) {
 endRow = i;
 }
 }
 
 //the number of rows in range for client
 numRows = endRow-startRow;
 
 //Creates new validation based on client chosen
 option = milestoneSheet.getSheetValues(startRow+1,2,numRows+1,1); //startrow, startcol, numrows, numcols 
 var dv = SpreadsheetApp.newDataValidation();
 dv.setAllowInvalid(false); 
 //dv.setHelpText("Some help text here");
 dv.requireValueInList(option, true);
 sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).setDataValidation(dv.build()); 
 
 }//end of project dependent dropdown
 
 
}// end function onEdit(e)

• mongoose36: I haven't dug into the code to create the validation, but right off the bad you can simplify your declaring and instantiation of variables at the top by utilizing the on edit object that is passed to the code from the trigger -- namely 'e'. You can do this with the following...var ss = e.source; var s = ss.getActiveSheet(); etc... Notice how the spreadsheet object, ss, is utilized in the next variable...I doubt this increases speed much if at all but it makes for cleaner code. :)

Answer 1

I used your principle but different approach. Check with your data.

function onEdit(e) 
{
 var ss = e.source.getActiveSheet();// select active sheet
 var projectSheet = e.source.getSheetByName("Projects")// select project sheet
 var projectData = projectSheet.getRange(2,1,projectSheet.getLastRow(),projectSheet.getLastColumn()).getValues(); // get project data as 2D array
 var milestoneSheet = e.source.getSheetByName("Milestones");// select milestone sheet
 var milestoneData = milestoneSheet.getRange(2,1,milestoneSheet.getLastRow(),milestoneSheet.getLastColumn()).getValues();// get milestone data as 2D array
 var newValue = e.value;// changes value
 var range = e.range; //select changed range
 var cellcol = range.getColumn(); //cell's column
 var cellrow = range.getRow();//cell's row
 var validations =[]; //array initiate
 if(cellcol == 1)//changed project
 {
 //craete validation data array from 2D array
 for(var i = 0; i < projectData.length; i++)
 {
 if(newValue == projectData[i][0])//if project name equal to project name in projectdata
 {
 validations = validations.concat(projectData[i][1]);
 }
 }
 var cell = ss.getRange(cellrow,cellcol +1);//select cell next to project selected cell
 var rule = SpreadsheetApp.newDataValidation().requireValueInList(validations, true).build();//build data validation
 cell.setDataValidation(rule);//set data validation
 }
 if(cellcol == 2)
 {
 for(var i = 0; i < milestoneData.length; i++)
 {
 if(newValue == milestoneData[i][0])
 {
 validations = validations.concat(milestoneData[i][1]);
 }
 }
 var cell = ss.getRange(cellrow,cellcol +1);
 var rule = SpreadsheetApp.newDataValidation().requireValueInList(validations, true).build();
 cell.setDataValidation(rule);
 }
}