// Sheets · Apps Script

Alternate row colors with a script in Google Sheets.

Script that builds a full 2D background-color array and writes it in a single setBackgrounds call — faster than looping cell by cell, and more flexible than the built-in banding API.

“I want to stripe alternating row colors in my Google Sheet without manually painting every other row, and without the banding API re-flowing my colors whenever I insert or delete rows.”

The script

copy · paste · trigger
alternateRowColors.gs
Apps Script
// Alternate row background colors across a named sheet.
// Writes the entire color grid in one setBackgrounds call.
function alternateRowColors() {
  var SHEET_NAME = 'Sheet1';
  var EVEN_COLOR  = '#ffffff';
  var ODD_COLOR   = '#e8f0fe';

  var sheet = SpreadsheetApp.getActiveSpreadsheet()
                            .getSheetByName(SHEET_NAME);
  var numRows = sheet.getLastRow();
  var numCols = sheet.getLastColumn();

  var colors = [];
  for (var r = 0; r < numRows; r++) {
    var rowColor = (r % 2 === 0) ? EVEN_COLOR : ODD_COLOR;
    var row = [];
    for (var c = 0; c < numCols; c++) {
      row.push(rowColor);
    }
    colors.push(row);
  }

  sheet.getRange(1, 1, numRows, numCols).setBackgrounds(colors);
}
Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Build the 2D array, write it once

The core idea is to never call a Sheets API method inside a loop. Every call to setBackground (singular) on an individual cell crosses the Apps Script quota boundary — 30 seconds of execution time is easy to hit on a 1,000-row sheet if you're painting cell by cell. Instead, build a JavaScript array-of-arrays where each inner array is one full row of hex color strings, then hand the whole thing to setBackgrounds (plural) on a single range. One network round-trip, regardless of row count.

getLastRow() and getLastColumn() give you the occupied extent of the sheet. Using those instead of a hardcoded range means the script stays correct when someone adds columns later. If your sheet has a header row you want left untouched, change the range start: getRange(2, 1, numRows - 1, numCols) and start the loop index at 1 so parity math still gives you even/odd relative to data rows, not the absolute sheet row number. I keep that offset logic in a comment the first time I hand this to a non-developer — it trips people up every single time.

Why not applyRowBanding

Sheets has a native banding API: range.applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY) or a custom-color variant. It is genuinely useful for pure display striping with no further manipulation. The problem is that banding is a live formatting rule attached to the range, not baked-in cell colors. Insert a row in the middle, delete two rows, sort — the band re-computes automatically. That sounds convenient, but it means you cannot mix the band color with a per-row highlight (say, red for overdue items) without the banding rule overwriting your highlight on the next recalculation.

setBackgrounds writes static cell background colors. They survive row insertion, sort, and copy-paste exactly as any other cell format does. If you need a conditional highlight on top, you can run a second pass that overwrites specific rows with a status color. The banding approach cannot do that cleanly — you would have to remove the band, paint manually, then never re-apply it. Static backgrounds plus a script-driven conditional paint is the pattern that scales.

Attaching a trigger so it stays current

Running the function once gives you static stripes at that moment. If rows are added frequently, wire up an installable trigger: in the Apps Script editor, go to Triggers, add a time-driven or spreadsheet onChange trigger pointing at alternateRowColors. The onChange event fires on structural changes (insert/delete row, sort) but not on cell edits, which is usually the right granularity — you don't want a full re-paint every keystroke.

One quota note: each setBackgrounds call counts as one Sheets API write operation, not numRows operations. Even triggered on every change, a sheet with a few hundred rows will stay well inside the 20,000 API calls per day free-tier limit. The first time I hit that limit it was because someone had wired setBackground (singular) in a loop inside an onEdit trigger on a busy collaborative sheet — 40 editors, each edit firing 500 individual calls. The 2D-array approach makes that category of mistake structurally impossible.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions
The script colors my header row too — how do I skip it?
Change the range to start on row 2 and shrink the height by 1: sheet.getRange(2, 1, numRows - 1, numCols). Also start your loop at r = 1 if you want row 2 to be your 'even' color, or r = 0 if you want it to be odd — it depends on whether you treat the header as row 0 or row 1 in the parity count.
Can I use this on a range smaller than the full sheet?
Yes. Replace getLastRow() and getLastColumn() with literal numbers, or use sheet.getRange('A2:F200') and read .getNumRows() and .getNumColumns() off that range object. Then call setBackgrounds on the same range. The 2D array dimensions must match exactly or you will get an 'Incorrect range height' error.
My colors reset whenever I sort the sheet. How do I keep them?
Sorting re-orders rows including their cell formats, so the stripes follow the data, not the row positions. Re-run alternateRowColors after the sort. Wire an onChange trigger (Triggers > Add Trigger > event type: On change) and it will re-stripe automatically every time a sort happens.
What hex format does setBackgrounds expect?
Six-digit lowercase hex with a leading hash: '#ffffff', '#e8f0fe'. Shorthand three-digit hex like '#fff' is not accepted and will silently apply no color. Named colors such as 'white' are also not accepted by setBackgrounds — they work in some other Sheets API calls but not this one.

Related scripts