CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Stormdance
Stormdance

Reputation: 11

Is there a way to make this loop more efficient?

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.

Upvotes: 0

Views: 423

Answers (2)

Cooper
Cooper

Reputation: 64040

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

Upvotes: 3

tehhowch
tehhowch

Reputation: 9862

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)

Upvotes: 2

Related Questions