Use Apps Script to pull all Google Shared Drives and permissions into a Google Sheet

If you're a Google Workspace Super Admin, you've probably been thinking of ways to inventory and keep a handle on Google Shared Drives. They are a really great addition to the Workspace offering, but there are still some gaps in reporting & oversight that some people have wanted. This solution uses Google Apps Script and the Drive API to pull information about all the Shared Drives in a domain into a Google Sheet. Then, it uses the Drive API again to loop through all the drives and get the top-level permissions.

Note, this does not traverse down into subfolders' permissions inside every shared drive. While this is entirely possible to script, the exponential size and scope of the data collected is too large to be effectively stored in a single Google Sheet (in my opinion). This solution is meant to solve two fairly basic questions:

1. Who owns and has access to the Shared Drives in my domain?
2. Can I perform an inventory on a schedule, so I can see how things change over time?

Let's get into it.

Components

• Google Sheet
• Google Apps Script
• Google Workspace Domain Super Admin (or delegated access for "all" drive content in the domain)
• This code I've shared on github

Process

1. Sign into Google as a super admin / domain admin and create a new Google Sheet with 2 tabs. Feel free to keep them named "Sheet1" and "Sheet2" but you might want to change them to make more sense (maybe Drives and Permissions)
2. Create a new Apps Script project via script.google.com
3. Add the "Drive" service to the project (using the + symbol on the left side of your Apps Script editor)
4. Copy the code from the shared repo into the Code.gs file
5. Update the variables at the top of the code to point to the google sheet you created in Step 1, and the tab names (if you changed them from Sheet 1 & 2)...and the email address you want a notification to be sent to when it's finished.
6. Select the "job_set_sheet_headers" item in the Run menu at the top of the editor, and click "Run"
7. The first time you run anything, you'll be prompted to allow permissions for the script to run. Because this is needing superadmin permissions, you'll get a list of authorization types for everything in Google Drive. Remember, this is "your" script running as "your superadmin account" which means you shouldn't share this script with anyone else you don't trust.
8. After the permissions authorization step, the script will run, and put the header row into the target sheet. Open the sheet and check the top row of each tab, and you should see the column names. This means you've set things up correctly and are now ready to get the shared drives and permissions info.
9. Select the "job_get_shared_drives_list" item in the Run menu at the top of the editor, and click "Run"
10. This will take a little while depending on the number of drives in your domain. For reference, I was able to get 8200 shared drives in 76 seconds.
11. When it's finished, look at the google sheet again, and now you should see a lot of info in the first tab.
12. Select the "job_get_permissions_for_drives" item in the Run menu at the top of the editor, and click "Run"
13. This will go out and get the permissions for the shared drives listed in tab 1, and put the results in tab 2 (then delete the row in tab 1)
14. Let this run and watch for completion and the results to be written into the sheet.
15. When it's complete, you can now set up a trigger to run "job_get_permissions_for_drives" automatically.
16. Click the triggers item on the left side, and set up a new trigger to run "job_get_permissions_for_drives" every 5 minutes
17. Wait up to 10 minutes for the script to run automatically, so you can verify the results in the target sheet.
18. It will send you an email when it's complete, and so you can disable the trigger (if you'd like, so it doesn't just run all the time with nothing to do).
19. You can set up a regular schedule for this (say, weekly) by setting up triggers for the first "job_get_shared_drives_list" function every week, and then the permissions job as well. Just keep in mind the size of your domain, and if the sheet will get too large. In that case, you could consider offloading the data into BigQuery or something else (i have other tutorials and code that does that kind of thing if you want to go that route).
20. For bonus points you can now connect a Google Data Studio dashboard to the sheet, and visualize the data easily.

That's pretty much it. Feel free to take the code and improve it (or submit pull requests for it).

Hopefully this is helpful to some admins out there.