How to batch the calls in google app script

Question

I was trying to optimize runtime some code that ran really slowly, when searching it up google came up with this: https://developers.google.com/apps-script/guides/support/best-practices#:~:text=Use%20batch%20operations,-Scripts%20commonly%20need&text=Alternating%20read%20and%20write%20commands,data%20out%20with%20one%20command.

it shows an example of inefficient code:

var cell = sheet.getRange('a1');
  for (var y = 0; y < 100; y++) {
    xcoord = xmin;
    for (var x = 0; x < 100; x++) {
      var c = getColorFromCoordinates(xcoord, ycoord);
      cell.offset(y, x).setBackgroundColor(c);
      xcoord += xincrement;
    }
    ycoord -= yincrement;
    SpreadsheetApp.flush();
  }

and efficient code:

var cell = sheet.getRange('a1');
  var colors = new Array(100);
  for (var y = 0; y < 100; y++) {
    xcoord = xmin;
    colors[y] = new Array(100);
    for (var x = 0; x < 100; x++) {
      colors[y][x] = getColorFromCoordinates(xcoord, ycoord);
      xcoord += xincrement;
    }
    ycoord -= yincrement;
  }
  sheet.getRange(1, 1, 100, 100).setBackgroundColors(colors);

I tried to understand how this code works and tried running it but first of all "cell" doesn't seem to get used and I do not understand the code at all. What is a version of the code that actually works and how does this make it more efficient? And what part of this code batches the calls and how can I use this in my own coding?

Answer 1

Basically, what it does it reduce the number of calls by its methods.

The inefficient code above calls offset and setBackgroundColor every loop thus making it resource intensive and time consuming.

For the efficient code, it uses the loop to build the 2d array which then will be used on a single call on the method setBackgroundColors to execute it by bulk.

What takes much of the run time is the method calls, so reducing it would be very beneficial. This would make use of loops to build 2d arrays for the array versions of the methods single execution.

e.g.:

• setValue -> setValues
• setBackgroundColor -> setBackgroundColors

Pseudocode:

from:

loop {
  setValue(x[i][j])
}

to:

loop {
  x[i][j] = data
}
setValues(x)