Track contract expiry from Drive files

Northwind signs contracts the way most studios do — a Google Doc per client, filed in a folder, then forgotten until something goes wrong. The expiry date is written into the document, but nobody re-reads every contract every month, so a renewal slips past and the studio is suddenly out of cover or auto-renewed on old terms.

This script reads the expiry date straight out of each contract Doc. It scans a folder, pulls the date out of the document text with a regular expression, and emails a digest of anything expiring within a warning window. It is a reminder system that needs no separate spreadsheet of dates to maintain — the contracts themselves are the source of truth.

What you’ll need

• A Drive folder of contract Google Docs — its ID goes in the config below.
• Each contract must contain an expiry line the regex can find, e.g. Expiry: 2026-03-31 or Expires 2026-03-31. The date must be in YYYY-MM-DD form.
• The address the digest should go to — set it in the config.

The script

// The folder of contract Google Docs to scan.
const CONTRACTS_FOLDER_ID = '1abcContractsFolderId';

// Where the "expiring soon" digest is sent.
const NOTIFY_EMAIL = '[email protected]';

// How many days ahead counts as "expiring soon".
const WARN_DAYS = 30;

// One day in milliseconds — used to build the cutoff date.
const DAY_MS = 86400000;

// Matches "expiry", "expires", or "expire", then a YYYY-MM-DD date.
const EXPIRY_PATTERN = /expir(?:y|es?)[:\s]*(\d{4}-\d{2}-\d{2})/i;

/**
 * Scans the contracts folder, reads the expiry date from each Doc,
 * and emails a digest of contracts expiring within WARN_DAYS.
 */
function checkExpiringContracts() {
  // 1. Get every Google Doc in the contracts folder.
  const files = DriveApp.getFolderById(CONTRACTS_FOLDER_ID)
    .getFilesByType(MimeType.GOOGLE_DOCS);

  // 2. Work out the cutoff: anything dated past this is not "soon".
  const now = Date.now();
  const cutoff = now + WARN_DAYS * DAY_MS;

  // 3. Walk each Doc and collect the ones expiring inside the window.
  const expiring = [];
  while (files.hasNext()) {
    const file = files.next();

    // Read the document body as plain text and search for an expiry date.
    const text = DocumentApp.openById(file.getId()).getBody().getText();
    const match = text.match(EXPIRY_PATTERN);
    if (!match) continue;

    // Skip contracts already expired or still beyond the warning window.
    const expiry = new Date(match[1]).getTime();
    if (expiry < now || expiry > cutoff) continue;

    expiring.push(`• ${file.getName()} expires ${match[1]}\n  ${file.getUrl()}`);
  }

  // 4. Send a digest only if something is actually expiring soon.
  if (!expiring.length) {
    Logger.log('No contracts expiring in the next ' + WARN_DAYS + ' days.');
    return;
  }

  GmailApp.sendEmail(
    NOTIFY_EMAIL,
    `${expiring.length} contract(s) expiring soon`,
    expiring.join('\n'),
  );
  Logger.log('Sent a digest of ' + expiring.length + ' contract(s).');
}

How it works

1. checkExpiringContracts opens the contracts folder and gets only its Google Docs — getFilesByType skips PDFs, images, and anything else.
2. It computes a cutoff timestamp WARN_DAYS into the future. A contract counts as “expiring soon” only if its date falls between now and that cutoff.
3. For each Doc it reads the body as plain text and runs EXPIRY_PATTERN against it. A Doc with no matching line is skipped silently.
4. It parses the captured YYYY-MM-DD date. Contracts already expired (before now) or still far off (past the cutoff) are dropped; the rest are formatted into a bullet line with the file name, date, and link.
5. If the expiring list is empty it logs and stops — no empty email. Otherwise it sends one digest to NOTIFY_EMAIL listing every contract due for renewal.

Example run

Say the contracts folder holds five Docs and today is 2025-10-28 with WARN_DAYS at 30. Two contracts have expiry lines inside the window:

Contract Doc	Expiry line found	In window?
Acme Retainer 2025	Expires 2025-11-12	yes
Globex Hosting	Expiry: 2026-06-01	no — too far off
Initech Support	Expiry: 2025-09-30	no — already expired
Umbrella Studio Lease	Expires 2025-11-20	yes
Wayne Co Licence	(no expiry line)	skipped

The run sends one email, subject “2 contract(s) expiring soon”, with:

• Acme Retainer 2025 expires 2025-11-12
  https://docs.google.com/document/d/...
• Umbrella Studio Lease expires 2025-11-20
  https://docs.google.com/document/d/...

Trigger it

The reminder is only useful if it runs without anyone asking:

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

Weekly means a 30-day window gives roughly four nudges before any renewal — enough warning without becoming noise.

Watch out for

• The regex needs a date in YYYY-MM-DD form. A contract written expires 31 March 2026 will not match — standardise the expiry wording when contracts are drafted.
• It reads only the first match per document. If a Doc mentions several dates, put the contract’s own expiry line first, or tighten EXPIRY_PATTERN.
• It scans one folder, not subfolders. Contracts filed in client subfolders are missed unless you add a recursive walk.
• Already-expired contracts are skipped, not flagged. If you want to catch ones that lapsed unnoticed, drop the expiry < now check and label them separately.
• The digest goes to a single address. For a shared inbox, use a group address in NOTIFY_EMAIL so the whole team sees the reminder.