javascript - Slow loop to find and copy

Question

I have a written a function to create reports:

1. Detect if there's a specific letter in a specific range (function asks user for letter and column)
2. Grabs the entire row if it has the letter.
3. Copies it to another sheet.

My code is currently working, but it takes ages to finish. Also, if I have more than hundreds of results, I get a timeout issue.

You can reproduce the issue here:

https://docs.google.com/spreadsheets/d/1ggVvxquruYfckNWxhsV6-Od2J8QIkMOYpJps7qz9PkI/edit?usp=sharing

This is the code:

for(var i = 0; i<rapport.length-1; i++) {
    if(colonneCode[i] == code.getResponseText()) {
      ligneCode[v] = i;
      v++;
    }
  }

for(var i = 0; i<ligneCode.length;i++) {
    
    var codeLastRow = 12;
    var copySource = sheet.getRange(ligneCode[i]+10, 1, 1, 16);
    var copyTarget = feuille.getRange(feuille.getLastRow()+1,1,1,16);
    copyTarget.setValues(copySource.getValues());
    copySource.copyTo(copyTarget, {formatOnly:true});

  }

Click on the personalized menu (évaluations Philippe Caron) -> Classement personnalisé -> First input box "2" -> Second input box "e" -> Third one is the name you want the new sheet.

Answer 1

Profiling your code

I used these snippets to check the times of your code.

let start = new Date()

Logger.log("before first for loop")
Logger.log(new Date().getTime() - start.getTime())

And as you probably guessed, for the e example on your sheet, most of the code until the second for loop ran in about 1 second. The second for loop, however, took around 45 seconds. This part:

for(var i = 0; i<ligneCode.length;i++) {
    
    var codeLastRow = 12;
    var copySource = sheet.getRange(ligneCode[i]+10, 1, 1, 16);
    var copyTarget = feuille.getRange(feuille.getLastRow()+1,1,1,16);
    copyTarget.setValues(copySource.getValues());
    copySource.copyTo(copyTarget, {formatOnly:true});

  }

Why is this code slow?

Because during every single iteration it is calling getRange, getValues, setValues, copyTo. All these commands require that the Apps Script execution read and write from the spreadsheet. This is slow.

Dumping the whole range

It is much faster to collect the whole range in a large 2D array and setValues all together. This will require building the range within Apps Script first. So instead of storing the index numbers of the rows in ligneCode you store the whole row in an output.

By the way, you can use array.push(item) to add an item to the end of an array. No need to keep track of index numbers.

So instead of this:

var ligneCode = [];
for(var i = 0; i<rapport.length-1; i++) {
    if(colonneCode[i] == code.getResponseText()) {
      ligneCode[v] = i;
      v++;
    }
  }

Build an array that represents what you will paste in the new sheet.

var output = [];
for (var i = 0; i < rapport.length - 1; i++) {
  if (colonneCode[i] == code.getResponseText()) {
    output.push(rapport[i]);
  }
}

Then once you have your new sheet feuille, all you need to do is:

var target = feuille.getRange(
    11, // first row
    1, // first column
    output.length, // the height of the output
    output[0].length // the width of the output
    );
target.setValues(output);

Result

Now with the e example at the start, the whole script takes about 2 seconds to run.

Reference

• Range Class in Apps Script Documentation
• setValues details