What trigger should I use for Audit Google Drive for Files Larger than 50MB and Export to Sheet?

This script is designed for: On Demand. Configure it under Apps Script > Triggers (clock icon).

Google Drive AutomationDifficulty: Intermediate⚡ On Demand

Audit Google Drive for Files Larger than 50MB and Export to Sheet

Q: What OAuth permissions does Audit Google Drive for Files Larger than 50MB and Export to Sheet require?

This script requests the following authorized scopes: https://www.googleapis.com/auth/drive.readonly, https://www.googleapis.com/auth/spreadsheets.

Scans Google Drive storage, identifies massive videos, zip archives, and bloated presentations, and logs file name, owner, and size into a spreadsheet for storage cleanup.

Required OAuth Scopes: https://www.googleapis.com/auth/drive.readonly • https://www.googleapis.com/auth/spreadsheets

Code.gs (Apps Script V8 Runtime)

function auditLargeDriveFiles() {
  const minBytes = 50 * 1024 * 1024; // 50 MB
  const files = DriveApp.searchFiles('trashed = false');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('StorageAudit');
  
  const rows = [['File Name', 'Size (MB)', 'Owner', 'URL']];
  
  while (files.hasNext()) {
    const file = files.next();
    const size = file.getSize();
    
    if (size >= minBytes) {
      const sizeMB = (size / (1024 * 1024)).toFixed(2);
      const owner = file.getOwner() ? file.getOwner().getEmail() : 'Shared Drive';
      rows.push([file.getName(), sizeMB, owner, file.getUrl()]);
    }
  }
  
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, 4).setValues(rows);
}

Line-by-Line Code Explanation

Line 2: Configures minimum file size threshold (50MB).

Line 3: Searches non-trashed files across Drive.

Line 11-16: Formats sizes into megabytes and records ownership email.

Line 19-20: Writes audit report into spreadsheet.

Step-by-Step Setup & Deployment

Create a sheet tab named "StorageAudit".
Run the script to identify files consuming your pooled Google Workspace quota.

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More Google Drive Automation Snippets

Google Drive