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

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 2

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.