Change alternate row background colors in Spreadsheet using Google Apps Script

Aug 22, 2011

—

Here is a Google Apps Script that would set the background color of alternate rows in Google Spreadsheet. This script would set the background color of of the selection, this may not be an efficient script but would do the job.

Let us take the following sample data which will be used by the script for setting the background color.

Install the below script and select the data by excluding the Header columns and execute the script.

function alternateColor() {
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).setBackgroundColor(“lightgrey”);
      }
      column++;
}
    row++;
}
}

After executing the script, the sample data would look as shown below.

If you want to change the white color then you can add an else statement to set the background color for odd row numbers as well.

if(row%2 == 0){
           sheet.getRange(row, column).setBackgroundColor(“lightgrey”);
      } else {
          sheet.getRange(row, column).setBackgroundColor(“lightblue”);
}

Let me know if any one have a better suggestionn.

Comments

8 responses to “Change alternate row background colors in Spreadsheet using Google Apps Script”

somnath

September 28, 2012

This script is not working on google Spreasheet.

gave error as Illegal character. (line 13)

Thanks

Reply
brasilboy

November 13, 2012

I had the same problem Somnath. All you need to do is replace the “lightgrey” with “lightgrey”. The quotes are of the wrong character type in the example above. Just replace with the regular double quote ” character.

Reply
brasilboy

November 13, 2012

This website is replacing the regular “quote character” with a fancy one.

Reply
How to use conditional formatting to colour alternate rows in Excel 2013

December 12, 2013

[…] Listed below are the steps for changing the background colour of alternate rows using conditional formatting. […]

Reply
elango

January 10, 2016

Change the single quotes instead of double quotes.

Reply
Ariel Devulsky

January 21, 2016

// Fixed script Zebra or Alternate colors

function onOpen() {
alternateColor();
}

function alternateColor() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(“Revisões Periódicas v2”);

//odd color
var linecolor = “#a8b7d1”
//Chosse the range
var range = sheet.getRange(“B5:U82”);

//Define start and end… Lines and columns

var totalRows = range.getNumRows();
var totalColumns = range.getNumColumns();
var startRow = range.getRow();
var startColumn = range.getColumn();
var row = startRow;

while (row < totalRows+startRow)
{
var column = startColumn;
while (column < totalColumns+startColumn){
if( row%2 == 1 ) {
sheet.getRange(row, column).setBackground(linecolor)
}
column++;
}
row++;
}
}

Reply
1. Ravi Shankar
  
  January 22, 2016
  
  Thanks
  
  Reply
Andy

December 22, 2017

You can omit the if-condition if you use
row += 2

Reply