performancegoogle-apps-scriptgoogle-sheets

Why is the optimized g script code slower than the "inefficient" one?


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.


Solution

  • 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,

    • Use two different functions - calling them one after another shuffling each time. Make sure the execution of each is completely stopped before the next function is called.
    • Use built in console.time and console.timeEnd to correctly time the functions
    • Make sure both the code does the same number and type of operations. Not just that the output is the same.
    • Run each test thrice or more.