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”

Get the Creator’s Email of a Shared Drive with Google Apps Script

Google Drive’s Shared Drives are designed a little differently than regular Google Drive folders. Each Shared Drive file is collectively owned by the group working on the file or, more directly, the Google Workspace Domain account that manages the file.

While you can search for shared drives and get some level of metadata (e.g. who has what permissions) from them as you can see in this tutorial:

Get a list of Google Shared Drives by ID and Name in Google Apps Script [updated 10 Feb 2022]

… you won’t see details of who created the shared drive with DriveApp or the Drive API advanced service in Google Apps Script.

After a bit of experimentation, I built a solution that uses:

  1. The Drive Activity API to extract the creator’s numerical ID.
  2. The Admin SDK to search for the user by ID and retrieve their email.

Check out the video tutorial below, then grab a copy of the script along with links and a brief discussion of some of the more interesting and technical bits of the code.

The Video

The Code

The main function here is the getSharedDriveCreator() function. If you are just copying and pasting then this will be all you need to add to your project.

All you need to do is add the ID of your shared drive as the first parameter of the function:


The runsies() function is just an example function showing how everything works and is not needed in your own project, but may be useful for testing.

You will need to add two Google Apps Script advanced services to your script:

I haven’t changed the reference in either of these services here, but if you do, you will need to update the script.

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

Code Walkthrough of the Shared Drive Creator function

Get the activity records of the shared drive

The first task in this script is to invoke the Google Drive Activity API DriveActivity class. You can use this API to:

  • See what has changed on a file or folder.
  • See who made those changes.
  • Provide alerts when changes or comments have been made.

We can create an activity query with the API using  DriveActivity.Activity.query(). The query will return an object containing a historical log of all the interactions on the selected file or folder.

The query takes a single object as a parameter. We are specifying a particular location to run our query so we need to add the “itemName” property.

Strangely the value of the “itemName” needs to contain “items/” and then the file or folder id.

Next, we can filter the returned object down to contain information about the creation of the file or folder. This is achieved with the detail.action_detail_case: field. In these filter fields, the colon (:) is an operator representing “has”. Here we can add “CREATE” to reduce our search to only show information about the creation of the file or folder. Basically, this says:

“In the folder, show me the details related to the creation action.”

This will return an object that looks a little like this:

Getting the Shared Drive creator’s unique id.

If you look down at the actors’ property on line 79 of the object above, you should see only one user, the creator of the shared drive. This will be under “user” > “knowUser” > “personName”.

As you can see, the creator’s email is not provided. We will need to find this in a moment, but first, we need to extract the user’s id from the personName property.

const personName = activity.activities[0].actors[0].user.knownUser.personName

We won’t need the prepended “person/” in the personName value so let’s create a JavaScript substring of just the numerical id.

const accountId = personName.substring(7)

This will result in something like this:


Get the user’s details from the Google Admin Directory SDK

With the “users” unique numerical ID we can now use the Google Admin Directory SDK to access some details about the user.

At its most basic level, we can get a user’s details with:


Non-admin privileged users can still make use of the Admin Directory SDK. However, they will be limited to just the information that is publically available. For us, this is no problem, we only want the publically available primary email of our creator.

To ensure that users without admin privileges can run the code without error we need to add an object field to the second parameter of the get request changing the view type to domain public.

"viewType": "domain_public"

Next, we will also set the projection to “basic” to limit the amount of object data returned from the query.

"projection": "basic"

Weirdly, when a user with admin privileges runs code with a “domain public” view type it will result in an error. As such, we need to provide a “Try Catch” statement to handle the times admins run the code.

So, within the catch statement, we can add just the account id to the get method for the admins to access the data.

The resulting object will look a little like this:

Retrieving the email address

Finally, we can collect the primary email of the user with the object path:


Then we return that email back to be used by the function it was instantiated from.


While on the whole, this might be a little bit more of a convoluted process that first expected, the entire processing speed was relatively short.

Try and give this a run-through on your own and then try and get the creator emails of all the shared drives that you have access to by connecting it to the tutorial below:

I really like to hear how folk are using and adapting the code that I provide. It provides inspiration for me and the people reading the article. Please consider sharing your use cases in the comments below and subscribe (Top-right sidebar).

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.


Google Apps Script: Store a Unique User Key from a User Accessing your WebApp [updated Dec 2021]

Google Apps Script: WebApp [updated Dec 2021 – With thanks to Greg S]

In this tutorial, we will cover how you can get a unique temporary access key from a user accessing your WebApp that lasts for 30 days.

Temporary access keys allow you to track users as they use your WebApp over time while still providing anonymity to the user by providing only an access key to that user. Rather than, say, use their name or email address.

Why is this important? Well, you might want to limit the number of times a user submits a form on your WebApp. If you can get a user’s access key unique to them then you can store the number of attempts by the user and check it before the data is submitted.

For example, in a previous post, we created a chain story that we might want to limit the number of times our users contribute to our story to once a day.

NOTE! This tutorial is pretty much standalone. However, it will require some basic knowledge of Google Apps Script WebApp and HTML. Don’t worry if some basic setup parts are not covered in this tutorial, I’ll link to how to do these bits if you need some more instruction.

Let’s take a look at what we are going to make:

Continue reading “Google Apps Script: Store a Unique User Key from a User Accessing your WebApp [updated Dec 2021]”