samedi 15 mai 2021

What is the best way to deal with multiple scenarios to increase speed in Google Apps Script?

I have a function that is supposed to unhide certain columns, but only if other filters that relate to the columns are not in use. Because there are 4 other filters that it needs to check to see if they are in use (either 'true' or 'false'), there are 16 possibilities that the function needs to run through.

I've used else if statements to accomplish this and it does work, but it is incredibly slow. I was wondering if there is a more appropriate way to deal with all the possible options that is faster.

This is the code I currently have (sorry if I've shown too much, not sure how much I need to include):

function Unfilter(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var numCols = sheet.getRange(1,3).getValue(); //gets the number of columns to loop through
  var xRow = sheet.getRange(1,5).getValue() + 16; //gets the target row to run the blank check on
  // check filter statuses 
  var nameShow = sheet.getRange(1,1).getValue(); 
  var statusShow = sheet.getRange(2,1).getValue(); 
  var dateShow = sheet.getRange(3,1).getValue();
  var evidenceShow = sheet.getRange(4,1).getValue();
  //loop through all target columns and unhide all columns that are not filtered
  for (var i=10; i<=numCols; i++) {
    if (sheet.getRange(xRow,i).getValue() == "") {
    var catType = sheet.getRange(16,i).getValue();
      if (nameShow == true && statusShow == true && dateShow == true && evidenceShow == true) {
        sheet.showColumns(i)
      } else if (nameShow == false && statusShow == true && dateShow == true && evidenceShow == true) {
        if(catType !== "Name") {
          sheet.showColumns(i);
        }
      } else if (nameShow == false && statusShow == false && dateShow == true && evidenceShow == true){
          if (catType == "Date" || catType == "Evidence") {
           sheet.showColumns(i);
          }
      } else if (nameShow == false && statusShow == true && dateShow == false && evidenceShow == true) {
          if (catType == "Status" || catType == "Evidence") {
            sheet.showColumns(i);
          }
      } else if (nameShow == false && statusShow == true && dateShow == true & evidenceShow == false){
          if (catType == "Status"|| catType == "Date") {
            sheet.showColumns(i);
         }
      } else if (nameShow == false && statusShow == false && dateShow == false && evidenceShow == true){
          if (catType == "Evidence") {
            sheet.showColumns(i);
          }
      } else if (nameShow == false && statusShow == false && dateShow == true && evidenceShow == false){
          if (catType == "Date") {
            sheet.showColumns(i);
          }
      } 
      //...etc for all 9 remaining possibilities
    }
  }
}

Aucun commentaire:

Enregistrer un commentaire