Missing a rights expiry deadline is one of the most expensive mistakes in content distribution. When a license window closes and nobody notices, one of two things happens: you keep distributing content you no longer have rights to, exposing your organization to legal liability and financial penalties, or you miss a renewal window and lose access to a title that was generating revenue. Either outcome is painful. A single overlooked expiry on a high-value title can cost tens of thousands in penalties or lost revenue.

The fix is straightforward: an automated alert system that warns you well before any deadline arrives. In this tutorial, you will build one using Google Sheets and AppScript in about 30 minutes. No paid tools required.

Step 1: Set Up Your Rights Data Sheet

Create a new Google Sheet (or open your existing rights tracker) and set up the following columns starting in row 2. Leave row 1 for headers and rows 2-4 for the dashboard summary you will add later. Your data starts in row 5.

Column	Header	Purpose
A	Title	The name of the film, series, or episode
B	Territory	The licensed territory (e.g., US, UK, France, World)
C	Window Type	SVOD, TVOD, AVOD, Linear, etc.
D	Expiry Date	The date the rights window closes (formatted as a date)
E	Days Until Expiry	Calculated automatically by the script
F	Alert Status	Tracks which alert level has been sent
G	Last Alert Sent	Timestamp of the most recent alert email

Add your column headers in row 1. Format column D as dates (Format > Number > Date). Add a few sample rows of data starting in row 5 to test with. Set some expiry dates within the next 30, 60, and 90 days so you can verify each alert tier works.

Step 2: Write the Expiry Scanning Function

Open the script editor from Extensions > Apps Script. Delete any placeholder code and paste the following:

function scanForExpiries() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var lastRow = sheet.getLastRow();

  // Exit if no data rows exist (data starts at row 5)
  if (lastRow < 5) return;

  var dataRange = sheet.getRange(5, 1, lastRow - 4, 7);
  var data = dataRange.getValues();

  var today = new Date();
  today.setHours(0, 0, 0, 0);

  var urgent = [];    // 30 days or fewer
  var warning = [];   // 31-60 days
  var notice = [];    // 61-90 days

  for (var i = 0; i < data.length; i++) {
    var expiryDate = new Date(data[i][3]); // Column D
    if (isNaN(expiryDate.getTime())) continue; // Skip invalid dates

    var diffMs = expiryDate.getTime() - today.getTime();
    var daysUntil = Math.ceil(diffMs / (1000 * 60 * 60 * 24));

    // Update Days Until Expiry (column E)
    sheet.getRange(i + 5, 5).setValue(daysUntil);

    // Skip titles that have already expired
    if (daysUntil < 0) continue;

    var lastAlertLevel = data[i][5]; // Column F

    if (daysUntil <= 30 && lastAlertLevel !== "URGENT") {
      urgent.push({ title: data[i][0], territory: data[i][1], window: data[i][2], days: daysUntil, row: i + 5 });
    } else if (daysUntil <= 60 && daysUntil > 30 && lastAlertLevel !== "WARNING" && lastAlertLevel !== "URGENT") {
      warning.push({ title: data[i][0], territory: data[i][1], window: data[i][2], days: daysUntil, row: i + 5 });
    } else if (daysUntil <= 90 && daysUntil > 60 && lastAlertLevel !== "NOTICE" && lastAlertLevel !== "WARNING" && lastAlertLevel !== "URGENT") {
      notice.push({ title: data[i][0], territory: data[i][1], window: data[i][2], days: daysUntil, row: i + 5 });
    }
  }

  if (urgent.length > 0) sendExpiryNotification("URGENT", urgent, sheet);
  if (warning.length > 0) sendExpiryNotification("WARNING", warning, sheet);
  if (notice.length > 0) sendExpiryNotification("NOTICE", notice, sheet);
}

This function reads every row, calculates the number of days between today and the expiry date, writes that number back to column E, and sorts titles into three urgency buckets. It also checks column F so you never get the same alert level twice for the same title.

Step 3: Build the Email Notification System

Add the following function below scanForExpiries():

