• 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

Alternate rows

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

Primary Sidebar

Recent Posts

  • Streamline your Excel spreadsheets with these easy steps to hide rows and columns
  • Effortlessly Convert Text Cases in Excel with These Methods
  • A Guide to Using the MAX and MIN Functions in Excel
  • How to display fractions in Excel
  • How to Separate a Person’s First and Last Name in Excel

blogmines.com Copyright © 2023