Build a Drive cleanup recommendation report

Northwind’s shared Drive has been growing for years and is creeping towards its storage limit. Nobody wants to delete files blind, but nobody has time to audit the whole tree by hand either. The two things worth flagging — files that are large, and files that have not been touched in months — are easy to detect; they just need a script to surface them.

This automation walks a folder tree, measures every file’s size and last-updated date, and writes a report to a Google Sheet listing only the files worth a second look, each with a plain-language suggestion. It does not delete anything — it hands a human a shortlist to act on.

What you’ll need

• A Drive folder to audit (the root of the tree to walk), and its folder ID.
• A Google Sheet to hold the report, and its file ID. The script clears and rewrites the first tab on each run.
• Read access to every folder in the tree, so the walk can see all the files.

The script

// A file is "stale" if it has not been updated in this many days.
const STALE_DAYS = 180;

// A file is "large" if it is bigger than this, in bytes (50 MB).
const BIG_BYTES = 50 * 1024 * 1024;

// Milliseconds in a day.
const MS_PER_DAY = 86400000;

/**
 * Walks a folder tree and writes a cleanup report — large or stale
 * files only — to the first tab of a Google Sheet.
 * @param {string} rootFolderId The folder to audit.
 * @param {string} sheetId The spreadsheet to write the report to.
 */
function cleanupReport(rootFolderId, sheetId) {
  // 1. Collect candidate files by walking the tree.
  const candidates = [];
  walk(DriveApp.getFolderById(rootFolderId), '', candidates);

  // 2. Rebuild the report sheet from scratch.
  const sheet = SpreadsheetApp.openById(sheetId).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 5).setValues([
    ['Path', 'Name', 'Size MB', 'Last touched', 'Suggestion'],
  ]);

  // 3. Write the candidate rows, if there are any.
  if (candidates.length) {
    sheet.getRange(2, 1, candidates.length, 5).setValues(candidates);
  }
  Logger.log(`Reported ${candidates.length} cleanup candidate(s).`);
}

/**
 * Recursively scans a folder and its sub-folders, pushing any large
 * or stale file onto the candidates array.
 * @param {Folder} folder The folder to scan.
 * @param {string} path The path of the parent, for readable output.
 * @param {Array[]} out The candidates array to append rows to.
 */
function walk(folder, path, out) {
  // Build a readable path for this folder.
  const full = path ? `${path}/${folder.getName()}` : folder.getName();
  const staleCutoff = Date.now() - STALE_DAYS * MS_PER_DAY;

  // Check each file in this folder against the size and age thresholds.
  const files = folder.getFiles();
  while (files.hasNext()) {
    const f = files.next();
    let suggestion = '';

    // Flag large files.
    if (f.getSize() > BIG_BYTES) suggestion = 'large — review';

    // Flag stale files, combining the note if it is also large.
    if (f.getLastUpdated().getTime() < staleCutoff) {
      suggestion = suggestion ? 'large + stale' : 'stale — archive';
    }

    // Only files that earned a suggestion go into the report.
    if (suggestion) {
      out.push([
        full,
        f.getName(),
        (f.getSize() / 1024 / 1024).toFixed(1),
        f.getLastUpdated(),
        suggestion,
      ]);
    }
  }

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

How it works

1. cleanupReport starts an empty candidates array and calls walk on the root folder to fill it.
2. walk builds a readable full path for the current folder by joining it to the path passed down from its parent.
3. It works out the staleCutoff — the timestamp STALE_DAYS ago.
4. For each file in the folder it checks two things: whether the size exceeds BIG_BYTES, and whether the last-updated time is before the stale cutoff. A file can earn one suggestion, both, or none.
5. Only files with a non-empty suggestion are pushed onto the array, along with their path, name, size in MB, and last-touched date.
6. walk then recurses into every sub-folder, so the whole tree is covered.
7. Back in cleanupReport, the report sheet is cleared, a header row is written, and all the candidate rows are written in a single setValues call.

Example run

Running cleanupReport over a Projects folder produces a report like this:

Path	Name	Size MB	Last touched	Suggestion
Projects/2023/Acme	Acme final render.mp4	412.6	2023-06-02	large + stale
Projects/Archive	Old proposal deck.pptx	8.2	2024-09-11	stale — archive
Projects/Active	Hero image master.psd	88.4	2026-05-19	large — review

Files that are small and recently touched never appear — the report is a shortlist, not a full inventory. The team can scan it and decide what to delete or move to cold storage.

Run it

This is an on-demand audit — run it when storage is getting tight:

1. In the Apps Script editor, call cleanupReport with your root folder ID and report sheet ID.
2. Approve the authorisation prompt the first time.
3. Open the report sheet and work down the suggestions.

For a regular check, add a Time-driven trigger that runs cleanupReport monthly via a small wrapper that passes the two IDs.

Watch out for

• The report only flags files; it never deletes anything. Acting on the suggestions is a deliberate human step — that is by design.
• getSize() returns 0 for Google-format files (Docs, Sheets, Slides). They do count against storage, but this report will never flag them as large — it catches uploaded files like videos and images.
• A deep folder tree means many getFiles/getFolders calls. A large Drive can hit the 6-minute execution limit — narrow the root folder or walk sub-trees separately.
• The report despite its name does not detect duplicates. Matching files by name or content hash is a heavier job; this script covers only large and stale.
• sheet.clear() wipes the first tab every run. Point the script at a dedicated report sheet, not one that holds anything else.