Build a Drive storage audit dashboard

Drive grows in the dark. A folder of raw video here, a years-old archive there, and one day the account is near full with no obvious culprit. Northwind needs a clear answer to “what is actually taking up the space” before deciding what to delete, move, or archive.

This script walks a folder tree and writes one row per folder — its file count, total size in megabytes, and owner — into an audit sheet. Sort that sheet by size and the heavy folders rise straight to the top, turning a vague worry into a short, actionable list.

What you’ll need

• A root Drive folder to audit — the script recurses into every subfolder.
• A Google Sheet to hold the dashboard. The script clears the first tab and rewrites it on every run, so use a dedicated sheet.
• Edit access to both the folder tree and the dashboard sheet.

The script

// Bytes in a megabyte, for converting raw file sizes.
const BYTES_PER_MB = 1024 * 1024;

/**
 * Audits a Drive folder tree and writes a per-folder storage report.
 * Each row is [path, file count, size in MB, folder owner].
 *
 * @param {string} rootFolderId  ID of the folder to audit.
 * @param {string} sheetId       ID of the spreadsheet for the report.
 */
function auditFolderTree(rootFolderId, sheetId) {
  // 1. Walk the tree, collecting one summary row per folder.
  const root = DriveApp.getFolderById(rootFolderId);
  const rows = [];
  walk(root, '', rows);

  // 2. Open the report sheet and reset it.
  const sheet = SpreadsheetApp.openById(sheetId).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 4)
    .setValues([['Path', 'Files', 'Size (MB)', 'Owner']]);

  // 3. Write every folder row in one bulk call.
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, 4).setValues(rows);
  }
  Logger.log('Audited ' + rows.length + ' folder(s).');
}

/**
 * Recursively scans a folder, pushing one summary row per folder.
 *
 * @param {Folder} folder  The folder to scan.
 * @param {string} path    Path of the parent, for a readable trail.
 * @param {Array}  rows    The accumulator array of folder rows.
 */
function walk(folder, path, rows) {
  // Build a readable path like "Clients/Acme/Assets".
  const full = path ? `${path}/${folder.getName()}` : folder.getName();

  // Tally file count and total size for this folder's own files.
  let count = 0;
  let size = 0;
  const files = folder.getFiles();
  while (files.hasNext()) {
    const f = files.next();
    count++;
    size += f.getSize();
  }

  // Record the summary row for this folder.
  rows.push([
    full,
    count,
    (size / BYTES_PER_MB).toFixed(2),
    folder.getOwner() ? folder.getOwner().getEmail() : '',
  ]);

  // Recurse into every subfolder.
  const subs = folder.getFolders();
  while (subs.hasNext()) walk(subs.next(), full, rows);
}

How it works

1. auditFolderTree opens the root folder and calls walk to scan the whole tree, collecting one summary row per folder.
2. It opens the dashboard spreadsheet, clears the first tab, and writes a header of Path, Files, Size (MB), and Owner.
3. If walk produced any rows, it writes them all in one bulk setValues call.
4. walk builds a readable path string so each folder is identifiable at a glance.
5. For each folder it loops over the files it directly contains, counting them and summing getSize into a running total of bytes.
6. It records a row with the path, count, size converted to megabytes, and the folder owner’s email — falling back to an empty string when there is no owner, as in shared drives.
7. It recurses into every subfolder, so each subfolder gets its own row.

Example run

Point the script at a Projects folder. The dashboard fills with one row per folder:

Path	Files	Size (MB)	Owner
Projects	4	12.40	[email protected]
Projects/2024	31	880.15	[email protected]
Projects/2024/Video	9	6420.77	[email protected]
Projects/Archive	210	3015.60	[email protected]

Sort by Size (MB) descending and the Video folder jumps to the top — there is your first candidate for archiving or cleanup.

Run it

This is an on-demand audit, so run it by hand when you need a fresh picture:

1. Paste the script into the Apps Script editor.
2. Add a small wrapper with your folder and sheet IDs:

function runAudit() {
  auditFolderTree('YOUR_ROOT_FOLDER_ID', 'YOUR_DASHBOARD_SHEET_ID');
}

3. Select runAudit, click Run, and approve the authorisation prompt.
4. Open the dashboard sheet and sort by Size (MB) to find the heavy folders.

To track growth over time, add a time-driven trigger on runAudit from the Triggers panel and copy the totals into a history tab after each run.

Watch out for

• Each row counts only the folder’s own files, not its subfolders. The size shown is local, not cumulative — to get a true folder-tree total, sum the child rows in the sheet with a SUMIF over the path column.
• A file in two folders is counted in both. Drive lets a file live in multiple places, so the grand total can exceed real storage usage.
• Large trees take time. walk touches every file, and each getSize call is a Drive request — a tree of many thousands of files can approach the six-minute execution limit. Audit subtrees separately if you hit it.
• getOwner returns null for files and folders in shared drives, which is why the owner column can be blank. That is expected, not a bug.
• A full rebuild clears the sheet first. Keep any charts or history on a separate tab so they survive the next run.