function sendExpiryNotification(urgency, titles, sheet) {
  var subjects = {
    "URGENT": "URGENT: " + titles.length + " title(s) expiring within 30 days",
    "WARNING": "WARNING: " + titles.length + " title(s) expiring within 60 days",
    "NOTICE": "NOTICE: " + titles.length + " title(s) expiring within 90 days"
  };

  var colors = { "URGENT": "#dc3545", "WARNING": "#fd7e14", "NOTICE": "#0d6efd" };

  var html = '<h2 style="color:' + colors[urgency] + ';">' + urgency + ': Rights Expiry Alert</h2>';
  html += '<p>The following titles require attention:</p>';
  html += '<table style="border-collapse:collapse;width:100%;font-family:Arial,sans-serif;">';
  html += '<tr style="background:#f8f9fa;">';
  html += '<th style="border:1px solid #dee2e6;padding:8px;text-align:left;">Title</th>';
  html += '<th style="border:1px solid #dee2e6;padding:8px;text-align:left;">Territory</th>';
  html += '<th style="border:1px solid #dee2e6;padding:8px;text-align:left;">Window</th>';
  html += '<th style="border:1px solid #dee2e6;padding:8px;text-align:left;">Days Left</th>';
  html += '</tr>';

  for (var i = 0; i < titles.length; i++) {
    var t = titles[i];
    var rowColor = t.days <= 7 ? '#fff3cd' : '#ffffff';
    html += '<tr style="background:' + rowColor + ';">';
    html += '<td style="border:1px solid #dee2e6;padding:8px;">' + t.title + '</td>';
    html += '<td style="border:1px solid #dee2e6;padding:8px;">' + t.territory + '</td>';
    html += '<td style="border:1px solid #dee2e6;padding:8px;">' + t.window + '</td>';
    html += '<td style="border:1px solid #dee2e6;padding:8px;font-weight:bold;">' + t.days + '</td>';
    html += '</tr>';
  }

  html += '</table>';
  html += '<p style="margin-top:16px;color:#6c757d;">This alert was generated automatically. Review your rights tracker for full details.</p>';

  var recipient = Session.getActiveUser().getEmail();

  GmailApp.sendEmail(recipient, subjects[urgency], "", { htmlBody: html });

  // Update the sheet to record the alert
  var now = new Date();
  for (var j = 0; j < titles.length; j++) {
    sheet.getRange(titles[j].row, 6).setValue(urgency);       // Column F: Alert Status
    sheet.getRange(titles[j].row, 7).setValue(now);            // Column G: Last Alert Sent
  }
}

The function builds an HTML email with a color-coded header and a table of expiring titles. Titles expiring in 7 days or fewer get a yellow highlight within the table. After sending, it stamps each row with the alert level and timestamp so the scanner skips it on the next run.

Step 4: Add the Daily Trigger

Add this helper function to create the trigger programmatically:

function createDailyTrigger() {
  // Remove any existing triggers for this function to avoid duplicates
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === "scanForExpiries") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }

  ScriptApp.newTrigger("scanForExpiries")
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
}

Run createDailyTrigger() once from the script editor (select it from the function dropdown and click Run). It will ask for authorization the first time. Grant access so the script can read your sheet and send email on your behalf.

Alternatively, you can set up the trigger manually: go to Triggers in the left sidebar of the Apps Script editor (the clock icon), click Add Trigger, select scanForExpiries as the function, choose Time-driven as the event source, select Day timer, and pick the time window you prefer.

Step 5: Add a Dashboard Summary

Back in your spreadsheet, use rows 2-4 for a quick dashboard. Put these labels and formulas in place:

In cell A2, type Expiring in 30 days: and in cell B2 enter:

=COUNTIFS(E5:E9999,">0",E5:E9999,"<=30")

In cell A3, type Expiring in 60 days: and in cell B3 enter:

=COUNTIFS(E5:E9999,">30",E5:E9999,"<=60")

In cell A4, type Expiring in 90 days: and in cell B4 enter:

=COUNTIFS(E5:E9999,">60",E5:E9999,"<=90")

These update in real time as the script refreshes column E each morning. Bold the cells and add conditional formatting (red for the 30-day count, orange for 60, blue for 90) so the dashboard is immediately scannable when you open the sheet.

Bonus: Add Slack Notifications

If your team lives in Slack, you can post alerts there instead of (or alongside) email. Create an incoming webhook in your Slack workspace under Apps > Incoming Webhooks, then add this function:

function sendSlackAlert(urgency, titles) {
  var webhookUrl = "https://hooks.slack.com/services/YOUR/WEBHOOK/URL";

  var text = "*" + urgency + ": " + titles.length + " title(s) expiring soon*\n";
  for (var i = 0; i < titles.length; i++) {
    text += "- " + titles[i].title + " (" + titles[i].territory + ", " + titles[i].window + ") - *" + titles[i].days + " days*\n";
  }

  var payload = JSON.stringify({ text: text });

  UrlFetchApp.fetch(webhookUrl, {
    method: "post",
    contentType: "application/json",
    payload: payload
  });
}

Call sendSlackAlert(urgency, titles) alongside or in place of sendExpiryNotification() inside scanForExpiries().

Next Steps

Download the Rights Expiry Calendar template to get a pre-built spreadsheet with this script already installed, sample data, and the dashboard pre-configured. You can duplicate it and start entering your own rights windows immediately.

Once this system is running, you will never be blindsided by an expiry deadline again. The 30-minute investment pays for itself the first time it catches a window you would have missed.

Google Sheets AppScript: Build a Rights Expiry Alert System in 30 Minutes