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.

UNIQUE demo Google Sheets

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:

UNIQUE Array generated from a 1d array in GAS

…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:

UNIQUE 2-Array of multiple columns with result set in GAS
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…

Continue reading “Creating Unique Ranges from 2D Arrays in Google Apps Script”

Find the item with the highest order of importance and store it with JavaScript

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. 

 

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:

  1. Royal flush
  2. Straight flush
  3. Four of a kind
  4. Full house
  5. Flush
  6. Straight
  7. Three of a kind
  8. Two pairs
  9. Pair
  10. 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

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:

  1. The array or list to reference
  2. The stored item, in our case, bestHand.
  3. 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.

bestHand = setMostImportant_Array(poker, bestHand, "Pair");

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.

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:

  1. tryzatium << currently lowest value.
  2. ocoumbre
  3. malcatite
  4. enzome
  5. parl
  6. 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

The results of which should appear like this:

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()

The moveItem() function allows us to change the order of priority of our list of commodities.

The function takes 3 arguments as parameters:

  1. list – the array of ordered items. In the example, this is our commoditiyByVal array.
  2. item – This is the item in the list that needs to change position.
  3. 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)

  1. start index – our new index for the position of the item.
  2. delete count – the number of items to delete. For us, this is zero. We only want to add an item.
  3. 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:
    1. start index – the current index of our selected item.
    2. 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:

  1. “daily”,
  2. “6 hours”,
  3. “1 hour”,
  4. “30 minutes”,
  5. “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

Conclusion

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.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Create and Publish a Google Workspace Add-on with Apps Script Course

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.

 

~Yagi

 

Create a ISO String from date text input intended for UTC date in JavaScript

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:

Now unless you are sitting smack-dab in a UTC timezone you might be in for a bit of a surprise.

Right now, my timezone is UTC+11 hours. This means that my result of the code in the example above will report the previous day at 1pm UTC.

That’s not what I am looking for all. I need to set this date to precisely midnight of 14 Jan 2022 UTC time.

The ISO String we need is this: 2022-01-14T00:00:00.000Z

The Solution

Continue reading “Create a ISO String from date text input intended for UTC date in JavaScript”

Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script

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.

Take a quick look at the results.

Continue reading “Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script”

Get a Unique List of Objects in an Array of Object in JavaScript

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:

Pretty neat, huh?

Here are two locations I found them:

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.

 

The Example

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

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:

As an ordinary function, it would 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.

So the first element in the new array will look like this:

new Map

A quick side example

Before we continue, let’s take a quick look at a basic Map process on a 2d array:

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.

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.

Back to our main example…

new Map of our example

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.

The same is true for the values() method.

Displaying the values of each property in the Map

Here we want to get an iterator of our values so that we can recreate an array of objects again.

Using the values() iterator method we now have our Map values ready to go.

Using the spread syntax to create our array of object

Now that we have an iterator of our unique values we can now place them in our spread syntax – “...“.

When you apply the spread syntax on an array, it will add each item of an iterable to the array. Take a look at what it does to our Map values.

This is similar to using the Array.from() static method that would look like this:

Performance

So how does this one-liner stack up against a more traditional for-loop like this?

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:

https://yagisanatode.com/2021/06/27/creates-a-google-workspace-add-on-file-picker-card-with-cardservice-that-opens-a-google-picker-in-an-overlay-window-google-apps-script/

 

What do you think? Is it too abstract or is it elegant?

Did this tutorial help you understand the JavaScript one-liner better? Do you think you would apply it in your own projects?

Please let me know your thoughts in the comments below. I really enjoy hearing how things are used in the wild.