**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.

**The Code**

Two functions are used in this code:

`getSections()`

`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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
/** * Alternate the color after each different column category. */ // --Global Variables-- var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getActiveRange(); var lastColumn = range.getLastColumn(); var lastRow = range.getLastRow(); //Sort Column var sortColumn = 3; // Header var header = [1,1,1,3]; // Background Colors var backgroundColor = ["#fce5cd","#f9cb9c"]; function getSections() { //get Range and Values of the sorting colum after the header var sortColumnRange = sheet.getRange(header[2]+1, sortColumn, lastRow-1); var sortColumnVals = sortColumnRange.getValues(); //Creates a list of start and section length vals e.g.[[2,3],[6,5],[11,5]] // For example[[Start position1, section1 lenght][Start position2, section2 length]...] var changeVal = [[header[2]+1]] //Loops through column and identifys the changing value location var count = 1; //used count the length of the section. for(i=1;i< sortColumnVals.length; i++){ //if the previous column value is not the same as the current one. Note the row number and length of section. if(sortColumnVals[i-1][0] !== sortColumnVals[i][0]){ var newSectionSart = i + 1 + header[2]; changeVal[changeVal.length-1].push(count); changeVal.push([newSectionSart]); count = 0; }; count = count += 1; }; changeVal[changeVal.length-1].push(count); return changeVal; }; function sectionColor(sections){ for(i=0;i< sections.length; i++){ //Get the range of the section var sectionRange = sheet.getRange(sections[i][0],range.getColumn(),sections[i][1],lastColumn); if(i%2 == 0){ //Even numbers background Color sectionRange.setBackground(backgroundColor[0]); }else{ //Odd number background Color sectionRange.setBackground(backgroundColor[1]); }; }; }; function start(){ var sectionGetter = getSections(); var alternateColor = sectionColor(sectionGetter); }; |

**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**

46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
function sectionColor(sections){ for(i=0;i< sections.length; i++){ //Get the range of the section var sectionRange = sheet.getRange(sections[i][0],range.getColumn(),sections[i][1],lastColumn); if(i%2 == 0){ //Even numbers background Color sectionRange.setBackground(backgroundColor[0]); }else{ //Odd number background Color sectionRange.setBackground(backgroundColor[1]); }; }; }; |

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 lengt**h 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.