• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Digital Answers

How to tech tutorials in simple words

  • Home
  • About Us
  • Disclaimer
  • Privacy Policy

Change alternate row background colors in Spreadsheet using Google Apps Script

August 22, 2011 by Admin 8 Comments

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.

Filed Under: Excel, Technical Tagged With: Alternate rows, Google Apps Script, spreadsheet

Reader Interactions

Comments

  1. somnath says

    September 28, 2012 at 10:59 am

    This script is not working on google Spreasheet.

    gave error as Illegal character. (line 13)

    Thanks

    Reply
  2. brasilboy says

    November 13, 2012 at 9:03 pm

    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
  3. brasilboy says

    November 13, 2012 at 9:05 pm

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

    Reply
  4. elango says

    January 10, 2016 at 12:37 pm

    Change the single quotes instead of double quotes.

    Reply
  5. Ariel Devulsky says

    January 21, 2016 at 3:41 am

    // 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
    • Ravi Shankar says

      January 22, 2016 at 12:34 pm

      Thanks

      Reply
  6. Andy says

    December 22, 2017 at 2:39 pm

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

    Reply

Trackbacks

  1. How to use conditional formatting to colour alternate rows in Excel 2013 says:
    December 12, 2013 at 11:35 am

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

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Recent Posts

  • Disable conversion of two numbers with dash as date in Excel
  • How to Protect Formulas in Excel
  • How to Change the Default Font in Excel
  • How to Add Country Code to Phone Automatically in Excel
  • How to Insert Pictures in Excel

blogmines.com Copyright © 2023