Quite often I will need to get the range of each category in an item and do something with it in Google Sheets. For example, I work in education, I will often have rows of students that are categorized by class sections. I will then be asked to do something like those sections like put each section of students in their own sheet or set alternating colours for each section to make the sheet easier to read.
Alternatively, you may want to grab sales data by region or sales items by a particular category and work with them in Google Apps Script.
Let’s say we want to get the range values of the following sheet by planets. We will be categorizing our data by the Grouping, column C.
First, we don’t want to take into account the headers on the first row. Our first grouping will be Mars, followed by Jupiter, Uranus, and Mercury. We want to know which column that each category starts on and how many of that category there are.
Google Apps Script: SpreadsheetApp, getRange, getLastRow, getDataRange
I was trying to rush out some Google Apps Script code to deal with a task on Google Sheets recently. Basically, I had to search through a heap of data and find certain values and do something too them.
My column was reaching across the page to something like Column BK and my rows were over 1000 deep. Running this code was taking forever!!!
My immediate instinct was:
What have I done wrong?
…and my instinct was right.
The Good and Bad Way to Search Through Code
So after looking at my code again, I discovered that for some reason I go it into my head that I should be searching each cell for the value I needed and then doing something with it.
Sounds logical right? It’s sorta what you are meant to do.
The problem is that I was calling the sever and asking for the range in each cell as I was looping through the entire document. This is super costly and inefficient in terms of time.