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

Try using getFormulas() and setFormulas() and remove all activates() and get all of your data at one time with getValues() or getDisplayValues(). These return 2d arrays and you can iterate through them atleast 10x faster. — Cooper
– Cooper, Commented Jan 8, 2020 at 17:38

Cooper · Accepted Answer · 2020-01-09 14:50:22Z

2

Sometimes it's just simpler to use the direct writes because you don't want to mess with over writing the formulas that are really not part of the process. But this function removes the individual reads for each rowPath variable and also eliminates the activates() which are used so much in macros.

function autoJ7() {
  var ss=SpreadsheetApp.getActive();
  var msh=ss.getSheetByName('New Accoounts Q4');
  var mrg=msh.getRange(1,8,msh.getLastRow(),1);
  var mvA=mrg.getValues();
  mvA.forEach(function(r,i){
    var row=i+1;
    if(r[0]<13) {
      msh.getRange(row,16,1,1).setFormula("=vlookup(A"+row+",\'J+7\'!A:M,13,0)");            
      Logger.log("Path A");
    }else if(r[0]<20){
      msh.getRange(row,17,1,1).setFormula("=vlookup(A"+row+",\'J+14\'!A:M,13,0)");
      Logger.log("Path B");
    }else if(r[0]<42){
      msh.getRange(row,18,1,2).setFormula("=vlookup($A"+row+",\'J+21\'!$A:$M,13,0)");    
      Logger.log("Path C");
    }
  });
}

edited Jan 9, 2020 at 14:50

answered Jan 8, 2020 at 18:09

Cooper

64.2k7 gold badges28 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Louis Strauss Over a year ago

Thank you so much! I am trying it now but it tells me that row is not defined? Would you be able to tell me how or where to define it ? Thank you very much again

ADW · Accepted Answer · 2020-01-08 18:02:04Z

A few ways to speed things up:

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.
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
}

Collectives™ on Stack Overflow

GoogleScript - Speeding up a simple loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related