Creating Unique Ranges from 2D Arrays in Google Apps Script

Google Apps Script, Google Sheets, Javascript 

If you have ever done any work in Google Sheets you might have come across the incredibly useful UNIQUE function. This function can take a single row or multiple rows and generate a list of unique cell values.

Sometime you might have a use case where you need to replicate the UNIQUE functionality in Google Apps Script. Maybe you are drawing in data from another source like an API, BigQuery or a database, or presenting your unique data in another format.

Whatever the case, knowing how to to create a unique array from a 1-dimensional array or a 2-dimensional array is an important part of your toolkit as a coding goat ?. This tutorial will look at how to create this with modern Javascript that is incorporated into the Google Apps Script language. And of course, we’ll focus our tutorial more on a Google Apps Script because that’s kinda our thing.

We’ll start off with a very basic example of creating a unique array from a 1-dimensional array in our code:

…and then build ourselves up to a final example by grabbing multi-column range of Google Sheet data from which we create a unique list and then display the total sum corresponding to each unique cell value in each of our associated unique columns. Something like this:

As we go through our examples I’ll display the runtime of the entire code. Runtime is not a perfect indicator of the performance of the script there are other factors that will influence the runtime, but it will do for our purposes. I think you will be surprised by how fast these scripts run.

Before we get started, let’s take a quick look at the sample data…

Sample Data – A Fictional List of Blood Bank Donations

The data we are using consists of a fictional list of Blood Bank donations that contains the following information:

1. Blood Type
2. City
3. Age Group
4. Number of Donations

There may be multiple rows containing the same blood type, city or age group, and multiple occurrences when all three may occur at once.

Imagine that there are multiple clinics in each city and each clinic is providing blood type and age group data into this sheet.

Take a look at the sheet below:

Here is a copy of the link to the Google Sheet if you are keen to play along:

Fictional List of Blood Donations

Let’s get cracking with our first example.

1. Creating a Unique Array of Values Using a flat 1-dimensional array

In this simple example, we will forego the sample data and provide a simple array of data that contains duplicate values.

We’ll split our code into two functions consisting of:

• a main run function that will contain our main variables and return our results to a Google Sheet.
• a unique function that will generate our unique array of values

The main run function is like a place marker for your own function that you draw your data from and then run the function to create the unique array.

Take a look at the code:

Runtime: 0.68 seconds

Note: You can pop out the code above by hovering over the menu and clicking the pop-out button.

This code takes in a single array containing duplicate values that are hardcoded in the runsies() function and returns a unique array that is then added to the Google Sheet.

In short, it turns this array:

const myArray = ['A', 'B','C','C','D','E','E','E','A','B', 'C','C'];

…into this Google Sheet Column:

runsies()

Setting up the variables

The runsies() function starts off with the flat array containing duplicate data on line 7.

Next, we call the currently active spreadsheet with the SpreadsheetApp class and the getActiveSpreadsheet() method on line 10.

Then we add our global variables. First, we need to tell Google Apps Script what sheet we want to paste our final results into with the getSheetByName() method that takes the name of the sheet tab as an argument. In our case, this is the “Unique” sheet tab on line 11. Our next variable tells Google Sheets what cell we want to start our paste from on line 12.

Sending off the array to make it unique

We already have our array to transform into a unique array so we don’t need to do anything else before we send our myArrayvariable to our createUniqueSingleArray() function on line 16.

Google Sheets gets and sets range of values as a 2d array where the data of each row is an array that is wrapped around a larger array. So for example this array:

…would be displayed like this in Google Sheets:

preparing the Google Sheet to receive the data

When you set values into a Google Sheet you need to need to provide Google Apps Script with the range of the 2d array that you wish to insert into the sheet. This requires the start row and start column and the depth rows and columns it will occupy.

In our example,  we assigned our starting row and column on line 12 as “A3”. It’s a bit tricky to find the end cell value at a certain letter and number combination (known as A1-notation). So instead we will find the number of rows down and the number of rows across.

To do this we first need to ask Google Apps Script to find the range or our “A3” cell in our Unique Google Sheet tab. Line 19

const uniqueRange = UNIQUE_SHEET.getRange(UNIQUE_ROW_START);

Here we use the getRange() method to find the range. This method can take either a comma-separated number of arguments or an A1-notation range. In our case, we are using the A1-notation.

