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:

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)

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