How to Use Google Workspace Drive API to Generate Bulk Sharing Reports

As a Google Workspace administrator, you are responsible for ensuring that sensitive company data is not improperly shared with external Gmail accounts or public internet users. While the Admin Console provides basic reporting tools, it is incredibly difficult to answer a seemingly simple question: “Can you give me a spreadsheet listing every single file in the Marketing Team’s shared drive that is currently shared with anyone outside the company?” The built-in GUI simply cannot generate this type of granular, bulk report for thousands of files. To accomplish this, you must interact programmatically with the Google Drive API.

The Solution: Google Apps Script

You do not need to build a complex Python application or set up OAuth 2.0 credentials on a local server to use the Drive API. You can use Google Apps Script, a cloud-based JavaScript platform built directly into Google Workspace. It handles authentication automatically because it runs under your admin account.

Step 1: Set Up the Script Environment

1. Open Google Drive and create a new, blank Google Sheet (e.g., “External Sharing Report”).
2. In the top menu, click Extensions > Apps Script.
3. A new browser tab will open with the code editor. Rename the project to “Drive Security Audit”.

Step 2: Enable the Advanced Drive Service

By default, Apps Script uses basic, limited classes (like DriveApp). For detailed permissions reporting, you need the Advanced Drive API.

1. On the left side of the Apps Script editor, click the + icon next to Services.
2. Scroll down and select Drive API (usually version v3).
3. Click Add.

Step 3: Write the API Script

Delete the default myFunction() code and paste the following script. This script iterates through all files in a specific folder (or Shared Drive) and logs the permissions to your spreadsheet.

function generateSharingReport() {
  // Replace with the ID of the folder or Shared Drive you want to audit
  const FOLDER_ID = 'YOUR_FOLDER_ID_HERE'; 
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();
  sheet.appendRow(['File Name', 'File URL', 'Shared Email', 'Role', 'Permission Type']);
  
  // Use DriveApp to get the folder contents
  const folder = DriveApp.getFolderById(FOLDER_ID);
  const files = folder.getFiles();
  
  while (files.hasNext()) {
    const file = files.next();
    const fileId = file.getId();
    
    try {
      // Use the Advanced Drive API to get detailed permissions
      // Setting supportsAllDrives allows it to scan Shared Drives
      const permissions = Drive.Permissions.list(fileId, {
        supportsAllDrives: true
      }).permissions;
      
      if (permissions && permissions.length > 0) {
        for (let i = 0; i < permissions.length; i++) {
          const perm = permissions[i];
          
          // Filter out internal users (Optional: adjust to your domain)
          // If you want ALL permissions, remove this if statement
          if (perm.emailAddress && !perm.emailAddress.endsWith('@yourcompany.com')) {
            sheet.appendRow([
              file.getName(),
              file.getUrl(),
              perm.emailAddress,
              perm.role,       // e.g., reader, writer, commenter
              perm.type        // e.g., user, group, domain, anyone
            ]);
          }
          
          // Check for public "Link Sharing"
          if (perm.type === 'anyone') {
             sheet.appendRow([
              file.getName(),
              file.getUrl(),
              'PUBLIC LINK',
              perm.role,
              perm.type
            ]);
          }
        }
      }
    } catch (e) {
      Logger.log(`Error reading file ${file.getName()}: ${e.message}`);
    }
  }
}

Step 4: Execute the Code

1. Replace YOUR_FOLDER_ID_HERE with the actual ID of the folder you want to scan (found in the URL when viewing the folder in Google Drive).
2. Replace @yourcompany.com with your actual domain to ensure internal users are filtered out of the report.
3. Click the Save icon (floppy disk).
4. Click Run at the top of the screen.
5. The first time you run this, Google will require you to authorize the script. Follow the prompts (you may need to click “Advanced” and “Go to Drive Security Audit” to bypass the unverified app warning, since you wrote the code yourself).

Step 5: Review the Audit Report

Once the script finishes executing, return to the Google Sheet you created in Step 1. You will find a neatly organized, row-by-row audit log detailing exactly which external email addresses have access to which files, and whether any files have been accidentally set to “Anyone with the link can view.” This data is critical for compliance and security remediation.