Slow Loop To Find And Copy

April 19, 2024 Post a Comment

I have a written a function to create reports: Detect if there's a specific letter in a specific range (function asks user for letter and column) Grabs the entire row if it has th

Solution 1:

Profiling your code

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

let start = newDate()

Logger.log("before first for loop")
Logger.log(newDate().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:

Baca Juga

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 row1, // 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.

JavaScript College