Now that we have the range of the start cell, we can get the row and column using the getRow() and getColumn() methods respectively.

const rowStart = uniqueRange.getRow(); line 20

const col = uniqueRange.getColumn(); line 22

The only other range value we need to get is the depth of the row. We can work this out by getting the length of our newly created unique array. line 21

SENDING off the DATA to the Google SHeet

Our final task is to send off our unique data and set it into our Goolge Sheet at our desired location. Here we use the getRange() method again, but this time we use number values for our start row, start column and row depth. Line 26

The setValues() method pastes the array into the Google Sheet. It takes one argument, in our case, the unique array.

 

createUniqueSingleArray(vals)

The createUniqueSingleArray(vals) function takes one parameter, the 1d array containing duplicate values. It returns a single-column 2d array of unique values ready to be set into a Google sheet.

The first task on line 38 uses new Set() to store unique values. This is then sorted into a new array with the spread syntax (...). The “spread” syntax permits the expansion of an iterable object, like an array or a string, to fill in zero or more arguments (in function calls) or elements (in array literals) at the expected places. If we put our new Set() function with our list of duplicate values inside a new array with the spread syntax at the start it will create a new unique array.

let unique = [...new Set(vals)]; //Make unique

It’s quite likely that you would want to sort your newly created unique array. We do this here with the sort() method on line 39. If you don’t want your new array to be sorted, you can leave this line out.

let uniqueSort = unique.sort() //Sort Array

Finally, on line 41, we use the map() method to iterate through each value in the array and put it inside its own array so that each value appears one under the other in a column. In our example the returned results would look like this:

This is then returned back the runsies() function.

return uniqueSort.map(row => [row]); //Create 2d array for Google Sheets

The map() method takes a function as an argument. Here, we are using arrow functions; arrow functions are shorthand versions of the standard function with a few differences and limitations. The map() method iterates through each element in its attached array. It allows you to do something with part or all elements of the current iteration and returns the data for each iteration. We will be using map() and some of the other functional programming methods as we expand on our code in the next examples.

Note! As we continue through the other examples we will just cover what has changed only so we are not repeating ourselves.

2. Creating a Unique Array of Values Using Single-Column Data drawn from a Google Sheet.

In this next example, we are going to grab the blood type column of our Google Sheet sample data and then return a unique column list of all the blood types presented in the sample data.

Here’s the code:

Runtime: 1.186 seconds

runsies()

Adding variables to grab the data from the Google Sheet

We need to add some more variables to the Global area of our runsies() function so that we can draw in the data from our Google Sheet of sample data.

Having already selected our Spreadsheet under the SS variable, we use this to grab the sheet tab “Data” with the getSheetByName() method on line 9.

From there, we grab the range we want with getRange(), this time using the A1-notation approach. Here you can see we are only selecting one column, Column A, which is the blood type data.

Once the range is selected the getValues() method is called.

You could substitute this with your own approach to drawing in the code from a Google Sheet or some other API.

createUniqueSingleArray(vals)

This time around our vals parameter is a 2d array that is drawn from the Google Sheet. Before we can get unique values out of this data we need to flatten the data to a 1-dimensional array. Fortunately, Javascript has a simple method for this. The flat() method. If you leave the flat() method empty, it will flatten the array by one level. Otherwise, you can input an integer parameter indicating how many levels you want to flatten your array.

let singleArray = vals.flat();//Convert 2d array to 1d array

The final result is this:

3. Creating a Unique Array of Values From a Single Column of data drawn from Multiple columns of Google Sheet data.

In this example, we are bringing in all the columns and rows of data. We will need to select one column from all this data and find the unique values of that selected column.

This time around we will want to grab a unique list of all the cities from the full list of sample data.

This would be a pretty common scenario where you might be drawing from an array of values where you want to filter out unnecessary data and focus on just one column.

Here is the code:

Runtime 1.273 seconds

runsies()

Tweaking the Globals

Again we are making a few changes to the global variables. First, on line 11, we have selected a range that includes all 4 columns of data to simulate a large dataset with many columns.

On the following line, we indicate what column we want to isolate from that data to make it unique. This would probably be done programmatically by searching through headers or some other approach, but for now, we are selecting the column by number where A is 1, B is 2, C is 3 etc.

