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 colors 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 color 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 colors 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 colors 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 color to the lighter orange, backgroundColor[0]. Alternatively if i is odd, then set the background color 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 colors.

Conclusion

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

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *