How to Track Certificate of Insurance (COI) Expirations in Google Sheets (2026)

If you hire vendors or subcontractors, you probably already drop their certificates of insurance into a folder and type the expiration date into a spreadsheet. The useful part is knowing which certificate is about to lapse, and emailing the person who has to request a new one, before coverage runs out.

This is narrower than a general contract renewal tracker: one vendor often has several certificates (general liability, workers' compensation, auto) with different end dates, so the unit of tracking is the certificate, not the vendor.

Step 1: one row per certificate

Create a tab named COIs with columns such as: Vendor, Certificate type, Carrier or policy number, Expiration, Owner, Owner email, File link, Status, Sent 30d, Sent 14d, and Sent 0d. Format Expiration as a date.

Start from a sample tracker and a short playbook (sample data only): Make a copy of the Vendor COI Expiration Tracker and Make a copy of the COI Tracker Playbook.

If one PDF lists several policies that end on different days, split them into separate rows and keep the vendor name the same. Paste a Drive (or other) link to the current file in File link; the sheet is a log, not the filing cabinet.

In Days left, starting at row 2 (expiration in column D in this example):

=IF(D2="","",D2-TODAY())

In Expiry status:

=IF(D2="","Missing date",IF(D2<TODAY(),"Expired",IF(D2-TODAY()<=30,"Expiring soon","Current")))

Step 2: highlight and filter what needs action

Select the data range from row 2 and add conditional formatting:

Save filter views (Data › Filter views) named “Expired” and “Expiring in 30 days” so the team does not fight over one filter. A 15-minute weekly pass over those two views already catches a lot, if someone actually does it.

What will not email you by itself

Google Sheets will not send mail just because a date arrived. Tools › Notification settings and, on some work or school accounts, Tools › Conditional notifications both react to edits, not to the calendar. Google's help for conditional notifications also notes that TODAY() does not trigger a notification when its value changes while the spreadsheet is closed. See Use conditional notifications.

Step 3: 30, 14 and 0-day email alerts

Open Extensions › Apps Script, paste the script, save, run sendCoiAlerts once to approve permissions, and add a daily time-driven trigger.

/**
 * 30 / 14 / 0-day email alerts for certificate-of-insurance expirations.
 * Columns: A Vendor | B Certificate type | C Expiration | D Owner email
 *          E Sent 30d | F Sent 14d | G Sent 0d | H File link (optional)
 * A send is recorded in E/F/G so each milestone emails only once.
 * Trigger: Triggers > Add trigger > sendCoiAlerts > Time-driven > Day timer.
 */
function sendCoiAlerts() {
  const sh = SpreadsheetApp.getActive().getSheetByName('COIs');
  const rows = sh.getDataRange().getValues();
  const today = new Date(); today.setHours(0, 0, 0, 0);
  const tz = Session.getScriptTimeZone();
  const milestones = [
    { days: 30, col: 5 },
    { days: 14, col: 6 },
    { days: 0,  col: 7 },
  ];

  for (let r = 1; r < rows.length; r++) {
    const vendor = rows[r][0], typ = rows[r][1], exp = rows[r][2], email = rows[r][3];
    const link = rows[r][7] || '';
    if (!email || !(exp instanceof Date)) continue;
    const expDay = new Date(exp); expDay.setHours(0, 0, 0, 0);
    const left = Math.round((expDay - today) / (24 * 60 * 60 * 1000));
    for (const m of milestones) {
      if (left > m.days) continue;            // too early for this milestone
      if (rows[r][m.col - 1]) continue;       // already sent
      if (MailApp.getRemainingDailyQuota() < 1) return;
      const when = m.days === 0
        ? 'expires today'
        : (left < 0
            ? ('expired ' + Math.abs(left) + ' day(s) ago')
            : ('expires in ' + left + ' day(s)'));
      const subject = 'COI ' + when + ': ' + vendor + ' (' + typ + ')';
      let body = 'The ' + typ + ' certificate for ' + vendor + ' ' + when +
        ' (' + Utilities.formatDate(exp, tz, 'MMM d, yyyy') + '). Please request an updated certificate.';
      if (link) body += '\nCurrent file: ' + link;
      MailApp.sendEmail(email, subject, body);
      sh.getRange(r + 1, m.col).setValue('Sent ' + Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd'));
    }
  }
}

Each milestone has its own sent column, so a certificate can get a 30-day note, a 14-day note and a same-day note without duplicates. If a date is already inside a window when you first turn this on (for example 10 days left), the 30-day and 14-day emails both go out on the first run, which is usually what you want for a catch-up.

Caveats

Want reminders without maintaining a script? SheetReminders (disclosure: we make it) is a Google Sheets add-on that watches a date column and sends scheduled email reminders before, on or after each date, for example 30, 14 and 7 days before a due date, or after a date for overdue follow-ups. Sheet data stays inside your Google account and it uses narrow scopes. 14-day free trial, no credit card required. It is live on the Google Workspace Marketplace.
See SheetReminders
Short on time? Watch the 50-second version

Related Google Sheets guides

Instructions refer to Google Sheets, Google Docs, Google Forms, Google Drive and Apps Script as they worked on November 10, 2026. Not affiliated with Google or any other vendor named here. Google Sheets, Google Docs, Google Forms, Google Slides and Google Drive are trademarks of Google LLC. Features change, so verify in your own account. Questions or corrections: hello@loomwise.app.