Change the Unique array function

You can also see now that our createUniqueSingleArray() function takes two arguments. Line 41

1. The full set of values. That is all the values from col A to col D.
2. The array indicates the selected column from which to get the unique array. Here we subtract the column number by one because our arrays start on the zeroeth number in Google Apps Script (Javascript). Line 44

const unique = unique_single(VALUES,SELECTED_COL - 1);

createUniqueSingleArray(vals, col)

As mentioned above, the createUniqueSingleArray(vals, col) function takes two parameters now. The full data set and the select column we make the array from.

Unlike the previous example where we flattened the array, this time around we use the map() method to create a new 1d array extracting only the selected column. Remember col is the column number of the current row being iterated through.

let singleArray = vals.map(row => row[col]);

A sample of the resulting unique array is as follows:

4. Getting the total sum of blood donations by Age Group.

In this example, we grab the Age Group column and Blood Donations column from our dataset and generate a unique list of ages and then calculated the total sum of donations for each age group in a second column.

Here is a sample of our raw data:

And the final calculated totals:

Here is the code:

Runtime 1.11 seconds

runsies()

Some changes to the globals

Now that we are both creating a unique column of data and calculating some corresponding data associated with those unique values we need to identify 2 columns to work on.

First, on line 12, we have selected the range for the age groups (col C) and the number of blood donations (Col D).

Then we rename the old SELECTE_COL variable to a more explicit UNIQUE_COL and we also add CALC_COL for our calculation column. Lines 13-14

The new function to create the unique and  calculation array

It’s probably about time to change the name of our function to create our unique array column and corresponding calculation column (line 22). Here we now add 3 arguments:

1. vals – The data set.
2. UNIQUE_COL - 1 – The unique column minus one to start the count from zero.
3. CALC_COL - 1 – The calculation column minus one to start the count from zero.

uniqueSingleWithCalc(vals, uniqueCol, calcCol)

As you can likely see, we do the exact same thing as we did in the previous example to create our unique array. Once we have this we can reference the array to calculated our corresponding data.

Adding the calculation to the unique array

To calculate our corresponding data and pair it with our unique data we first need to run map on our newly created unique data set with the variable name uniqueSorted.

On each iteration of our unique variable, we want to iterate once again through all the raw data and find all the rows that match the currently iterated uniqueSorted row of data. For example, on our zeroeth iteration, we are searching the raw data for all instance contain the age group 0-20.

We create this list by using the filter method. The filter method takes a function inside which you create a true condition based on the rules you set. In our case, the rules are simple. If the raw data column contains the age group matches the currently iterated age group of uniqueSorted then keep that row of data. line 55

The great part about these functional methods like filter, map and reduce is that you can chain them one after the other. We’ll make use of this now by applying the Javascript reduce() method.

The reduce method allows us to reduce all the values we iterate over to a single value. The reduce method takes a function as its primary parameter. Again we will use arrow functions to do this. You will need at least two parameters for the reduce method and accumulator (acc) and the currently iterated data (cur). Line 56

The accumulator retains the calculations of all the iterated values in the array of data. In our example, it will accumulate the sum of each donation as we run through our filtered list of donations only for the age group 0-20. We do this by adding the accumulator to the current iteration. For us, this current iteration is a nested array containing the age group and the donation so we will need to add cur[calcCol] to our accumulator.

The zero at the end of this is an optional argument of the reduce method that allows us to include an initial value. This is zero for us. Line 56

Once we have our calculated sum for the current row we add it to our currently iterated unique data so that we have two columns of data. Line 59

return [row, calc];

5. Getting a 2 Dimensional Unique Set of Values

In this next example, instead of getting a single column of unique values, we are going to create a unique row of values. For our example, we will select the city as our first unique item. Each city may have multiple blood types and each of those blood types in that city may have multiple age groups. Take a look at the sample result data:

As you can see with Alexandra Headland, not all blood types were donated. However, all age groups for A+ and O+ were donated. Keep in mind that there might be multiple clinics in each city reporting data so we just need the unique list from all of these reports.

A full sample of all the results will be linked at the end of this tutorial.

We’ll be modifying the 3rd example to create this code. Let’s take a look at the code:

Runtime 1.55 seconds

Runsies()

Updating our Global Variables

This time around we will select the entire range of the sample dataset on line 11.

