CODEWITHSUNDEEP
javascriptarraysfor-loopgoogle-apps-scriptgoogle-sheets
Eric Johnson
Eric Johnson

Reputation: 89

Faster Alternative to a For Loop Google App Script Javascript

I've created a for loop that pulls sales data in columns from an array (salesLog) and places them into a sheet (targetSheet). The sales data is kept in multiple columns. The columns between columnStart and columnEnd are added (unit), matched with the correct row in the source sheet, and placed in the correct row in the targetSheet. This loop works fine except it's too slow, and a fear unscalable with more data. I'm looking for a way to run this loop faster. Any help?

var length = POlistTarget.length;

for (var i=0; i <= length; i++){
  //find Row
  var row = POlistSource.indexOf(POlistTarget[i]);

  //findColumns
  var columnStart = periodArr[0]+1
  var columnEnd = periodArr.length
  var unitArr = 
salesLog.getRange(row+3,columnStart,1,columnEnd).getValues().flat().filter(row=>row!="");

  //add units in an array
  var unit = unitArr.reduce(function(a,b){return a+b;},0);



  //execute



    targetSheet.getRange(i+4,7,1,1).setValue(unit);
   
}

Upvotes: 1

Views: 952

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

  • As also Tanaike mentioned in his comment, the main improvement is to use setValues once outside of the for loop instead of using setValue inside the for loop iteratively. In this way you will make one call to set the data instead of POlistTarget.length calls. See more details in best practices.

  • Another improvement both in terms of performance but also code formatting is to use forEach instead of for loop. The idea is to get rid of unnecessary code.

Improved Solution:

const units = [];
POlistTarget.forEach((pt,i)=>{                         
        let row =  POlistSource.indexOf(pt);
        let columnStart = periodArr[0]+1;
        let columnEnd = periodArr.length;
        let unit = salesLog.getRange(row+3,columnStart,1,columnEnd).
                   getValues().
                   flat().
                   filter(r=>r!='').
                   reduce((a,b)=>a+b);
        units.push([unit]);                     
});

targetSheet.getRange(4,7,units.length,1).setValues(units);

More improvements:

If your code was complete, we could see whether it could be feasible to use getRange for the full array on salesLog so you can get rid of getRange inside the for loop. In this way, you could skip the for loop altogether.

Upvotes: 1

Related Questions