GoogleScript - Speeding up a simple loop

Question

I've just started dabbling in VBA for Google Sheets and have written a script, however, it's insanely slow, is this due to a mistake below or is there a way of speeding it up ?

The point of this script is to have 3 work tabs and one mastersheet to save the data each day. Once all the VLookUps are set, I'll copy and paste them as values, but so far the loop is way too slow to even consider using it.

function autoJ7(){

  var masterSheet= SpreadsheetApp.getActive().getSheetByName('New Accounts Q4');
  var masterLength = masterSheet.getLastRow();
  
  for(var row = 1; row < masterLength; row++){
    
    var rowPath=masterSheet.getRange(row,8).getValue();
    
    if(rowPath<6) {}
    
    else if(rowPath<13) {
    
      masterSheet.getRange(row,16,1,1).activate().setFormula("=vlookup(A"+row+",\'J+7\'!A:M,13,0)");            
      Logger.log("Path A");
    }
    
    
    else if(rowPath<20) {
    masterSheet.getRange(row,17,1,1).activate().setFormula("=vlookup(A"+row+",\'J+14\'!A:M,13,0)");
    Logger.log("Path B");   
    }
    
    else if(rowPath<42) {
      masterSheet.getRange(row,18,1,2).activate().setFormula("=vlookup($A"+row+",\'J+21\'!$A:$M,13,0)");    
      Logger.log("Path C");    
    }
    
  };
  

Answer 1

A few ways to speed things up:

1. Make fewer calls to the sheet: a. get all data from the sheet in a single call when possible (as is the case here) b. post data or formulas to the sheet in a single call.

2. If you have a lot of if-else conditions, switch-case may be faster. In this case, perhaps not. But I find it more elegant. :-D

Here is how I would write the script:

function autoJ7() {
  var masterSheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('New Accounts Q4');
  var data = masterSheet.getDataRange().getValues(); // Get all the sheet data in one call
  var rowPath, sheetRow; // This avoids defining variables repeatedly inside the loop
  var formulas = []; // Array to hold formulas to be posted back to the Sheet
  for (var row = 0; row < data.length; row++) {
    sheetRow = row + 1; //To adjust for the array being zero-based
    rowPath = data[row][7];
    switch (rowPath) {
      case (rowPath < 6):
        formulas.push(["", "", ""]);
        break;
      case (rowPath < 13):
        formulas.push(["=vlookup(A"+sheetRow+",\'J+7\'!A:M,13,0)", "", ""]);
        break;
      case (rowPath < 20):
        formulas.push(["", "=vlookup(A"+sheetRow+",\'J+14\'!A:M,13,0)", ""]);
        break;
      case (rowPath < 42):
        formulas.push(["", "", "=vlookup($A"+sheetRow+",\'J+21\'!$A:$M,13,0)"]);
    }
  }
  masterSheet.getRange(1, 16, formulas.length, 3).setFormulas(formulas); // Post to sheet in one call
}