Creating a Google Drive report in Google Sheets: Making Google Workspace Enterprise solutions with Google Apps Script

Think 10x — supercharging your Google Apps Script solutions by directly calling Google Workspace Enterprise APIs.

I’m sure many are familiar with Google’s productivity suite, Google Workspace. Fewer may be familiar with Google Apps Script, a JavaScript syntax based development platform which tightly integrates with Google Workspace applications like Google Sheets, Gmail and Drive.

Apps Script has been around since 2009, and whilst originally it was only available to Google customers, it was quickly made freely available to consumer @gmail.com accounts. This, combined with a cloud based script editor and pre-authenticated access to Google services, has made it a popular platform for novice and low coders.

Google’s Accessing Google Sheets, Maps, and Gmail in 4 lines of code! — Codelab is a great example of what can be achieved with Google Apps Script. With these four lines of code anyone with a Google account can interact with Google service with zero setup, no requirement to setup a dev environment, no requirement to install client libraries, no requirement to create a Google Cloud Developer Console project.

/** @OnlyCurrentDoc */
function sendMap() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var address = sheet.getRange('A1').getValue();
  var map = Maps.newStaticMap().addMarker(address);
  GmailApp.sendEmail('YOUR_EMAIL_ADDR', 'Map', 'See below.', {attachments:[map]});
}

This snippet shows how Apps Script built-in services like SpreadsheetApp (Google Sheets), Maps (Google Maps) and GmailApp, can be used to utilise these respective products. There are currently over 30 of these built-in services available to Apps Script users, including all of the major Google Workspace products.

A question I often get asked is whether Google Apps Script is an enterprise ready solution. My answer is ‘yes’ … with caveats. Yes you can do a lot with Google Apps Script ‘out-of-the-box’ and even employees with low/no coding abilities can quickly create solutions that super charge workflows making huge efficiency and productivity gains. The inclusion of built-in services like SpreadsheetApp, GmailAppand for Google Drive, DriveApp, lower the bar for people getting started with Apps Script, but mean your code isn’t optimised for enterprise level solutions.

For example, recently I’ve had a couple of customer projects where I needed to generate reports on the contents and folder structure in Google Drive. A common approach to do this is recursively call DriveAppusing the file and folder iterator methods to get data of the users file structure. Here is a gist for a DriveAppfile/folder iterator approach.

This is a good solution when there aren’t many folders, but can be slow for larger file/folder structures. Running this script on my personal My Drive took almost 4 minutes to index 10,000 files/folders. The issue with speed is related to the number of calls to DriveApp. To highlight this consider the following simplified file/folder structure which includes the required calls to DriveApp:

In the diagram above note the number of calls to DriveApp which return empty responses. For example, because Folder A only contains subfolders, when DriveApp.getFiles() is called on the folder it has no data to return.

Instead of using theDriveApp built-in service an alternative approach is directly call the Google Drive API. The benefit of this is we have more control over what data is queried and returned. For example, instead of individually getting each file/folder branch we can use the following approach:

1. Get all the folder IDs which also includes the parent folder ID
2. Filter the folder IDs to the root parent folder ID
3. Get all the files where the parent folder ID is in the filtered list

This is more efficient because the Drive API can return up to 1,000 items in a single call. Once we get all the files we need, we can then reconstruct the structure using the data in the file response object. This means if you have a filetree with less than 1,000 folders and 1,000 files you can potentially get all the data in two API calls as illustrated below:

To see what this looks like here is an example Apps Script gist for calling the Drive API. When testing with My Drive it can get 10,000 files/folders in a Google Sheet in 40 seconds! [If you would like to try both methods on your own Google My Drive you can make a copy of this Google Sheet].

There are a couple of considerations to keep in mind with this approach. This version of the script is limited to reporting on My Drive, but with minor modification you can implement shared drive support, or by setting up a service account with delegated admin view other My Drives and Shared Drives.

This approach is also more efficient when you are indexing high volumes of files/folders close or at the root of the drive. I would anticipate as you move further away from the root there is a crossover point where you go from: the Drive API for files and folders; to a mixed DriveAppfor folders and Drive API for files; and DriveAppfor small numbers of folders and files.

So in summary there is a lot you can do ‘out-of-the-box’ very easily with Google Apps Script, but there are times where knowledge of the underlying APIs can greatly improve the solutions you develop … a little engineering knowledge can greatly improve your productivity.

CTS is the largest dedicated Google Cloud practice in Europe and one of the world’s leading Google Cloud experts, winning 2020 Google Partner of the Year Awards for both Workspace and GCP.

We offer a unique full stack Google Cloud solution for businesses, encompassing cloud migration and infrastructure modernisation. Our data practice focuses on analysis and visualisation, providing industry specific solutions for; Retail, Financial Services, Media and Entertainment.

We’re building talented teams ready to change the world using Google technologies. So if you’re passionate, curious and keen to get stuck in — take a look at our Careers Page and join us for the ride!