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:

getSharedDriveCreator("0APU21xY4hn2STek9PVA")

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:

102444833385071164881

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:

AdminDirectory.Users.get(accountId)

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:

user.primaryEmail

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

Conclusion

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:

https://yagisanatode.com/2021/07/26/get-a-list-of-google-shared-drives-by-id-and-name-in-google-apps-script/

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?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I 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

Count the Occurrence of a Selection of a Cell in Google Sheets with Apps Script

A mate reached out to me last week asking if there was a way to monitor the times any article in a Google Sheet list goes through an editing stage before being published. From there, he wanted to report the number of edits on the article in an adjacent cell.

Take a look at the example:

As you can see in the example, whenever “NEEDS EDIT” is selected from the dropdown menu, then the counter column for the selected row item is updated automatically.

In this tutorial, we will cover how to create this for a single Google Sheet tab or for multiple selected tabs. I’ll also wrap up with a script to calculate just the aggregate count of changes to all the rows in the selected range and report it in a single cell.

Check out the video tutorial that covers the basics and grab the Starter Sheet to play along:

The Video

Scheduled release 6 Dec 2022 9am GMT+10

Generally better to view it from YouTube.

Starter Sheet

Here is a link to the starter sheet copy the sheet and head to Extensions > Apps Script in the menu.

Starter Sheet

The onEdit(e) Simple Trigger

Everything starts with the onEdit(e) simple trigger. This is a special trigger in Google Apps Script called a Simple Trigger that will run every time you edit your Google Sheet. If Apps Script sees the onEdit() function in your project it runs automatically.

Every time onEdit() is executed, it will retrieve an event parameter that is commonly referenced as "e" (onEdit(e)). The event object can return things like:

  • The current value
  • The previous value
  • The edited range
  • The source spreadsheet
  • The trigger id
  • The authorisation mode

We will be using a few of these in our code below.

Unless you have a very short coding task, it is generally good practice to reference another function that will be executed when onEdit() is run rather than coding directing within the onEdit() function.

Take a look at the three examples I added for my test project for this tutorial:

You can see that each one of these functions will be run one after the other when a cell is edited and calls onEdit().

Count Every Time The Cell Changes

In this example, we add a count to our adjacent counter cell each time the cell is edited.

Note that we take the event (e) as our parameter for our countEveryTimeCellIsChanged(e) function.

Set the Variables

First, let’s assign all our variables at the top of our function to make it easier for us to change should we need to down the track.

We have 3 variables that we need to include here. They won’t change so we will make them a constant variable:

  1. tgtSheet – This is the target sheet tab name. For our example, this is assigned to “Single List”.
  2. tgtCol – This is the target column or the column that has the values that will need to be monitored for changes. In our example, this is the status column, column 3.
  3. counterCol – This is the column where we display our change count. For us, this is column 4, but it could be any selected column in the sheet.
Numbers correspond to the points above.

You can stop here if you want to copy and paste the script into your own project and update the variables for your own purposes. Or continue to learn how it all works.

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

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I 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.

Get the Correct Sheet Tab and Column

Lines 33-38

We don’t want to end up counting the wrong column or updating a completely different sheet tab by mistake. This means we need a way to check if we are in the right spot.

The first thing we need to do is check the range we are currently editing. This can be done by grabbing the range object in our event parameter. Line 33

We now have access to the Google Apps Script range class. From this class, we can get the sheet class and also the range column numberLine 34-35

Using the JavaScript ‘if’ statement we first check that the sheets name (sheet.getName()) is equal to our assigned target sheet name and if it is, then check that the selected column is equal to the target column. Line 38

Update the Counter Cell

Lines 40 – 48

We need to update the adjacent counter cell in the same row that was changed when edited.

We can use the range class again with the getRow() method. This will give us a row number. Line 40

const row = range.getRow();

Next, using the sheet variable we created earlier we can call the getRange() method to get the counter cell. For a single cell, this method can take two variables the row and the column number. Here, we add in these two variables we collected earlier.

const counterCellRange = sheet.getRange(row, counterCol);

Now we need to add one to the current count in our counter cell. This means that we first need to collect the current value in the cell, which we do with the getValue() method.

We need a way to check if the value in the counter cell is empty. If it is then we want to give it a value of 1 otherwise we want to add 1 to the current value.

const counterCellVal = counterCellRange.getValue();

This can be achieved with a ternary operation. Think of a JavaScript ternary operator as a single line if statement in a way.

The ternary operator below states that if the value is less than zero (empty) then add one otherwise add one to the counter cell value.

const updateCounterCell = (counterCellVal < 0)? 1 : counterCellVal + 1;

Finally, we update the counter cell:

counterCellRange.setValue(updateCounterCell);

Done!

 

The following scripts will modify this code. We will touch on the changes only in the code discussion of each script.

Count Every Time The Cell Changes To the Selected Value

In this scenario, we only want to count when a cell changes to a specific value. For our example, this value is  “NEEDS EDIT”.

First off, we need to store a target value that we want to monitor for and if that value appears add one to our counter cell. Here on line 29, we set our target value to “NEEDS EDIT”. 

const tgtVal = "NEEDS EDIT"

You can change this to the value that you need for your project.

Next, we need to check if the cell that was edited was edited to the new target value before we update the counter cell.

To do this we first get the value of the selected range (Line 42):

const cellVal = range.getValue();

Then we create an if statement checking that the cell value matches the target value on line 43 and closes on line 54.
if(cellVal === tgtVal){

Apply The Script to Multiple Selected Google Sheets

What if we have different editorial teams or genres handled by different editors? We might want to put the data for each team in a different sheet tab to make it easy for monitoring. Here we will need to check all the selected sheets for any changes.

In our example now we want the “Team 1” and “Team 2”  sheet tabs. Both tabs are identical in format.

Monitoring multiple sheet tabs for changes to a cell value onEdit in Google Sheets with Apps Script

Let’s see how our code works now:

Two changes need to be made to the script here.

On line 25, we need to change the tgtSheet variable to tgtSheets and include an array of all the Google Sheet tabs we need to monitor with our Apps Script code.

const tgtSheets = ["Team 1", "Team 2"]; // The sheet names the data is on.

Then on line 38, we need to replace the first part of the if statement with a way that will look through our array of sheets and check if the current sheet contains the selected name.

This can be achieved with the JavaScript includes function. This function appends a selected array (In our case, the target sheets array) and takes a target value as an argument. Our target value is the select sheet that this being edited.

... tgtSheets.includes(sheet.getName()) ...

Get the Aggregate Count of the Changes

This final script checks all the cells in the target column in a single Google Sheet tab and checks if they have been changed to “NEEDS EDIT”. Then records the aggregate count of all the cells in a single counter cell in a separate sheet tab.

In our example, we will monitor the “Single  List” sheet tab for our desired change and record the total count of changes for all rows in the “Aggregate” sheet tab.

Calculate the aggregate of all changes to cells to the target value in Google Sheets With Apps Script

Check out the code:

First, we have replaced the counterCol variable with the counterCell variable and set that to cell A2 of the ‘Aggregate’ sheet tab.

const counterCell = 'Aggregate!A2'; // The column to update the counter.

Next, we don’t need to get the row number this time so we have deleted this variable.

Lastly, on line 44, we use the source property of the event (e) object to grab the current spreadsheet. This allows us to use the getRange() method to select our counter cell.

Conclusion

There should be enough samples there for you to work out your own combinations. If you want to test your new skills out, try to:

  1. Create an aggregate count of multiple sheet tabs.
  2. Move the counter column to another location in the sheet.
  3. Check for different target values in different sheets with different locations.

If you get stuck check out some of the other popular tutorials on using onEdit():

It’s always nice to hear how people use these scripts in their own projects and it can be inspiring to share your use cases with others. Feel free to share your experience and examples in the comments below.

Finally a big thanks to Mike Kanert for the inspiration for this post. Editor, writer, artist, actor, educator, polymath and all-around nice fella, you can find Mike on Instagram @Unremarkable_Us for his latest comic series made by parents for parents:

Unremarkable Us

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

~Yagi