Optimization for alternate background rows color in Google Spreadsheet

Question

I set a different background color for the even rows from the Spreadsheet sheet using Google Apps Script but it is very very very slow...any ideas how can I optimize it?

Example Spreadsheet: https://docs.google.com/spreadsheets/d/1yRotjooCRpuuSTjjgFEzw4xxqPLJwMZJPchYQeNvUyw/edit?usp=sharing

See the GS code by going to Tools -> Script Editor...

This is the code:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange("A1:Z1000");
  ss.setActiveRange(range);
  var totalRows = SpreadsheetApp.getActiveRange().getNumRows();
  var totalColumns = SpreadsheetApp.getActiveRange().getNumColumns();
  var startRow = SpreadsheetApp.getActiveRange().getRow();
  var startColumn = SpreadsheetApp.getActiveRange().getColumn();
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = startRow;
  while (row < totalRows+startRow)
  {
    var column = startColumn;
    while (column < totalColumns+startColumn){
      if(row%2 == 0){
         sheet.getRange(row, column).setBackground("#F3F3F3");
      }
      column++;
    }
    row++;
  }
}

Answer 1

Go line by line instead of cell by cell. You can also loop directly on the lines you want to change.

function setBackgroundColorOnEvenLines() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();
  var totalRows = sheet.getMaxRows();
  var totalColumns = sheet.getMaxColumns()

  for (var i=2; i <= totalRows; i+=2){
      sheet.getRange(i, 1, 1, totalColumns).setBackground("#F3F3F3");
  }
}

This reference shows how to reference an entire line like above.

Answer 2

You can use new applyRowBanding():

Applies a default row banding theme to the range. By default, the banding has header and no footer color.

Example:

function setColors() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange("A1:Z1000");
  
  // first remove any existing alternating colors in range to prevent error "Exception: You cannot add alternating background colors to a range that already has alternating background colors."
  range.getBandings().forEach(banding => banding.remove());
  // apply alternate background colors
  range.applyRowBanding();
}

Result:

For more options also see

• applyRowBanding(bandingTheme)
• applyRowBanding(bandingTheme, showHeader, showFooter)

Answer 3

You might also like to look into this call:

https://developers.google.com/apps-script/reference/spreadsheet/range#getbackgrounds

and its sister call

https://developers.google.com/apps-script/reference/spreadsheet/range#setbackgroundscolor

This returns a two dimensional array for the entire range of all background colours.

Using this you can get an array of the whole range, step through it in increments of two, and set all of the elements of each row to the alternate background colour you wish (perhaps using one of the methods described here: Javascript | Set all values of an array). Then at the end call setBackgroundColors(color) to set the colors.

This way, you only make one call to actually update the spreadsheet at the end - and that's the part that will take the most time and which will have slowed down your original code the most.

Answer 4

While you've asked for a Google Apps Script solution, I prefer to set a conditional formatting by formula rather than do this as a scripted solution - I feel it's less complicated, faster and more intuitive.

=mod(A1,2)=0

Will format all even rows in the range defined.