One spreadsheet have a 50x36 array with values. The function should get values from certain columns and build an array if col+1 cell is greater than 0. The first part of the code is calling the page once, getting all the values in one line, then with nested for and an if, creating the desired array. The second part is doing the exact same thing, but calling the page with getValues() several times, which supposed to be slower and not a good practice according to google. The fist one spits out a result 100ms slower than the second one. Can someone explain?
function PieseArray() {
let pag = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Configuracion');
//start counting time for the first part
let start1 = new Date
let allTheParts = pag.getRange(11,40,50,36).getValues()
let array2=[]
for (let i=0;i<36;i=i+4){
for (let j=0;j<allTheParts.length;j++){
if(allTheParts[j][i+1]>0){
array2.push([allTheParts[j][i],allTheParts[j][i+1]])
}
}
}
let end1 = new Date
Logger.log(end1-start1)
//start of the second part
let start2 = new Date
let piese = []
for(let k=40;k<=72;k=k+4){
piese = piese.concat(pag.getRange(11,k,50,2).getValues())
}
let array1=[]
for (let i=0;i<piese.length;i++){
if(piese[i][1]!=''){
array1.push(piese[i])
}
}
let end2 = new Date
Logger.log(end2-start2)
return(array1)
}
Execution log
1:06:11 PM Notice Execution started
1:06:12 PM Info 107.0 (first part)
1:06:12 PM Info 15.0 (second part)
1:06:12 PM Notice Execution completed
The result is the same array, but the execution time is very strange. I was expecting that the second part of the code to be way slower.
Apps script has lookahead caching, where the values you need are already cached. If you run the code one after another, getting the same values from the same range, then the values cached from the first code is used in the second.
Furthermore, Both the codes are not doing the same thing. The main comparison is different.
if(allTheParts[j][i+1]>0){
is different from
if(piese[i][1]!=''){
To properly time these functions,
console.time
and console.timeEnd
to correctly time the functions