Skibs' Tech and Geekery

Fast Banded Rows for Google Spreadsheets

I couldn’t find any other scripts for formatting a Google spreadsheet with banded rows, so here’s some quick code I wrote that gets the job done. Hopefully, this is useful to someone else out there.

Banded rows script:

//Method gives odd rows one background colour and even rows another background color
function addBandedRowColor() {
   //Hex Code for white color, used of even rows
   var colorRowEven = '#CCCCCC';
   //Hex code for light grey color, used for odd rows
   var colorRowOdd = '#FFFFFF';
   //Get the current work area from the current spreadsheet
   var range = SpreadsheetApp.getActiveSheet().getDataRange();

   //Iterate accross each row of the work area
   for (var i = range.getRow(); i < range.getLastRow(); i++) {
      if (i % 2 == 0){ //if even row
         //Set row background to a one color
         range.offset(i, range.getColumn()-1, 1, range.getNumColumns()).setBackgroundColor(colorRowEven);
      } else { //if odd row
         //set row background to another color
         range.offset(i, range.getColumn()-1, 1, range.getNumColumns()).setBackgroundColor(colorRowOdd);
      }
   }
}

And for the sake of balance, here’s a script that gets rid of the banded rows:

//Method gives each row in the spreadsheet a flat background color
function removeBandedRowColor() {
   //Hex code for white color
   var colorRow = '#FFFFFF';
   //Get the current work area from the current spreadsheet
   var range = SpreadsheetApp.getActiveSheet().getDataRange();

   //Give the area a single background color
   range.setBackgroundColor(colorRow);
}