Flag oversized media eating your quota

Drive storage creeps up quietly. Northwind’s shared drive fills with raw camera exports, uncompressed screen recordings, and the same hero image saved three times at full resolution. By the time someone notices the quota warning, nobody knows which files are the culprits — the heavy ones are scattered across dozens of subfolders.

This script walks a folder tree top to bottom and lists every file over a size threshold into a Sheet, with its full path so you can find it. It is the report you skim before a cleanup: the twenty files worth compressing, sorted out from the thousands that are fine as they are.

What you’ll need

• A root Drive folder to audit — the script descends into every subfolder beneath it. Its ID goes in the config below.
• A blank Google Sheet for the report — its ID goes in the config too.
• Nothing else. The script reports; it never deletes or modifies a file.

The script

// The folder to audit. The script recurses into every subfolder.
const ROOT_FOLDER_ID = '1abcRootFolderId';

// The blank Sheet that will hold the report.
const REPORT_SHEET_ID = '1abcReportId';

// Files at or above this size (in MB) get flagged.
const SIZE_LIMIT_MB = 25;

/**
 * Walks the root folder tree and writes every file at or above the
 * size limit into the report Sheet, with its full folder path.
 */
function flagOversizedMedia() {
  // 1. Convert the MB threshold to bytes once, up front.
  const limit = SIZE_LIMIT_MB * 1024 * 1024;

  // 2. Recurse the whole tree, collecting rows for oversized files.
  const rows = [];
  walk(DriveApp.getFolderById(ROOT_FOLDER_ID), '', rows, limit);

  // 3. Rebuild the report Sheet with a fresh header.
  const sheet = SpreadsheetApp.openById(REPORT_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 4)
    .setValues([['Path', 'Name', 'Size (MB)', 'Link']]);

  // 4. Write the findings, or just log if nothing crossed the threshold.
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, 4).setValues(rows);
    Logger.log('Flagged ' + rows.length + ' oversized file(s).');
  } else {
    Logger.log('No files at or above ' + SIZE_LIMIT_MB + ' MB.');
  }
}

/**
 * Recursive helper. Records oversized files in the current folder,
 * then descends into each subfolder, building up the folder path.
 */
function walk(folder, path, out, limit) {
  // Build the path for this folder, e.g. "Assets/Video/2025".
  const full = path ? `${path}/${folder.getName()}` : folder.getName();

  // Check every file in this folder against the limit.
  const files = folder.getFiles();
  while (files.hasNext()) {
    const f = files.next();
    if (f.getSize() < limit) continue;
    out.push([
      full,
      f.getName(),
      (f.getSize() / 1024 / 1024).toFixed(1),
      f.getUrl(),
    ]);
  }

  // Recurse into each subfolder, passing the path down.
  const subs = folder.getFolders();
  while (subs.hasNext()) walk(subs.next(), full, out, limit);
}

How it works

1. flagOversizedMedia converts SIZE_LIMIT_MB into bytes once, since getSize() always reports bytes.
2. It calls walk on the root folder with an empty starting path. walk does the real work and fills the shared rows array.
3. walk builds a readable folder path, checks every file in the current folder against the byte limit, and records the oversized ones with their path, name, size in MB, and a link.
4. walk then recurses into each subfolder, passing the path down so nested files get a full path like Assets/Video/2025.
5. Back in flagOversizedMedia, the report Sheet is cleared, given a header, and filled with the findings — or, if nothing crossed the threshold, the run just logs that the tree is clean.

Example run

With SIZE_LIMIT_MB set to 25, a run over the shared drive produces a report like this:

Path	Name	Size (MB)	Link
Assets/Video/2025	client-walkthrough.mov	412.6	https://drive.google.com/file/d/…
Assets/Photography/Raw	shoot-DSC0481.cr2	68.3	https://drive.google.com/file/d/…
Brand/Logos	master-lockup.psd	31.9	https://drive.google.com/file/d/…

Three files account for most of the bloat. Sort the Sheet by Size (MB) descending and you have your cleanup list — compress, archive, or delete from the top down.

Trigger it

Run it on a schedule so the report stays fresh without anyone thinking about it:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add a trigger for flagOversizedMedia, time-driven, on a weekly timer.

Or run it by hand the moment a quota warning appears.

Watch out for

• Google Docs, Sheets, and Slides do not count against Drive storage and report a tiny getSize() — so they never appear here. That is correct behaviour, not a bug.
• A deep folder tree can take a while to walk. If the script approaches the six-minute execution limit, raise SIZE_LIMIT_MB or audit one subtree at a time.
• getSize() is the stored size, not the on-screen file size. A shortcut to a file elsewhere reports as small even if its target is huge.
• The report is rebuilt every run, so notes you add will be wiped. Track decisions on a second tab the script never touches.
• This finds large files; it does not find duplicates. A folder full of identical 5 MB images stays under the radar even though it wastes real space.