1
\$\begingroup\$

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)

\$\endgroup\$

migrated from stackoverflow.com Sep 12 '16 at 22:34

This question came from our site for professional and enthusiast programmers.

  • \$\begingroup\$ 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. :) \$\endgroup\$ – mongoose36 Aug 10 '16 at 1:44
3
\$\begingroup\$

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);
  }
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy