Sometimes your Google Workspace users may install and give access to third-party applications without your consent, or some sort of shadow IT.

There are a number of tools in the market that can help identify third-party applications your users are using with a company Google Workspace account.

If you’re an enterprise, you might want to try such tools as they offer a bunch of other features.

However, for small businesses and start-ups, these tools can be quite expensive. Therefore, I will be sharing with you how you can audit your Google Workspace applications without spending a dollar. How cool is that?

In 2017, Google Workspace faced a phishing attack and at that point in time I was working for a client, so I had to do an OAuth token audit for all users. That’s when I created this script that I’m sharing with you.

Watch the video tutorial above to learn how to use OAuth token Audit Script or you can continue reading this post for instructions.

How to Install the script

In the following steps, I will be showing you how to correctly setup this script step by step.

1. Copy script below.

OAuth Token Audit Script

‍

---------------------------------------------------
function OAuthCheck() {
 

 var ss = SpreadsheetApp.getActive()
 var sheet = ss.getSheetByName("OAuth")
 var values = sheet.getDataRange().getValues()
 var fileArray = [["User's Email Id", "Application Name", "Client Id","Is this Native App", "Is this Anonymous", "Scopes Granted"]]
 
 for(i=1; i <values.length; i++)
 {
   
   var userKey = values[i][0]
   var clientid = "my_customer"
   
 
       try {
  var Token = AdminDirectory.Tokens.list(userKey).items
  for (j=0; j <Token.length; j++){
    var displayText = Token[j].displayText
  var userName = Token[j].userKey
  var clientId = Token[j].clientId
  var anonymous = Token[j].anonymous
  var native = Token[j].nativeApp
  var scopes = Token[j].scopes
 
    fileArray.push([userKey, displayText,clientId,native,anonymous,scopes])
       
                   
  }}
   catch (e){
     Logger.log(e.message)}

 Logger.log(fileArray)
 
 }
var range = sheet.getRange(1,2,fileArray.length,6).setValues(fileArray)
}

‍

-----------------------------------------------------

‍

What this script does is; it goes through all your users one by one. Then it fetches a few attributes of each user including the name of applications they have installed.

For instance, it will fetch user’s email id, application name, client id and whether or not an application is native, anonymous and which scopes have been granted.

So, you will want to copy the script above and follow next step.

2. Open Script editor

Create a new Google sheet then go to ‘Tools‘. Click on ‘Script editor‘ option

3. Create a new script file

From script editor screen, you will be creating a new script file. Click on ‘File‘ option then ‘New‘ then click on ‘Script file‘.

Then give your script file a name, using the form that pops up.

4. Paste script in the new file

So now you paste the script you copied in step 1 into this file. Make sure you save the file, then continue to the next step.

5. Enable Admin Directory API

The code you just pasted requires access to admin directory. So you will need to turn on this option by following these simple steps.

Click ‘Resources‘ and then on ‘Advanced Google services‘.

From Advanced Google Services screen, toggle on/off button to turn on ‘Admin Directory API‘.

Once you turn on admin directory API, you will also want to enable Google API Console. To do that, just click on ‘Google API Console‘ text link.

Click on ‘Enable APIS and Services’ option

Using the search bar, search for ‘admin skd’ and then click on ‘Admin SDK‘ from search results returned.

Now you can enable Admin SDK by simply clicking on ‘Enable’ button on the page.

Now that you’ve enabled Google API Console, you can now go back to Advanced Google Services and click ‘OK‘ button to continue.

6. Rename Your Google Sheet tab

Now you’re ready to run the script. But before you run the script make sure you rename your Google sheet to ‘OAuth’.

This is because the script will be looking for a sheet named ‘OAuth’, so make sure the sheet’s name is correctly spelt as it should.

Now that you have your sheet correctly named, it is time to run the script.

Click on the play icon (see screenshot above) to launch the script.

The first time you run the script,  Google will ask you for permissions.

After you’ve allowed permissions to the script, you want to click on the play button again to execute the script once again.

7. Using the script

After running the script, your OAuth sheet will now look like the screenshot below.

All you have to do now, is provide the email address of a user or users you want to check which applications they’ve installed. Read on to learn more.

8. Enter user email address

In the first column of the sheet, type an email id of the user you want to check which applications they have installed.

In my case, I’m only entering one user’s email ID, but you can of course enter hundreds of them (see instructions below for limitations though).

9. Run Script

After adding users email id, go back to script page, click on the play button to run the script.

If the script ran successfully, you should see the OAuth sheet populated with data about the applications they’ve installed.

In case you see “Execution Limit Exceeded” error that means you are putting lots of users in column 1 and script is not able to process them in allowed time, so you should reduce the number of users

10. Guidelines on how many users to run at one go

1. If you are using Google Workspace Basic Edition – upto 1500

2. If you are using Google Workspace Business or Enterprise and have been approved for Appmaker’s EAP, then you get 30 minutes of Google Apps script execution time, and you can easily add upto 5000 user emails at once.

In case you have a lot of users over 1500 (Google Workspace Basic Edition) or over 5000 (Google Workspace Business or Enterprise), here is what you have to do:

Run the users in batches, after the first batch of let’s say, 1500 user id’s copy the output data to a different sheet tab, then clear OAuth sheet, then you can add another batch of 1500 users’ email IDs again.

This way, you avoid seeing the execution limit error.

In Conclusion

One of the ways you can now use the data is by using the unique formula to quickly figure out the unique application and see if there’s an application used widely by most of the users.

For my case when the phishing attack happened in 2017,  we were able to figure out the client ID of the application, then we did a match to see if any user had granted access to that client ID. And then we ran another script which deleted the token for the applications that users had mistakenly given access.

I hope this helpful, in case you have questions and comments, feel free to leave them in the comment section below and I’ll be glad to help.