Build a weekly Drive activity report

Northwind’s shared Drive is busy all week — briefs land, decks get revised, exports get dropped into client folders. By Friday nobody has a clear picture of what actually moved, so the weekly catch-up starts with five minutes of “wait, who changed the proposal?” while people click around looking for the latest version.

This script walks a root folder, finds every file touched in the last seven days, and emails a tidy digest to Awadesh on Friday afternoon. Each line names the file, who owns it, when it last changed, and a direct link — so the catch-up starts with the facts already on screen instead of a scavenger hunt.

What you’ll need

• A root folder in Drive that contains the work you want to track. The script walks it and every subfolder beneath it, so pointing at the top-level team folder is usually right.
• The folder’s ID — the long string in its URL after /folders/.
• Nothing else. The report is plain text and goes out over Gmail, which every Google account already has.

The script

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

// Where the Friday digest lands.
const REPORT_RECIPIENT = '[email protected]';

// How far back to look, in days.
const WINDOW_DAYS = 7;

// Cap the digest so a busy week does not produce a wall of text.
const MAX_ROWS = 50;

/**
 * Walks the root folder, collects every file changed in the last
 * WINDOW_DAYS, and emails the most recent ones to the report recipient.
 */
function weeklyActivityReport() {
  // 1. Work out the cutoff timestamp — anything older is ignored.
  const cutoff = Date.now() - WINDOW_DAYS * 86400000;

  // 2. Recurse from the root folder, collecting recently changed files.
  const recent = [];
  walk(DriveApp.getFolderById(ROOT_FOLDER_ID), recent, cutoff);

  // 3. Nothing changed — say so plainly and stop.
  if (!recent.length) {
    GmailApp.sendEmail(REPORT_RECIPIENT, 'Drive — this week',
      'Nothing changed in the last ' + WINDOW_DAYS + ' days.');
    return;
  }

  // 4. Newest first, capped at MAX_ROWS, formatted one block per file.
  const body = recent
    .sort((a, b) => b.updated - a.updated)
    .slice(0, MAX_ROWS)
    .map((r) => `• ${r.name} (${r.owner}) — ${r.updated.toISOString().slice(0, 10)}\n  ${r.url}`)
    .join('\n');

  // 5. Send the digest.
  GmailApp.sendEmail(REPORT_RECIPIENT, 'Drive — this week', body);
}

/**
 * Recursively collects files changed since the cutoff into `out`.
 * Each entry records the name, owner, last-updated date and URL.
 */
function walk(folder, out, cutoff) {
  // Check every file in this folder against the cutoff.
  const files = folder.getFiles();
  while (files.hasNext()) {
    const f = files.next();
    if (f.getLastUpdated().getTime() < cutoff) continue;
    out.push({
      name: f.getName(),
      owner: f.getOwner()?.getEmail() || 'unknown',
      updated: f.getLastUpdated(),
      url: f.getUrl(),
    });
  }

  // Then descend into every subfolder.
  const subs = folder.getFolders();
  while (subs.hasNext()) walk(subs.next(), out, cutoff);
}

How it works

1. weeklyActivityReport works out a cutoff timestamp — WINDOW_DAYS (7) days ago in milliseconds — and anything last updated before it is treated as old.
2. It calls walk on the root folder. walk checks every file’s getLastUpdated() against the cutoff, keeps the recent ones, then calls itself on each subfolder so the whole tree is covered.
3. If nothing changed, it still sends a short “nothing changed” note. A silent week and a broken script look identical otherwise — the note proves the job ran.
4. It sorts the collected files newest-first and slices to MAX_ROWS (50), so a frantic week produces a readable digest rather than a hundred-line email.
5. Each file becomes a two-line block: name, owner and date on the first line, the direct Drive link indented underneath.
6. It sends the digest to REPORT_RECIPIENT over Gmail.

Example run

Say three files changed in the tracked tree this week. The email body looks like this:

• Q3 proposal — Acme.gdoc ([email protected]) — 2025-09-09
  https://docs.google.com/document/d/1x…/edit
• Brand deck v4.pptx ([email protected]) — 2025-09-08
  https://drive.google.com/file/d/1yy…/view
• Shoot brief.pdf ([email protected]) — 2025-09-05
  https://drive.google.com/file/d/1zz…/view

One glance shows what moved, who owns it, and where to click — no folder-diving before the Friday catch-up.

Trigger it

Run this on a weekly schedule so the digest is waiting before the catch-up:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose weeklyActivityReport, event source Time-driven, type Week timer, day Friday, time 5pm to 6pm.
4. Save, and approve the authorisation prompt the first time.

The digest then arrives every Friday afternoon with no further action.

Watch out for

• getLastUpdated() covers content edits and renames, but not every kind of change — a comment or a sharing-permission tweak will not show up. The report tracks file edits, not all Drive activity.
• Walking a deep tree calls Drive once per folder. A few hundred folders is fine; many thousands may approach the script’s six-minute runtime limit. If you hit it, narrow ROOT_FOLDER_ID to the folders that actually matter.
• getOwner() can be null for files on a shared drive, which is why the script falls back to 'unknown'. Do not rely on the owner column for shared-drive content.
• The seven-day window is wall-clock from when the trigger fires. If the trigger slips by an hour, files changed right on the boundary may fall in or out — for a weekly digest that overlap is harmless, but do not treat it as an exact audit.
• The digest is capped at MAX_ROWS. If a genuinely busy week is being clipped, raise the cap rather than assuming the quiet tail did not change.