-2

I am wondering if there is a more efficient way to speeding up this script?

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trends");
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Options");
var options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1, 2).getValues();

function onEdit(e){
 var activeCell = e.range;
 var val = activeCell.getValue();
 var r = activeCell.getRow();
 var c = activeCell.getColumn();
 var wsName = activeCell.getSheet().getName();
 if(wsName == "Trends" && c === 5 && r > 2){

   if(val === ""){
     ws.getRange(r, 6).clearContent();
     ws.getRange(r, 6).clearDataValidations();
   } else {
     ws.getRange(r, 6).clearContent();
     var filteredOptions= options.filter(function(o){ return o[0] === val });
     var listToApply = filteredOptions.map(function(o){ return o[1] });
     var cell = ws.getRange(r, 6);
     applyValidationToCell(listToApply,cell);
   }   
  }
 }

function applyValidationToCell(list,cell){

 var rule = SpreadsheetApp
 .newDataValidation()
 .requireValueInList(list)
 .setAllowInvalid(false)
 .build();

 cell.setDataValidation(rule);
}
  • 2
    Code optimization questions belong on Code Review. And it helps if you describe what this code is supposed to do. – user55949 Nov 3 at 21:13
2

Try this

Code

function onEdit(e) {
  if(e.range.columnStart === 5 && e.range.rowStart > 2) {
    if(e.source.getActiveSheet().getName() === "Trends") {
      var oSet = e.range.offset(0, 6);      
      if(!e.value || typeof e.value === 'object') { 
        oSet.clear({validationsOnly: true});
      } else {
        oSet.clearContent();
        oSet.setDataValidation(
          SpreadsheetApp
            .newDataValidation()
            .requireValueInList(
              e.source.getSheetByName("Options").getSheetValues(2, 1, -1, 2).filter( function(o) { 
                return o[0] === e.value;
              }).map( function (o) { 
                return o[1];
              })
            )
            .setAllowInvalid(false)
          .build()
         );
      }
    }
  }
}

Explained

The first three lines of your code are always executed. This makes an onEdit trigger slugish from the start. Secondly, I've restricted the range first so that it will not check the sheet name constantly. Thirdly, I've made use of the return parameters of the onEdit trigger. This reduces the API calls even more.

  • Hey Jacob, thank you for the response! I actually followed a guide for this because I am new to JS. I went ahead and tried what you have written above however it is not functioning for me. Thoughts? – Jon Nov 4 at 19:06
  • I'd like to add Jacob that this is for a dependent drop down list. – Jon Nov 4 at 20:59
  • @Jon please share a (dummy) sheet, so we can have a look. – Jacob Jan Tuinstra Nov 5 at 7:58
  • @Jon I over did it with the filter method, it needs the map to get the results you want. The above script works at my end. – Jacob Jan Tuinstra Nov 5 at 8:15
  • Hey Jacob, my apologies, it's been a busy week. I managed to get yours to work with my spreadsheet, and it is much quicker so thank you! The offset and sheetValues had to be tweaked. The only issue I'm having now is that if I backspace the first dropdown, I'd like it to clear the data validation in the second dropdown. It clears the options but keeps the dropdown menu in the spot. It's not a big deal and I could probably figure it out, but if you have any last input that would be greatly appreciated. – Jon Nov 6 at 8:54

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.