Google Apps Script run faster

Question

Below I have some code I have running for a spreadsheet. Right now it takes a min or two to run through the script. I was wondering if anyone has any suggestions on how to re-work my code to run a little faster.

What the code does is search on a tab in the sheet called "set up" for check-marked items in a list that I would like included in my "Master Sheet". Then go to my sheet which contains all of the information that I would like copied and pasted over according to what is check marked on my set-up page. Then copy and paste those line items to the master sheet.

function allToMaster(){
  var sss = SpreadsheetApp.getActive();
  var ssAll = sss.getSheetByName("FF All");
  var ssMaster = sss.getSheetByName("FF Master");
  var ssSetup = sss.getSheetByName("FF Setup");

  ssMaster.clear();
  var masterCounter = 2;
  ssAll.getRange("P:P").clear();

  var sourceRange = ssAll.getRange(1,1,1,15);
  sourceRange.copyTo(ssMaster.getRange(1,1));

  //get last row of FF All
  var lastRowAll = ssAll.getLastRow();
  var lastRowMaster = ssMaster.getLastRow();

  ssAll.getRange("P2:P" + lastRowAll).setFormula("=index('FF Setup'!B:B,match(B2,'FF Setup'!C:C,0))");
  ssMaster.setRowHeightsForced(2, 500, 26);

  for (i=2;i<=lastRowAll;i++){
    if (ssAll.getRange(i,1).getBackground() == "#a8d08d"){
      var sourceRange = ssAll.getRange(i,1,1,15);
      sourceRange.copyTo(ssMaster.getRange(masterCounter,1));
      masterCounter++;
    } else if (ssAll.getRange(i,1).getBackground() == "#e2efd9"){
      var sourceRange = ssAll.getRange(i,1,1,15);
      sourceRange.copyTo(ssMaster.getRange(masterCounter,1));
      masterCounter++;
    } else {
      if (ssAll.getRange("P" + i).getValue() == true) {
        var sourceRange = ssAll.getRange(i,1,1,15);
        sourceRange.copyTo(ssMaster.getRange(masterCounter,1));
        ssMaster.setRowHeightsForced(masterCounter, 1, 136);
        masterCounter++;
      }
    }
    
  }
  ssAll.getRange("P:P").clear();

  //Clear Empty Subtitles
  var lastRowMaster = ssMaster.getLastRow();
  for (i=2;i<=lastRowMaster;i++){
    if (ssMaster.getRange(i,1).getBackground() == "#e2efd9"){
      if(ssMaster.getRange((i+1),1).getBackground() == "#e2efd9" || ssMaster.getRange((i+1),1).getBackground() == "#a8d08d"){
        ssMaster.deleteRow(i);
        ssMaster.insertRowAfter(500);
        i=i-1;
      }
    }
  }
  //Clear Empty Titles
  var lastRowMaster = ssMaster.getLastRow();
  for (i=2;i<=lastRowMaster;i++){
    if (ssMaster.getRange(i,1).getBackground() == "#a8d08d"){
      if(ssMaster.getRange((i+1),1).getBackground() == "#a8d08d"){
        ssMaster.deleteRow(i);
        ssMaster.insertRowAfter(500);
        i=i-1;
      }
    }
  }

  //Find the row with "Delivery"
  var deliveryRow = getRowOf("DELIVERY", "FF All", 1);
  var sourceRange = ssAll.getRange(deliveryRow,1,(lastRowAll - deliveryRow + 1),15);
  var masterCounter = ssMaster.getLastRow()
  sourceRange.copyTo(ssMaster.getRange(masterCounter,1));
  masterCounter = masterCounter + lastRowAll - deliveryRow - 2;

  //.setFormula('=SUMA(J264:J275)');
 // ssMaster.getRange(masterCounter, 10).setFormula("=sum(J2:J" + (masterCounter - 1) + ")");
  //ssMaster.getRange(masterCounter, 11).setFormula("=sum(K2:K" + (masterCounter - 1) + ")");
  //ssMaster.getRange(masterCounter, 13).setFormula("=sum(M2:M" + (masterCounter - 1) + ")");
  //ssMaster.getRange(masterCounter, 15).setFormula("=M" + masterCounter + " - K" + masterCounter);

}

function getRowOf(value, sheet, col){
  var dataArr = SpreadsheetApp.getActive().getSheetByName(sheet).getRange(4, col, 3500, 1).getValues();
  for(var j = 0; j < dataArr.length; j ++){
    var currVal = dataArr[j][0];
   if(currVal == value){
     return j+4;
     break;
   }
  }
  return 0;
}

userH4H6 · Answer

You need to change the loops as they are doing several calls to Class SpreadsheetApp on each iteration.

Regarding the first loop,

for (i=2;i<=lastRowAll;i++){
    if (ssAll.getRange(i,1).getBackground() == "#a8d08d"){
      var sourceRange = ssAll.getRange(i,1,1,15);
      sourceRange.copyTo(ssMaster.getRange(masterCounter,1));
      masterCounter++;
    } else if (ssAll.getRange(i,1).getBackground() == "#e2efd9"){
      var sourceRange = ssAll.getRange(i,1,1,15);
      sourceRange.copyTo(ssMaster.getRange(masterCounter,1));
      masterCounter++;
    } else {
      if (ssAll.getRange("P" + i).getValue() == true) {
        var sourceRange = ssAll.getRange(i,1,1,15);
        sourceRange.copyTo(ssMaster.getRange(masterCounter,1));
        ssMaster.setRowHeightsForced(masterCounter, 1, 136);
        masterCounter++;
      }
    }
    
  }

Instead of getting the background of one cell at a time (ssAll.getRange(i,1).getBackground()), before the loop get the backgrounds of all the cells before the loop, i.e.

const backgrounds = ssAll.getRange(2,1,lastRowAll).getBackgrounds();

then replace ssAll.getRange(i,1).getBackground() by backgrounds[i-1][0].

Do the something similar about ssAll.getRange("P" + i).getValue(), before the loop get the all values of the P column:

const values = ssAll.getRange("P" + i + ":P" + lastRowAll).getValues()

then replace ssAll.getRange("P" + i).getValue() by values[i-1][0]`.

It might be also possible to optimize further the first loop depending on if you really need to copy the ranges (besides values, include borders, background, notes, etc.) or if you only need the values.

Another option is to use the Advances Sheets Services but this implies to make a completely different implementation.

Google Apps Script run faster

1 Answers