1

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?

Black sky
  • 11
  • 1
  • 1
    it's a partial code, uncompleted. Basically it move the set function our of the loop. In sample1 code, each loop, it will call a set function to set the cell color, this function will be called 10000 times. In the sample2, it moves the set function out of the loop, so each loop it will save the color coordinate in an array, then the set function will only be call 1 time. – liquidkat Feb 18 '22 at 03:09

1 Answers1

0

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)
NightEye
  • 10,634
  • 2
  • 5
  • 24