We also need to indicate what columns and in what order we want to create our unique rows of data. For our example, we will start will col 2 (Cities), then col 1 (Blood Type) and finally, col 3 ( Age Group).

Our new function to generate the unique rows of values

The name of our function needs to be updated to be more clear. This time around we went for uniqMulti(). We assigned 2 arguments to this new function. The first is our standard set of duplicate values. The second argument is our list of columns we will use to make unique. Line 20

const unique = uniqueMulti(VALUES,UNIQUE_COLS);

uniqueMulti(vals, uniqueCols)

The first task in our new unique-creator function is to quickly update our uniqueCols array by subtracting each column number by one with the map method. Line 43

Our next task is an interesting one. So we need an easy way for us to calculate if the combination of column data for each row is unique. The fasted way that I have found to do this is by combining each selected item in the row into a string.

The first task here is to map through all the values in the array so we can start iterating through each row of data in the original data set. Line 46

Inside this map, we need to then map through our selected list of columns and only grab the data on each row associated with those columns and in the order we have set. Line 47

Once we have generated our new row array, we can flatten it into a single string with JSON.stringify. This JSON method allows us to convert all our data into a single string but containing all the coma separation and braces. Line 48

Now that each row is a single string, we can use our old approach to create a list of unique items and sort them.

Once we have done do that we then map through our new unique array and use JSON.parse() to return our row data back to its original array form.

 

6. Getting a 2 Dimensional Unique Set of Values With an Associated Calculations Column

This time around we want to get the total number of donations for each city by blood type and age group.

Here is a sample of the end result:

As you can see, we are using the same 3 sets of unique column values as our previous example.

Take a look at the code:

Runtime 2.21 Seconds

runsies()

A tiny update of the globals

This time around we need to include one extra variable, the selected column we wish to do our calculation on. In our case, this is the donations column in column 4. Line 18

THE NEW unique function

Because we need to indicate what column our calculations will be drawn from, we need to add the CAL_COL to the arguments for the function.

uniqueMultiWithCalc(vals, uniqueCols, calc_col)

Create our stringified data

Our first change to this function is when we create our list of stringified data. Instead of just returning this unique row as a string we also include the data from the corresponding calc column on line 52. We will use this new 2d array to do 2 things. First, we will create our unique array and then we will reference this array to calculate the sum of donations.

Next, we map through the newly created array above to generate a pure 1D array of stringified selected columns. line 56

CALCULATING our data to add to our unique set

In our uniqueWithCalc array, we must compare our unique array (uniqueArray) against the full stringified array of duplicates data and our associated donation data (selectedArrays_With_CalcCol ). If the stringified row in selectedArrays_With_CalcCol  matches uniqueArraywe need to sum the data.

To start, we first map through our uniqueArray . This time around we have two parameters the current row and the optional parameter, the index (idx). Which is the current array number that the map is up to. Line 66

Next, we string together functional Javascript methods to calculate our total.

First, need to filter out only the current unique value from our stringified uniqueSorted[idx] array. We use the idx to get the current row of that array and compare it against each row of selectedArrays_With_CalcCol.

Once we have all the rows matching the currently iterated unqueSorted value we then use map to create a new array that only consists of those associated blood donations.

Finally, we use reduce to add these values together to create our total for that unique row of data.

This is then added to the end of each row of the uniqueArray.

A final quick example of this last code

In this final example, we want to get the total number of donations by age group and blood type. Just like below:

All you should need to modify is:

• const UNIQUE_COLS = [3,1];
• const UNIQUE_ROW_START = "S3"; // So you don’t paste it on top of the last data set.

Give it a try!

Runtime for me was 1.374 Seconds. What did you get?

Conclusion

In this tutorial, we have thoroughly traversed how to create unique datasets from very basic single column sets to complex multicolumn unique data sets with associated calculations.

Here are the results you should have got if you were playing along:

And here is the link to the Google Sheet:

Creating Unique Ranges in Google Apps Script

This should give you a deeper upstanding on how to use many of the functional Javascript methods to manipulate data in Google Apps Script.

I’d love to hear in the comments what you have used these code snippets in.

If you enjoyed this journey, why not subscribe to my blog (top right)? I send out regular posts on Google Apps Script and all things Google Workspace.

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.

Happy coding!

~Yagi