0

I have an apps script code to set dependence validations for my google sheet form. It worked but every day, there are so many users work at the same time in my sheet (over 9,000 rows of data), so sometimes the sheet is overload. I don't know the issue came from my code or other reasons. How can I optimize it to be faster? Thank you!

function onEdittest(){
  var tabLists = "Filter";
  var tabValidation = "Tracker B7";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);
  var activeCell = ss.getActiveCell();
  
  if(activeCell.getColumn() == 14 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){
    
    activeCell.offset(0, 1).clearContent().clearDataValidations();
    
    var make = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    
    var makeIndex = make[0].indexOf(activeCell.getValue()) + 1;
    
    if(makeIndex != 0){
    
        var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
  
     }  
      
  }
  if(activeCell.getColumn() == 15 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){
    
    activeCell.offset(0, 5).clearContent().clearDataValidations();

    var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();

    var makesIndex = makes[0].indexOf(activeCell.getValue()) + 1;

    if(makesIndex != 0){
      var validationRanges = datass.getRange(3, makesIndex, datass.getLastRow());
      var validationRules = SpreadsheetApp.newDataValidation().requireValueInRange(validationRanges).build();
      activeCell.offset(0, 5).setDataValidation(validationRules);
    }

  }
  if(activeCell.getColumn() == 20 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation && activeCell.getValue() == '4. Not cooperating'){
    
    activeCell.offset(0, 1).clearContent().clearDataValidations();

    var makess = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    
    var makessIndex = makess[0].indexOf(activeCell.getValue()) + 1;

    if(makessIndex != 0){
      var validationRangess = datass.getRange(3, makessIndex, datass.getLastRow());
      var validationRuless = SpreadsheetApp.newDataValidation().requireValueInRange(validationRangess).build();
      activeCell.offset(0, 1).setDataValidation(validationRuless);
    }

  }
  if(activeCell.getColumn() == 20 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation && activeCell.getValue() == '2. Called succesfully'){

    activeCell.offset(0, 2).clearContent().clearDataValidations();
    activeCell.offset(0, 3).clearContent().clearDataValidations();
    
    activeCell.offset(0, 2).setValue('Yes');

    var validationRangesss = datass.getRange(3, 31, datass.getLastRow());
    var validationRulesss = SpreadsheetApp.newDataValidation().requireValueInRange(validationRangesss).build();

    activeCell.offset(0, 3).setDataValidation(validationRulesss);
  }
  if(activeCell.getColumn() == 20 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation && activeCell.getValue() == '6. Skip'){

    activeCell.offset(0, 1).clearContent().clearDataValidations();
            
    var validationRangessss = datass.getRange(3, 32, datass.getLastRow());
    var validationRulessss = SpreadsheetApp.newDataValidation().requireValueInRange(validationRangessss).build();

    activeCell.offset(0, 1).setDataValidation(validationRulessss);
  }
  if(activeCell.getColumn() == 14 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation && activeCell.getValue() == ''){
    activeCell.offset(0, 1).clearContent().clearDataValidations();
    activeCell.offset(0, 6).clearContent().clearDataValidations();
    activeCell.offset(0, 7).clearContent().clearDataValidations();
    activeCell.offset(0, 8).clearContent().clearDataValidations();
    activeCell.offset(0, 9).clearContent().clearDataValidations();
  }

}
  • 1
    If it's a performance problem, then I don't think StackOverflow is the right site for it. The best I can tell is to try out a profiler tool, and search which parts of the code peaks in performance. – Steven Jan 26 '21 at 08:41
  • Read Best practices. See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for more details. And see linked duplicates. You need to batch operations – TheMaster Jan 26 '21 at 11:52

0 Answers0