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