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:
Click to Expand!
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…
I recently came across (yet another) situation where I needed to work with a text-based ranking system. You know, when some gem decided to create a ranking system like good, great, awesome, spectacular and then we need to store each user’s highest level of achievement over a period.
This occurs with surprising regularity in my work, most often when needing to work with spreadsheet data that a user has created. We can’t really change the data on the spreadsheet so we need to handle this with some code instead.
Not ideal, I know. We could complain about it ad nauseam or simply live with it and deal with it using some JavaScript magic.
In this tutorial, we will cover two approaches to this. Both approaches have their own uses. We will go over a few use cases so you get an idea of when these sorts of things come up.
Let’s get cracking.
Note: Sometimes it takes a couple of examples to figure out a concept. Other times, you can figure it out in an instant. Feel free to read as much as you need to understand the concept.
Table of Contents
Storing the most important text value with JavaScript
Example 1: Recording the best poker hand
Let’s say I am having a poker night with some friends and want to only record my best hand for the night, you know, to brag later.
First, we should list all the poker hands from best to worst:
Royal flush
Straight flush
Four of a kind
Full house
Flush
Straight
Three of a kind
Two pairs
Pair
High Card
As the night starts, my first hand was a ‘three of a kind’. So I make a note. Then, I only get a ‘pair’ for my next hand. I’m not writing that down. It’s worse. Next, I get a ‘flush’. Awesome, a ‘flush’ is better than a ‘three of a kind’, so I will store that one and put a line through my previous record.
Do you get the picture?
Cool. Let’s write some code.
We will need to store our list of poker hands in order and also create a variable to store the best hand we get during the game. We have two choices here. I will go through both and then discuss performance.
Storing data as an array
Array Set most Important
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
constpoker=[
"High Card",
"Pair",
"Two pair",
"Three of a kind",
"Straight",
"Flush",
"Full house",
"Four of a kind",
"Straight flush",
"Royal flush",
];
let bestHand="Three of a kind";
/**
* Compares the current text item agains the new item and returns the most
console.log("Best hand is: ",bestHand);// Straight flush
So what’s going on?
First, we store our list of all possible poker hands in an array called ‘poker’. Starting with the lowest hand on the left and finishing with the best hand on the right (bottom of the array). Lines 1-12
Arrays in JavaScript maintain their order so we know that “High Card” will always be in position poker[0] and “Royal Flush” will be in position poker[9]. We will be using the array’s order in a minute to check if we should be storing our next hand or not.
Next, we need a place to store our highest hand for the night. We set the variable bestHand for this, using JavaScript ‘let’ variable declaration to allow us to change the variable as we update it with a better hand. Line 14
setMostImportant_Array()
We now create the function setMostImportant_Array(). This function takes 3 arguments:
The array or list to reference
The stored item, in our case, bestHand.
The new contender item, which may or may not, be better than the stored one.
The function returns the most important item. This is done with a simple ternary operator, which is a kind of simplified ‘if’ statement.
On line 5, we compare the position of our stored bestHand value in its position in the poker array list against the new hand. We do this by using the indexOf method which returns the first occurrence of the matching item in an array as an index of that array. If the current bestHand is greater than or equal to the new hand then we return the current bestHand. Otherwise, we return the new hand.
Testing the results.
After we have set up our function we can now run it. Our second hand of the night was a ‘pair’ so we updated our best hand to our function adding in, poker as our array list, bestHand as our stored item, and “Pair” as our new item.
Because a pair is not as important as a ‘three of a kind’ we kept the current value.
On the next hand, we got a ‘Flush’ which is more valuable than a ‘Three of a kind’ so using our function again we see that bestHand now stores the ‘Flush’ as the most important value.
Storing Data as an object
We can achieve the same outcome by storing data as an object. Let’s take a look at the code.
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
constpoker={
"Royal flush":10,
"Straight flush":9,
"Four of a kind":8,
"Full house":7,
"Flush":6,
"Straight":5,
"Three of a kind":4,
"Two pair":3,
"Pair":2,
"High Card":1,
};
/**
* Compares the current text item agains the new item and returns the most
* important one.
* @param {Object} obj - the name ranked item: position as a number.
console.log("Best hand is: ",bestHand);// Straight flush
In our poker object, we store the names of each hand as a property string and then each property’s value is its position or order of importance in the group of data. Lines 1-12
Our setMostImportant_Object() function is a lot simpler than the array function above. Here we will use our ternary operator, but this time we simply are checking to see if the bestHand property value stored in our poker object is greater than the new hand property value. Line 14-24
Our function takes on arguments in the same way as the array function and will store the new property in the bestHand variable if the hand is better than the one stored.
Which is better? The array or object approach?
The most predictable and irritating answer first. It depends.
Running a benchmark test on the two they are fairly comparable, but over larger sets of stored ranked text, the object approach would perform better.
However, my preference would slant to the array approach if my ranking is not static. This is probably a rare case, but imagine if your text-based ranking system changes regularly and you need to update the order of you ranking system.
Moving your data around an array and cutting it out of one position and putting it in another is fairly simple with an array. With an object, however, we need to update the current value of the property that is going to change and then move all the other properties around than need to change. This would definitely be more memory intensive and harder to code.
Here is an example.
Example 2: Getting the best commodity in a fluctuating market
Imagine that we are investing in a fictional commodity market. As supply and demand fluctuate, a commodities value may move to become greater or lesser than other commodities in the same market.
Our goal in this scenario is to keep the best value commodity available to us. If the commodity’s value position compared to other commodities shifts, then we need to ensure that we always keep the best commodity available to us.
Let’s use this fictional list of commodities as our example:
tryzatium << currently lowest value.
ocoumbre
malcatite
enzome
parl
obvoster << currently highest value
We are first presented with the opportunity to buy ocoumbre. Having no investments in the market, we make the purchase.
Our next opportunity is to purchase tryzatium. Currently, this item is less valuable than ocoumbre so we won’t buy it.
Enzome comes up next. Being of higher value, we purchase it and sell off our ocoumbre.
Then the next day the market changes. A new large deposit of enzome is found and floods the market, reducing its value to below tryzatium. So when we get an offer to buy malacite we jump at it.
Let’s write some simple code to simulate this.
storing data in a fluctuating ranking system
Changeable commodity list.
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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
/**
* Compares the current text item agains the new item and returns the most
Just like in the first example, when a better commodity is presented to us we replace it with our current commodity. When there is a change to the order of the market, then we also see a change in how we prioritise what we keep.
moveItem()
moveItem()
JavaScript
1
2
3
4
5
6
7
8
9
10
/**
* Move a seleted item to a new position in the order.
* @param {Array} list - Array of ordered items.
* @param {String} item - The item to change position.
* @param {Number} newIdx - The new index of the item.
The moveItem() function allows us to change the order of priority of our list of commodities.
The function takes 3 arguments as parameters:
list – the array of ordered items. In the example, this is our commoditiyByVal array.
item – This is the item in the list that needs to change position.
newIdx – The new index or place in the list that the item needs to move to.
In the first task of the function, we need to get the current index of the item. We do this by finding its index in the array with the indexOf method. Line 8
Next, we use the JavaScript splice method to cut the item out of the array (The inner splice) and then add it back into the array at the desired location (The outer splice). Line 9
The outer splice takes 3 arguments:
splice(start index, deleteCount, item to add)
start index – our new index for the position of the item.
delete count – the number of items to delete. For us, this is zero. We only want to add an item.
item to add – This will be the item that we splice out. Not only does splice update an existing array, but it can also return the items that have been removed as an array. This is handy for us because we will use them in this last argument. This inner splice takes two arguments:
start index – the current index of our selected item.
delete count – one, the current item we need to move from the existing position.
Why I recently needed to store the most important item in a list in JavaScript?
Example 3: Time Trigger (Cron Job) efficiency
So the thing that actually compelled me to write about this was a part of a Google Apps Script project – Google Apps Script is built on Google’s V8 engine for EcmaScript JavaScript.
I was fetching requests from an external API that requested that an event be triggered at a particular interval. We use Clock Triggers in Google Apps Script but you might know them as Cron Jobs.
The API contained a list of text-based triggers that looked a little like this:
“daily”,
“6 hours”,
“1 hour”,
“30 minutes”,
“1 minute”
Anyway, we have limitations and quotas to how many clock triggers we have running in one account at one time so I always try and limit these. So if I can set a daily trigger for a user I will prefer it over a 1-minute trigger so that they don’t get any quota errors or issues.
In my example, when a user requires a trigger to be set for a project at a certain interval, the code will check against the current setting of all existing triggers for the project and update the trigger accordingly.
So for example, if a user sets their first project item to run every six hours, then the trigger will only run every six hours. If they add another item that needs to be run daily then that item will be initiated every fourth time the trigger is run ( 4 x 6 = 24 hours).
However, if they create a third item that requires it to be run every 30 minutes then we need the change the frequency that the trigger is called to 30-minute intervals. This means that the first item needs to be invoked every 12th occasion the 30-minute trigger is run (6 x 2 = 12). Likewise, the daily trigger needs to be invoked every 48th time the trigger is run (24 hrs x 2).
Let’s see how this looks in the code:
The code
clockTrigger.js
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
/**
* Compares the current text item agains the new item and returns the most
* important one.
* @param {Object} obj - the name ranked item: position as a number.
I’d love to hear if you have some examples of when you might need to handle text-based ordering systems and what approach you took. Feel free to add them in the comments below.
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.
Let’s say that you receive a date like “14/01/2022”, “14 January 2022”, “Jan, 14 2022”etc, and you need to convert this date to an ISO string in JavaScript while ensuring that the date that is inputted is for UTC (Universal Time Coordinated) timezone – no matter where you are in the world. It would seem easy right?
Your first reaction might be to simply do something like this:
1
2
3
constdateString="14 Jan 2022";// U.S. peeps <em>"Jan, 14 2022"</em>
Recently, I thought it would be a cool idea to add a date-time stamp to the end of a Google Doc checklist item with Google Apps Script. So I knew when I completed a task.
I often share a project Google Doc with clients and then add my tasks to the document list. With Google’s new check box list item, I wanted to add the date and time that I completed the task when I checked the box.
The bad news is that there is no onEdit() trigger (like in Google Sheets) for the DocumentApp class that would listen for an edit of the document and see a change of the checked box from unchecked to checked and then apply the date-time stamp. 😢
All good, I settled for the next best thing! A menu item.
Recently, I needed a way to ensure that my JavaScript array of objects does not contain any duplicate objects based on an ‘id’ key. While I do enjoy more common approaches to this solution with the use of a simple ‘for’ loop it is always a bit of fun to see how folks have come up with modern solutions.
It wasn’t too long before I stumbled across this ES6 one-liner:
1
let uniqueObjArray=[...new Map(objArray.map((item)=>[item["id"],item])).values()];
But as many code oneliners are, they are a little tricky to understand. So I wanted to spend some time understanding how this worked and thought, you might find this a little interesting too.
Let’s take a look at an example and then work through each bit as we go.
Table of Contents
The Example
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
constmyObjArray=[
{
name:"Eva Devore",
character:"Evandra",
episodes:15,
},
{
name:"Alessia Medina",
character:"Nixie",
episodes:15,
},
{
name:"Kendall Drury",
character:"DM",
episodes:15,
},
{
name:"Thomas Taufan",
character:"Antrius",
episodes:14,
},
{
name:"Alessia Medina",
character:"Nixie",
episodes:15,
},
];
// Creates an array of objects with unique "name" property values.
In the example above, we want to create a unique array of objects based on the ‘name’ property of each object set.
You can see that there is a duplicate property in positions 1 and 4 with key ‘name’ and value, ‘Alessia Medina’ that we need to remove.
You can also change the key to either the ‘character’ or ‘episodes’ property.
When the distinct array of objects is created it will set the last duplicate object as the object value. Why? Because the script will essentially reassign the ‘name’ property each time it loops through the array when creating the new map.
Let’s start breaking down the script so we can see how each one operates.
Code Breakdown
myObjArray.map
1
2
3
4
5
6
7
8
9
10
11
12
13
// Creates a new 2d array with the selected key in position 0 and the object in position 1 of each iteration.
let test_uniqueObjArray_map=myObjArray.map((item)=>{
The first task of this script remaps the array of objects using the JavaScript map() method. This method takes a function, which in our is an arrow function.
Map method arrow functions generally look like this:
In our example above, we have our callback arguments on a new line so we will also need to include curly braces {}.
With the map method, the function will act on each array and return the result to generate a new array of the same length.
For us, our call back condition rebuilds each array to make a sub-array containing the value of each name key in the array as the zeroeth element and the object at the first element.
1
2
3
4
5
...
[item["name"],item];
...
So the first element in the new array will look like this:
Before we continue, let’s take a quick look at a basic Map process on a 2d array:
1
2
3
4
5
6
7
8
9
10
11
12
13
// Creates a key-value map of a 2d array
let valuesObject=newMap([
["key_one","val_one"],
["key_two","val_two"],
["key_three","val_three"],
]);
console.log("valuesObject",valuesObject);
// LOGS: valuesObject Map {
// 'key_one' => 'val_one',
// 'key_two' => 'val_two',
// 'key_three' => 'val_three'
// }
To be frank, I didn’t really understand the Map object too well until I explored this script.
Map object stores key-value pairs similar to an Object. However, the Map maintains the insertion order of the properties. You’ll see Map objects often displayed like this when logged out in the console.
1
'key'=>'value'
Map can be iterated through in a similar way to a 2d array with the zeroeth element as a key and the next element as a value for each property of the map – ['key', 'value'].
Alternatively, we can also generate a Map from a 2d array as we did in the example above – turning each sub-array into a key-value pair.
We are using the data we retrieved from our previous example here to remove some of the clutter from the process. I have added those results at the top of the code block above.
In this example, we simply apply new Map to this array of data. By doing this Map turns into a type of Object with a key-value pair. Now keep in mind that Object keys are the highlander of data types – there can be only one.
What does this mean beyond a bad joke that really shows my age?
It means that each key must be unique. All of our keys are now the names of our users. The new Map constructor process will then iterate through each name and store it and then assign its value. If a key already exists it will overwrite it with this next value with the same key name.
This means that the last duplicate key will always be displayed. Effectively only storing unique values.
Displaying the keys of each property in the Map
We can generate iterators to go through each key or value with the keys() and values() methods respectively.
We will have a look at the keys() method first quickly.
Let’s apply keys() to our test_uniqueObjArray_NewMap Map we generated above.
As you can see this produces an iterator of all the (unique) keys in our data as a Map Iterator. It’s not quite an array of objects, but it allows us to iterate over each key to do something with it.
This is similar to using the Array.from() static method that would look like this:
1
2
3
let test_uniqueObjArray_NewMap_values_asArray=Array.from(
test_uniqueObjArray_NewMap_values
);
Performance
So how does this one-liner stack up against a more traditional for-loop like this?
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
vararray=[
{
name:"Eva Devore",
character:"Evandra",
episodes:15,
},
{
name:"Alessia Medina",
character:"Nixie",
episodes:15,
},
{
name:"Kendall Drury",
character:"DM",
episodes:15,
},
{
name:"Thomas Taufan",
character:"Antrius",
episodes:14,
},
{
name:"Alessia Medina",
character:"Nixie",
episodes:15,
},
];;
varunique=[];
vardistinct=[];
for(leti=0;i<array.length;i++){
if(!unique[array[i].name]){
distinct.push(array[i].name);
unique[array[i].name]=1;
}
}
console.log(distinct)
Surprisingly better than I thought it would, to be honest.
Running a benchmark test with jsbench.me, the one-liner ran only 13.74% slower. Which is pretty good compared to some of the other options I found out there.
Conclusion
So should you be using this oneliner over the for loop? Is an impressive one-liner better than something more clear? To be honest, I am on the fence.
I do like the way this script operates. It is clean and once I got my head around the Map object, it did make a lot of sense. I think if I saw something like this in the wild I could pretty easily identify what it was for and see that it was a nice short solution to a problem.
I don’t think I would use this approach when I need to iterate over objects in the many thousands. Then speed becomes important. But if I need something in my toolkit to solve a problem like this, then I am definitely going to use it.
I have an example of how I used the code to check for duplicate selections of files in Google Drive here:
