CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsoptimization
mark
mark

Reputation: 873

Speed up basic slow google script for spreadsheet

I've created a simple script which iterates over the active (selected) range and checks if the cell value is less than 0. If it is, it makes the cell red, otherwise green! It works fine, but takes a long time (over a minute) to run on a small sheet (26rows x 30columns)

Is there anyone anyone can see to speed this up?

function colourize() {
  var range = SpreadsheetApp.getActiveSheet().getActiveRange();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();

  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      if( range.getCell(i,j).getValue() < 0 ) {
        range.getCell(i,j).setBackground('#ea9999');
      } else {
        range.getCell(i,j).setBackground('#b6d7a8');
      }
    }
  }
};

Upvotes: 1

Views: 2119

Answers (1)

Serge insas
Serge insas

Reputation: 46812

Just do it at array level like this :

function colourize() {
  var range = SpreadsheetApp.getActiveSheet().getActiveRange();
  var values = range.getValues();
  var BGColors = range.getBackgrounds();

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j <values[0].length; j++) {
      if(values[i][j] < 0 ) {
        BGColors[i][j]='#ea9999';
      } else {
        BGColors[i][j]='#b6d7a8';
      }
    }
  }
  range.setBackgrounds(BGColors)
};

Upvotes: 3

Related Questions