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
