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.
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 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
|
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 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.
Need help with Google Workspace development?
Go something to solve bigger than Chat GPT?
I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
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!
Hi Marilenis,
What does your header row look like?
Cheers,
Yagi
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!
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?
It worked! It was a problem with my google account authentication, nothing to do with the code. Thanks!
Great to hear!
Happy coding.
I am getting
Error Exception: The number of rows in the range must be at least 1.
for line that contains: var sortColumnRange = sheet.getRange(header[1]+1, sortColumn, lastRow-1);
Any idea what’s wrong?
And would I be able to add more colors?
Thanks
Hi John,
I am guessing that your last row is less than your header row here. What his your header value?
Yes, you could add more colours, but not with the calculation used in this example. You would need a mathematical way to distinguish between each group and n colours.
~Yagi