List All Users in an Organisation’s Google Workspace Account with Google Apps Script

Note! This tutorial is for Google Workspace for organisations and not the free consumer account, unfortunately. 

While the Google Apps Script docs provide a great example of how to get a list of users in a Domain on a Google Workspace account, it is not in the scope of the documentation to go into the weeds and explain all the ways we can search for all users.

Weeds sound much more like the purview of a goat. A coding goat, perhaps 🐐. Me. I’m talking about me…yeesh!

In this tutorial, we will cover how to access your Google Workspace organisation’s user data, what data you can retrieve and how it looks, who can retrieve it and a couple of ways to display what you need.

This post is intended as a resource reference that compliments the Google Docs on the Admin SDK. Links to the Google documentation are provided throughout the post. It is worth a bookmark if you intend on using the Admin SDK a lot in Google Apps Script.

Use the contents page to navigate to what you need.

Continue reading “List All Users in an Organisation’s Google Workspace Account with Google Apps Script”

Get a list of Google Shared Drives by ID and Name in Google Apps Script

If your organisation is using Google Workspace Business Standard, Business Plus, Enterprise, or one of the other supported plans, you are likely taking advantage of the power of Google’s Shared Drives.

If you have decided to create a Google Apps Script project that needs to get a list of your Shared Drive (or a user’s shared drives in the case of a WebApp), then you might be scratching your head right now wondering how to get this list using the built-in DriveApp class.

Whelp, unfortunately, at the time of writing this article the DriveApp class does not have this functionality. However, it is pretty easy to access in a single line of code using an Advance API.

Here’s what you need to do:

As always, read what you need and skip the rest. 

Continue reading “Get a list of Google Shared Drives by ID and Name in Google Apps Script”

Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice

I don’t often do this*, but I recently got a question on my YouTube tutorial, Update dropdown list in Google Sheets dynamically based on previous dropdown choice: Data Validation, about whether or not this process can be applied to a column range.

The short answer is yes. The long answer is that it is a bit ugly, but it works.

Let’s first clarify the problem.

*obviously not procrastinating before starting another big project 🤣🐐.

Continue reading “Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice”

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 duplicates 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 too locations I found them:

But as many code oneliners are, they are a little tricky to understand. So I wanted to spend some to 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 with 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:

Create 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.

 

Why your COUNT function might not be working in Google Sheets

It can be frustrating when our Google Sheets functions don’t go the way we expect them to.

In this quick tutorial, we cover two common reasons why your COUNT function might not be running the way you expect it to. One beginner issue and one a little more advanced.

Counting Characters

When you count a range of numbers you use the COUNT function. However, if you are counting all the characters in a range you need to use the COUNTA function.

COUNTA counts all the values in a dataset including:

  • All duplicate values (You can use COUNTUNIQUE for just unique values).
  • Cells containing white space. That is, cells with spaces in them but no text.
  • Cells with a text string of zero. This is usually generated as a result of a formula.

To use COUNTA simply select a range or set of ranges.

=COUNTA(Range)

=COUNTA(C2:C25)

Counting the wrong number of values in a range generated from formulas

Quite often we are trying to template our Google Sheets for reuse in other projects or tasks. Take a look at this example:

Example of empty string in a cell Google Sheets
Click to Expand!

Here in Column K we have a formula that joins the first and last name together from columns I & J if it sees text in column J otherwise it will return an empty string. Here is the formula:

=IF(J2 = “”,“”, JOIN(” “,I2:J2))

This formula is then dragged all the way down the column so that it can be used again for another project.

The problem

The problem is that if we try and use COUNTA to get the values in Column K we will return a wrong number.

Why?

Because COUNTA will also count any cell that contains a string of characters with a length of zero (“”). So this will include and count all the cells in our range that contain our formula where we don’t want to display a value.

So how do we fix this?

The solution

Option 1

The most logical option is to try and use another column to reference your count. Unfortunately, this isn’t always possible.

Option 2

If your formula returns an error on an empty cell and you want to hide it, you could use the IFERROR function instead of the IF("","",... option to create an empty cell.

Here you wrap your formula inside the IFERROR function. You then have an optional argument to add what to do if there is an error or leave it blank if you don’t want anything to appear in the cell.

=IFERROR( your formula)

When you run a COUNTA on this column now it will return the correct count.

Option 3

If you can’t do either of these options then you could create a formula that checks the length of each string of data in each cell and if that cell length is greater than zero, count the cell.

First, you can get the length of a cell with the LEN function that takes a cell value. In our example, we will use the first cell item in our full name column K.

=LEN(K2)

We need to be able to use this LEN value on the selected range that we want to count. Here, we can use the ARRAYFORMULA function. This will allow us to put in a range of values in our LEN formula and get the length of each one. The result will be an array containing the length of values for each cell in the range.

=ARRAYFORMULA(LEN(K2:K))

Finally, we can use COUNTIF to count all the values in our new array of lengths that are greater than zero.

=COUNTIF(ARRAYFORMULA(LEN(K2:K)),“>1”)

Conclusion

Check out the video below for more information.

Do you have a clever way of dealing with this problem? I would love to hear about it in the comments below.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi