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