Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column

Reduce selected columns in Google Sheets by unique values in Google Apps Script

Google Apps Script, Google Sheets, getRangeList, 3D arrays

In a number of situations in my day-to-day work, I need to grab data from only Selected Columns in Google Sheets. Then using a selected column as a key, remove any duplicates in a similar way that a pivot table does.

I might want to use this information as part of a report or grab emails from a sheet and send custom emails to clients or staff.

I don’t always need all the data in each row, but I may need a lot of it. The two standard approaches would be to:

  1. Get all the data and then in Google Apps Script just select the relevant columns you need.
  2. Iterate through a list of columns you want to get ranges of and select each range individually making calls to your Google Sheet in each turn.

This might be useful for small data sets and, to be perfectly honest, I have used both these approaches in the past, but recently I stumbled across a method in the Google Apps Script Sheet Class called:

sheet.getRangeList([A1Notation,...,...])

Now I think that the awesome gods of GAS may not have purposed this method with the thought in mind that it would be used for collecting range values. I kinda get the impression that it was more designed for applying formatting to the selected column ranges.

However, what I have found is that it seems to be pretty fast at collecting all the ranges and then, on the Google Apps Script end, getting their values.

The resulting values of using getRangeList() can be then pushed into a 3D array of columns with row values.

For those of you in a hurry, the code is below. We will move on to an example and explanation after. Feel free to reference what you need and discard the rest.

The Code

Quick-Run Guide

The run() function consists of the key variables you need to changes for your own code (Lines 14-19):

  • startRow: number: row number where the range you want to select starts.
  • key_column: number: column number that you will use as a reference to reduce your data to unique values. For example, students in a section may have a column with a section number, you may wish to select the unique values of just the section without duplicates of the same section.
  • personalized_columns: array: Array of all the columns you want to be selected.
  • spreadsheetID: string:  The unique alphanumeric reference to your spreadsheet.
  • sheetName: string: The name of the sheet you wish to reference.

Next, on lines 21-23 we call the selected spreadsheet and the named target sheet tab. We then let Google Apps Script get the last Row number. The code will use the targetSheet sheet object and lastRow number in the getRanges() function.

personalized_columns is then updated to have the key_column at the front of the array. We could have added the these two into the personalized_columns but it is more explicit for the user at the top of the code.

To return the 3D array of each column’s values, we then call the getRanges() function which takes 4 parameters:

  • targetSheet
  • startRow
  • personalized_columns
  • lastRow

To get the unique values by the first column in the returned getRanges() 3D array we use the getUniqueFrom3dArray() function. This returns a standard 2D array that Google Apps Script will accept when setting values or you might use to iterate through to send emails, share folders or generate reports.

The Example

In our example, we have a store that sells high-end goods to exclusive customers. The store has a spreadsheet of client data including sales and the client’s personalized account manager.

Have a look at the data above ( you can click on the sheet and hold shift + mousewheel to scroll across). Perhaps we want to get a list of Customers and their emails along with the total cost of all purchases in the sheet for each customer, the account manager’s name and the account manager’s phone number.

For the brevity of this example, we’ll just log the results and paste the results in a new sheet… coincidentally, called  “Results”.

Here is the updated code. I’ll highlight the changes for you:

Let’s see where our first 3 EXTERNAL VARIABLES come from:

creating 3d Array of Cols in GAS

When we execute the run() function we will get a logged (line 43) list of unique customers by email, first name, last name, total cost of sale, their accounts manager’s name and their accounts manager’s phone number.

Log of unique rows by email with selected column values in Google Apps Script

The run() execution also pastes the above-logged values into the “Results” sheet tab. We did this by running our little function pasteIntoCells() that you can see down the bottom of the script.

Google Sheets list of unique rows by email with selected column values using Google Apps Script

This makes use of the Google Apps Script range.setValues() method.

Let’s take a brief tour of our two main functions.

getRanges(targetSheet, startRow, personalized_columns, lastRow);

The getRanges() function takes 4 parameters: sheet, starRow, pers_cols and lastRow.

We first set an empty array on line 15 to catch all the column ranges.

This will store an array of R1C1 notated values.  Why?  When we use the sheet.getRangeList() method, it requires an array of A1 notated values – well, according to the docs. However, I did discover that it also takes R1C1 notation.

Why didn’t I go for A1 notation? Letters are not fun to use script-side. I’d much rather have column numbers than letters because they are easier to count and add into functions and methods.

Next, we loop through all the selected columns in the pers_cols(line 16). We push the R1C1 values for each column we want into the  columnRanges array.

Next on line 21 we insert the newly created list of column ranges and use that as our rangeOfColumns variable which uses our darling of today’s tutorial, sheet.getRangeList(columnRanges).

The next step is to get the values from that range of columns. We first need to get all the ranges from our getRangeList with getRanges(line 23).

We then need to loop through each column range and get the values for each row in each column we’ll then store them in our valsForEachCol = [];

This will generate a 3D array like this:

This 3D array is then returned to the run() function.

getUniqueFrom3dArray(valsForEachCol);

This function takes the 3D array generated from the getRanges() function.  It uses the first set of values from column one to find all the unique values. In our example, it would take the email column and collect all the unique emails. In all the other corresponding columns it will also get the values and then pack it all into a 2D array with each row containing the key column (e.g. email) and then a list of all the corresponding values.

On line 12, we first set the unique array variable that we will store all our values.

We then start our main for loop (line 15) that will loop through each row in the zeroeth (first) column.

Before we are able to check to see if the value is unique, we first need to add the first set of row values for each column (lines 17-27). This gives us something to compare against. This section loops through each column and creates an array of all the values on line 0 of each column before pushing it to our unique 2D array.

Next, we need to be able to compare our current unique list against the row values in column 0 to make sure they are unique (lines 29-38).  First, we set the condition that the value in column 0 isUnique totrue. We then test this by looping through the list of unique values in our array and checking to see if they exist (lines 31-33). If there is a match, then we want to change the isUnique boolean to false and break out of the unique loop.

However, if the column 0 row value does not exists in the unique array, we want to push the column 0 value and all the other corresponding row values in our other columns into an array. We will then push this unique value into our unique array.

Finally we return the unique array to the main run() function.

Conclusion

I use this set of function in a number of day-to-day tasks like sending out emails, opening edit permissions for docs and sheets and creating custom letters and reports.

Have you made use of this? Do you have a better approach? I would love to hear from you in the comments below.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get your from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

 

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

~Yagi

3 thoughts on “Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column”

  1. I’m looking for a way for the code to check existing values in ‘Results’ and only append any new ones instead of overwriting the data already populated in the ‘Results’ tab, including data manually added to the ‘Results’ tab that did not generate from the ‘High End Sales’ tab. Any suggestions on what I could add in to make it check for duplicates and append only unique rows?

Leave a Reply