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: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. The dropdown in Col C is based off the input in Col B and comes from this data set.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)
Dependent dropdown
javascript;google apps script