Is there a way to make this loop more efficient?

Question

I've got a Google Sheet where I'm compiling an inventory. I've noticed a bunch of mathematical errors recently, so I decided to cut down on the mental addition and build an Apps Script loop that adds two columsn and resets the first column for me. But because I'm using a getValue in basically every loop, it's a very time-intensive process.

I tried looking for ways to write to arrays, etc, but this is my first foray into Google Sheets rather than Excel.

Here's the whole function:

function Update() {

  var ss = SpreadsheetApp.getActive();

  var sheet = ss.getSheetByName('Name');


 for (var x = 2; x < 905; x++) {


  var range1 = sheet.getRange([x],7);

  var num1 = range1.getValue();

  var range2 = sheet.getRange([x],8)

  var num2 = range2.getValue();


  range2.setValue(num1 + num2)

  range1.setValue ('0')

  }
}

It's working correctly, but it's really slow and I'd appreciate any tips on how to make this more efficient.

use getRange() to get the range you want to look at and then iterate over it using JavaScript. This will drastically reduce the number of calls to the server and lead to much better performance. — bartinmader
– bartinmader, Commented Feb 5, 2019 at 5:31

Cooper · Accepted Answer · 2019-02-07 16:30:16Z

3

Try this:

function Update() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Name');
  var rg=sh.getRange(2,7,904,2);
  var vA=rg.getValues();
  for(var i=0;i<vA.length;i++){
    vA[i][1]=vA[i][0]+vA[i][1];
    vA[i][0]=0;
  }
  rg.setValues(vA);
}

edited Feb 7, 2019 at 16:30

answered Feb 5, 2019 at 5:30

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Niek Waarbroek Over a year ago

This is a good example. Avoid lookups in the loop. Just get all data and loop through the values, update the array of values and write back (setValues) in 1 call.

New_2_Code Over a year ago

Yeap this is how I would go about it. Much more efficient to get all data in one shot and then just loop through the results. (Rather than fetching the data every time it is needed.)

Heretic Monkey Over a year ago

Answers which explain how code works are better than ones without, especially for future readers.

tehhowch · Accepted Answer · 2019-02-05 12:28:35Z

2

As Cooper's answer illustrates, you want to use the batch Range methods getValues and setValues to manipulate a JavaScript array, per "best practices"

An improvement is to use Array class methods, to reduce nested property accessing, such as map (return an array based on the source array) or forEach (do something for every element in the source array). These class methods provide your callback function with the current element, index, and source array as function arguments, in case they are needed.

When you want short-circuit behavior (stop processing once some condition is met), the traditional index-based for loop is still king.

function processArray() {
  const wb = SpreadsheetApp.getActive();
  const sheet = wb.getSheetByName('Name');

  const startRow = 2, endRow = 905;
  const startCol = 7, numCols = 2;
  const rg = sheet.getRange(startRow, startCol, endRow - startRow + 1, numCols);

  // Construct a new array based on the source array.
  const result = rg.getValues().map(function (row) {
    return [ 0, row[0] + row[1] ];
  });
  // (In this case we haven't changed the dimensions, so we could re-use `rg` .)
  if (result.length) {
    // sheet.getRange(someRow, someCol, result.length, result[0].length).setValues(result);
    rg.setValues(result);
  }
}

(Note that currently Apps Script does not support the block-scope use of const, just the reassignment protection)

answered Feb 5, 2019 at 12:28

tehhowch

9,9124 gold badges27 silver badges47 bronze badges

3 Comments

New_2_Code Over a year ago

Any benefit to using .map instead of just getting the range tehhowch? (Or are you just showing off. :D)

Cooper Over a year ago

Thanks for that example. I have to admit I tend to avoid those methods.

tehhowch Over a year ago

@New_2_Code ?? The range is acquired (as rg). The values from that range are also acquired, but (since OP isn't doing anything else with them) they aren't stored in a variable before they are acted on. Example code for writing the computed values to a different range is provided, just commented out. The major difference from Cooper's answer is the number of property accesses per row - 8 vs 2. Probably the performance difference due to the property access alone is not too great, but whichever engine is running the code may have its own optimizations for map and the other Array methods.

Collectives™ on Stack Overflow

Is there a way to make this loop more efficient?

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related