Pavel Valeriu Pavel Valeriu -5 years ago 189
Javascript Question

Optimization for alternate background rows color in Google Spreadsheet

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:

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");
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");

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download