Faster Alternative to a For Loop Google App Script Javascript

Question

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);
   
}

I think that your script is incomplete. So, although this is my guess from your script, if you are using getValues() and setValue in the loop. In this case, the process cost will be high. How about modifying them? If my understanding is correct, Best Practices and stackoverflow.com/tags/google-apps-script/info might be useful. If I misunderstood your script, I apologize. — Tanaike
– Tanaike, Commented Nov 17, 2020 at 8:54
One way is to skip the filter and go with reduce directly. — dork
– dork, Commented Nov 17, 2020 at 8:54
This loop works fine how is that? you have an else statement without an if statement there. Please provide the full code. — Marios
– Marios, Commented Nov 17, 2020 at 8:57
sorry. I had an if statement in the loop that was not important for the concept of this question, and forgot to remove the else statement following it. Thanks! I've edited the code accordingly. — Eric Johnson
– Eric Johnson, Commented Nov 17, 2020 at 8:59

Marios · Accepted Answer · 2020-11-17 09:34:33Z

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.

Collectives™ on Stack Overflow

Faster Alternative to a For Loop Google App Script Javascript

1 Answer 1

Explanation:

Improved Solution:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Explanation:

Improved Solution:

Comments

Your Answer

Sign up or log in

Post as a guest

Related