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

Google Apps Script and Google Sheets

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.

The Example

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.

The Code

Below is a simple script that grabs the section information of a column and records it’s starting row and length so that you can manipulate it in the future.  You can essentially copy and paste the getSections() function into your own code to grab groupings by a column identifier. The demo() function, as the name suggests, just runs the getSections() function and Logs the results.

First, we will layout the Global variables from Line 6 to Line 18. Line 7 to 10 grabs the spreadsheet and sheet we are working on. The range variable grabs the active range in this case, which means that it grabs the range that you have selected with getActiveRange. You could also select the range with a set value with getRange, or get all the data in the sheet with getDataRange.

The lastRow will be important when setting the range of the column.

Line 13 selects the column (Note when selecting ranges in Google Apps Scripts, start at 1, not 0, … well until you put them in an array).

Line 15 then identifies the Header so that we can take it into account. The header format is as follows:

var header = [ start row, start column, row length, column length];

getSections()

On lines 20 to 21, we select the range and values of the column we want to sort by. These are then put in the array sortColumnValues. Remember, now that they are in an array, the value locations start at zero, not one.

Line 25 creates the changeVal array that will store the start position and length of each section – in our case planets. We know the start value of the first array because it is the height of the header  (header[2]), plus 1.  This is a nested array. Each child array will have the starting position of the section followed by the section length. For example:

[[Start position1, section1 lenght][Start position2, section2 length]...]

[[2,3],[5,5],[10,4]...]

Line 28 is our count variable that will count how many rows in a section.

The for loop on lines 29 to 38 does the real heavy lifting. First, on line 31 it looks at the previous value in the row and compares it to the current one. If they are different then the if statement is run. If the values are the same, then the for loop with continue iterating through the array adding 1 to the count, essentially counting how many rows there are in the selection.

Once running the if statement (line 32-36) variable, newSectionStart records the starting position of the new set of planets information:

var newSectionSart = i + 1 + header[2];

Remember we want the actual value in rows. Knowing that the row values start at 1 and the array starts at zero, we add one to the i count. We also want to take into account the depth of the header so we add the header depth as well.

Remember the count variable? It would have counted the times the if statement was not successful, resulting in the length of the column category. We push this value (line 33) to the end of the last nested array of values. So in that now we will have the following in our changeValue array:

[[2,3]]

This means that we have identified that the first section in column 3 – Mars – starts on line 2 and has 3 values.

Line 34 then adds the new section starting position and on the next line, count is cleared to zero to begin counting again after exiting the if statement.

On the first iteration of the if statement we will then collect the following data in your changeValue array:

[[2,3],[5]]

Line 37, starts the count again.

After running through and completing the full loop, the changeValue array will look like this:

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

You’ll notice that we are missing the last value of the last nested array. Line 39 handles that by pushing the final count to the array to get our last value:

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

Finally, line 40 return the array for use in another function.

demo()

Demo simply demonstrates the getSections() function and then logs the resulting array as follows.

 

Conclusion

Now that you have the starting position and length of each category in your column on your Google Sheet, you can now use the Google Apps Script getRange method to do some interesting stuff with it.

Check out the following example to see how to create alternating colours for each category in a column using Google Apps Script.

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

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We 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.