Odd Behaviour with Alternating Colors in Google Sheets

I have a slew of Google Sheets that I’ve built (and continue to build) for various purposes - mostly financial - and I sometimes add in alternating colors to make the rows of data more aesthetic and readable.

Now if you’ve never used alternating colors in Google Sheets before, it’s a neat feature that allows you to automatically apply a different color to every other row in your sheet.

You can find it in the Format menu, under the aptly named Alternating colors option.

I’ve used alternating colors directly in Google Sheets many times before without issue, but recently noticed some odd behaviour when using it programmatically with Google Apps Script, and it left me scratching my head (and cursing quite a bit 🤪) for a few hours! It was only after deciding to walk away from my computer for a bit that I realized what was likely happening.

Now spreadsheet applications are a pretty interesting beast, as there’s a lot they have to do behind the scenes to make things work as smoothly as they do. And it’s because of this that I think I ran into the issue I did.

The Problem

In my apps script code, I created a function to apply formatting to sections of a sheet. The function takes a range of cells as a parameter, and applies a header row, footer row, and then alternating colors between them.

The exact code I was using isn’t important, but the gist of the part that applies the banding (the alternating colors) is as follows:

range
  .offset(contentSectionTop, 0, contentSectionLength)
  .applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY, false, false);

where contentSectionTop is the row-offset from the section range where the content starts, and contentSectionLength is the number of rows in the content section.

When you use the applyRowBanding method, you can also have it apply a header row color, as well as a footer row color, if desired, using the 2nd and 3rd parameters respectively. In my case though, I was applying my own separate header and footers, so I set both of these to false.

The Odd Behaviour

The odd behaviour I was seeing though was that despite me setting the 2nd and 3rd parameters to false, and despite the header row being fine, the banding was “leaking” into my footer rows, which I didn’t want.

My initial attempt at figuring out the issue

I assumed at first that my code must have been miscalculating the number of rows in the content section, but after adding some console logging to my script, I could see that the number of rows was indeed correct.

I ran the script a bunch of times, as initially I was getting inconsistent results from one run to the next. But the inconsistent results turned out to be due to an unrelated bug, which after I fixed, the banding issue remained.

My next attempt at figuring out the issue

Despite console logging the ranges and row counts, and seeing that they were correct, I figured that the data had to be lying to me, and so I decided to hard-code the range the banding was being applied to, to see if that would fix the issue.

So I removed the offset method and replaced it with a hard-coded range, and ran the script again.

SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheetByName("Sheet1")
  .getRange("B2:D4")
  .applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY, false, false);

But to my surprise, the banding was still leaking into the footer row, and even beyond! 😤

It was at this point I was about ready to throw my laptop out the window, so I figured it was time to take a break and come back to it later.

Well, go figure, about 10 minutes later, I realized what was likely happening. It was late though, and I had to get the little one ready for bed, so I decided to call it a night and come back to it the next day.

The Solution

Now, there’s something I didn’t mention previously, but that’s visible in the last pic showing above. Something that I was doing in another part of my code that I didn’t even think about initially, but that I had later realized was likely the cause of my issue. Or well, I hoped it was the cause of the issue, as I was running out of ideas!

You may have even figured it out based on the last image…

After I was applying the banding in the function I had created, I was setting the content (text) for the full range (header, content, and footer). Note that I wasn’t inserting any rows, just setting the text for the range, something along the lines of:

SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheetByName("Sheet1")
  .getRange("B1:D5")
  .setValues([
    ["Header 1", "Header 2", "Header 3"],
    ["Content 1", "Content 2", "Content 3"],
    ["Content 4", "Content 5", "Content 6"],
    ["Content 7", "Content 8", "Content 9"]
    ["Footer 1", "Footer 2", "Footer 3"],
  ]);

So my suspicion was that by setting the content after applying the banding, the banding was expanding to the full range, and not just the specific range I was setting.

Now this is something that would definitely make sense if I was inserting rows with the content, but that’s not what I was doing. I was just setting the text for the range, not adding extra rows, and so I didn’t think it would cause the banding to be modified/expand.

So after rearranging my code so that the banding was being applied after setting the content, sure enough, the issue was resolved.

Yup, that’s all it was. I just needed to reverse the order and make the banding apply after setting the content. Sigh. 😑

In fact, you can even test this out without using apps script. Just set some alternating colors to a small section of a Google spreadsheet, and then paste in some content that overlaps the range of the banding. You’ll see that the banding will expand to the full range, and not just the section you initially set it for.

Recap

So to recap, and to maybe save you from a little bit of frustration, if you’re going to be applying alternating colors, or “banding”, to a range of cells in Google sheets: make sure you set the content of the section first, and then apply the alternating colors.

Does it make sense that the alternating colors expand out like I noted? To me this seems counter-intuitive, but maybe I’m just weird. 🤷‍♂️

What do you think? Let me know what your thoughts on this are in the comments below! 👇