Google Apps Script – How to Alternate Colors in an Ordered List by Column Category. 

Google Apps Script and Google Sheets

Imagine that you have a Google Sheet that you have sorted by a certain column. You might be sorting by the surname of your sales team, class sections or regions. To make the sheet easier to read for your team, you want to alternate the background colours after each category in your sort column is complete.

The Example

I have the following list of numbers in column 1 and 2. I have sorted these numbers by my Grouping Column of planets in column 3. After each grouping, I have alternated the background colour to make the transition easier to read.

Alternating color by section - Google Apps Script

The Code

Two functions are used in this code:

  1. getSections()
  2. sectionColor()

For the purpose of this tutorial, we will be focusing on the sectionColor function. You can find out more details about the getSections function here:

Google Apps Script – Get the Start Row and Length of Each Category in an Ordered Column in Google Sheets

Global Variables

Lines 6-11 sets up our sheet and range information. I have used getActiveRange in this example, so I will have to physically select the range I want to use.

Line 14 is our sort column of Groupings of Planets. We then get the header dimensions in line 16. Finally, the backgroundColor array is created (line 18) with the two colours we’ll alternate with after each group of planet data.

getSections()

The getSections() function iterates through the column you have sorted by – in our case column 3 – and creates a nested array of the start location and length. The results of example would look like this:

[[2.0, 3.0], [5.0, 5.0], [10.0, 4.0], [14.0, 5.0]]

More details on this function here.

We’ll then use these array values to alternate through our…

sectionColor

The sectionColor function takes one argument, sections. This argument is the resulting nested array generated from the getSections() function.

Line 47 starts the for loop using the sections array length as its termination value.

Next on line 49 we get the range of all the value in the section. In our example, this will be all the data in the range of each planet category.

Google Apps Script’s getRange can take 4 values:

sheet.getRange(start row, start column, row length, column length);

In our case:

  • start row is sections[i][0]. The starting row location of the current section.
  • start column is range.getColumn(). Which will identify the first column of our range of data.
  • row length is sections[i][1]. The total number of rows of the current section.
  • column length is lastColumn. The last column in the range.

To select the alternating colours we are going to do some fancy footwork with Javascript’s remainder operator (%) or modulo. What this operator does is get the remainder of two values when divided by each other.

For example: 2 divided by 2 (2/2 = 1) will result in 1 cleanly with no remainder. However, 3 divided by 2 will result in 1 with 1 remaining (3/2 = 1 and 1 remaining). What the remainder operator does is tell us what is remaining. So, the remainder of 2 divided by 2 would look like this:

2%2 = 0

Alternatively, the remainder of 3 divided by 2 would look like this:

3%2 = 1

This means that any even number that is divided by two will produce a zero. This is extremely helpful when identifying even and odd numbers.

Our if statement in line 50, then asks if i or the value or the iterator is even (or zero) then set the background colour to the lighter orange, backgroundColor[0]. Alternatively if i is odd, then set the background colour to the darker orange, backgroundColor[1]. Note that we are starting on the zeroeth number so even (or zero) will be first.

In both conditions, we use Google Apps Script setBackground() method in the Range Class.

start()

The start function is simply an example function to call the getSections() function to grab the section range data and then run the sectionColor() function to alternate the colours.

Conclusion

The getSections(), sectionColor() combo is a useful tool to alternate colours for each category of data in an ordered list making it easier for your sheet users to distinguish between groups.

 

 

 

6 thoughts on “Google Apps Script – How to Alternate Colors in an Ordered List by Column Category. ”

  1. Hi!
    I am trying to run this example and I get this error message:
    Exception: The number of rows in the range must be at least 1. (line 23, file “Code”)

    I have selected the range A1:C18, any idea on what could be wrong?

    thanks! I appreciate you taking the time to make this code available to everyone!

  2. Hi Yagi,
    This in response to your comment on my previous question, my header row looks exactly like yours, I recreated the sheet you are using on your example, thanks!

    1. Hi Marilenis,

      It seems to be working well for me.

      Hmm. Did you run the “Start” function?

      Before line 23 can you do a:

      Logger.log(range.getValues())

      Then go to View >>> Logs and copy and paste in those values, please?

      1. It worked! It was a problem with my google account authentication, nothing to do with the code. Thanks!

Leave a Reply