Build a document-retention policy enforcer

Northwind has a retention policy on paper — keep finance records for years, clear out drafts after a few months — but no one enforces it. So the draft folders fill up with stale files, and the policy stays a document nobody acts on. The rules are simple enough; they just need a script to apply them.

This automation reads a retention table where each row is a folder and a number of days to keep files. It walks each folder and trashes anything that has not been touched within its window. Point a long window at finance folders, a short one at drafts, and the policy enforces itself.

What you’ll need

• A Retention Google Sheet with a header row and two columns: folderId (a Drive folder ID) and keepDays (a whole number of days).
• The retention sheet’s own file ID.
• Edit access to every folder listed, so the script can trash files in them.

The script

// The sheet that maps each folder to its retention window in days.
const RETENTION_SHEET_ID = '1abcRetentionId';

// Milliseconds in a day — used to turn keepDays into a cutoff time.
const MS_PER_DAY = 86400000;

/**
 * Reads the retention table and trashes files in each folder that
 * have not been updated within that folder's retention window.
 */
function enforceRetention() {
  // Read the table and drop the header row.
  const [_header, ...rows] = SpreadsheetApp.openById(RETENTION_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  // Bail out early if there are no rules to apply.
  if (!rows.length) {
    Logger.log('No retention rules — nothing to enforce.');
    return;
  }

  let trashed = 0;

  for (const [folderId, keepDays] of rows) {
    // Skip blank or malformed rows.
    if (!folderId || !keepDays) continue;

    // Anything not updated since this cutoff is past its retention window.
    const cutoff = Date.now() - parseInt(keepDays, 10) * MS_PER_DAY;

    const files = DriveApp.getFolderById(folderId).getFiles();
    while (files.hasNext()) {
      const f = files.next();
      if (f.getLastUpdated().getTime() < cutoff) {
        f.setTrashed(true);
        trashed++;
      }
    }
  }

  Logger.log(`Trashed ${trashed} file(s) past retention.`);
}

How it works

1. enforceRetention opens the retention sheet, reads every row, and discards the header.
2. If the table holds no rules, it logs a message and stops.
3. For each row it skips any with a missing folderId or keepDays, so a blank line in the sheet does not break the run.
4. It calculates a cutoff timestamp: the current time minus the folder’s keepDays window. Any file last updated before that moment is past its retention period.
5. It opens the folder and iterates over its files, comparing each file’s getLastUpdated() time against the cutoff.
6. Files older than the cutoff are sent to the trash with setTrashed(true).
7. After every folder is processed it logs how many files were trashed.

Example run

The Retention sheet holds two rules:

folderId	keepDays
1xFinanceFolder	2555
1xDraftsFolder	90

On a run dated 25 May 2026, the cutoffs work out as roughly 7 years ago for finance and 90 days ago for drafts:

File	Folder	Last updated	Result
Invoice 2019-04	Finance	2019-04-10	kept (within 7 years)
Old tax record 2015	Finance	2015-02-01	trashed
Logo draft v3	Drafts	2026-05-10	kept (within 90 days)
Pitch draft (Jan)	Drafts	2026-01-15	trashed

The log reads Trashed 2 file(s) past retention.

Trigger it

Retention is an ongoing job, so run it on a schedule:

1. In the Apps Script editor open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose enforceRetention, a Time-driven source, and a Week timer — weekly is frequent enough for a retention policy.
4. Save and approve the authorisation prompt.

To change the policy, just edit the Retention sheet — no code change needed.

Watch out for

• Trashed files stay recoverable for 30 days. For permanent, unrecoverable deletion use the Drive API’s files.delete endpoint — but be certain first.
• The script only looks at files directly in each folder, not in sub-folders. Add a recursive walk if your structure is nested.
• getLastUpdated() reflects the last edit by anyone, including a stray rename or comment. A file someone glanced at last week will not be trashed even if its content is years old.
• Test with a long window on a sample folder before pointing it at real data. A wrong keepDays value can trash a whole folder in one run.
• Large folders may hit the 6-minute execution limit. Split big folders across multiple rules, or process them with a continuation trigger.