Usage-Rights End Date Digest: A Weekly Email of Licensed Assets Nearing or Past Their End Date

What This Builds

A photo license ends, a font license is up for renewal, a music track is still running in a cutdown nobody remembered. This build puts the end dates in front of you early. You keep a Rights tracker in Google Sheets and type in the end date from each signed contract or license. Once a week, one email arrives in your inbox listing every asset that is past its date, inside your warning window, waiting on a renewal, or missing an end date.

The sheet tracks dates only. It does not read contracts, and no AI is involved. The email goes to you alone. It never contacts a vendor, a photographer, a talent agency or a producer.

Prerequisites

• A Google account with Sheets and Gmail
• A Zapier account on a paid plan. This Zap has three steps (a schedule, a lookup and an email), and Zapier's free plan allows two-step Zaps only. A paid plan starts at $29.99/month for the Professional plan. Check Zapier's pricing page for the task allowance on your plan.
• Total ongoing cost of the finished build: the Zapier plan above. Sheets and Gmail are in the Google account you already have.
• Access to the signed contracts or license files, because you type each end date from them
• A read of the "What Zapier Sees" section below before you enter any rows

The Concept

The sheet is a calendar that can shout. Each asset is one row, and the sheet works out how many days remain before the date you typed. Zapier is the courier: once a week it picks up one finished summary line and delivers it to your inbox.

Why one summary line and not a search for expiring rows? A Zapier search step that finds nothing halts the whole Zap ("Safely halted" in Zap History), and nothing is sent. For an expiry reminder that is the wrong failure: a quiet week and a dead automation would look the same. So the Zap looks up a Summary row that always exists. In a quiet week that row says "No usage end dates inside the warning window", and the email still arrives.

Where the Authority Sits

This tracker reminds you about a date. It does not know what a license allows. Media, territory, term, renewal and what happens after the end date are all in the contract or the license itself. Usage end dates can depend on first-use dates or territory, so type the date the contract gives and ask business affairs when you are unsure. Whether an asset can keep running or be renewed is a question for business affairs or the agency's or company's legal team. Nothing in the sheet or the email answers it, and the "PAST END DATE" label is a prompt to ask, not a ruling.

What Zapier Sees

Zapier runs the lookup and the email, and Zap History stores the data each step handled. It keeps the Summary row: the warn-days number, the count and the digest lines. Those lines hold project codes, asset labels, asset types, your own license references, end dates and status words.

Keep the following out of the sheet: unreleased campaign and product names, a client's real name where the work is confidential, talent and model names, amounts, fees, rates and any contract terms. The License Ref column holds your own short reference to the contract file (for example "LIC-014"), not the text of the license. Check the agency's or company's AI and software policy and the client contract before you build, because license and talent agreements often carry their own confidentiality terms, and some policies limit which automation tools may hold even coded data.

Build It Step by Step

Part 1: Create the spreadsheet

Make a new Google spreadsheet named "Usage Rights Digest" with two tabs: Rights and Summary. It is a separate spreadsheet from any other digest.

Rights tab, row 1 headers, one row per licensed asset from row 2 down:

Add dropdowns with Data, then Data validation, on columns C and F, and set column E to accept dates only. Format column G as a plain number.

Summary tab, row 1 headers, ONE data row in row 2:

Part 2: The formulas

Paste each formula into row 2. Then fill it down to row 200. Blank rows stay blank and are not counted.

G2 (Days Left):

=IF(OR(B2="",E2=""),"",E2-TODAY())

Blank when the asset or the end date is blank. Otherwise the days from today to the end date: positive ahead, zero today, negative once passed.

H2 (Stage):

=IF(B2="","",IF(OR(F2="Pulled from use",F2="Renewed (new row added)",F2="No end date (per contract)"),"Closed",IF(E2="","Missing end date",IF(G2<0,"PAST END DATE, ask business affairs",IF(G2<=Summary!$B$2,IF(F2="Renewal requested","WAITING ON RENEWAL","ENDS SOON"),"Later")))))

Each test runs only if the ones before it failed:

1. Asset blank: the row is empty, so Stage is blank.
2. Status is Pulled from use, Renewed (new row added) or No end date (per contract): "Closed". This comes before the date tests for two reasons. A pulled or renewed asset keeps an old end date that should no longer alarm you. And an asset with no end date in its contract has a blank Usage End on purpose, so it must not be flagged as missing.
3. Usage End blank: "Missing end date". Days Left is blank here, so this test sits ahead of the date comparisons.
4. Days Left below 0: "PAST END DATE, ask business affairs". A "Renewal requested" row still lands here once the date has passed, because the date has passed whatever has been asked.
5. Days Left at most Warn Days (Summary!B2): if Status is Renewal requested, "WAITING ON RENEWAL", otherwise "ENDS SOON".
6. Anything else: "Later".

The label with a comma and the labels with parentheses are fine as typed. The Summary formulas below test for these exact strings, so copy them as shown.

I2 (Digest Line):

=IF(B2="","",A2&" | "&B2&" | "&C2&" | "&D2&" | "&IF(E2="","no end date entered","ends "&TEXT(E2,"yyyy-mm-dd")&", "&IF(G2<0,-G2&IF(G2=-1," day"," days")&" ago",IF(G2=0,"today","in "&G2&IF(G2=1," day"," days"))))&" | "&H2)

It joins Project, Asset, Asset Type and License Ref, then the end date as text (TEXT prevents a serial number) with the distance in days, then the Stage. A blank Asset gives a blank line, and a blank Usage End gives "no end date entered" without touching the blank Days Left.

Summary C2 (Action Count):

=COUNTIF(Rights!H2:H200,"PAST END DATE, ask business affairs")+COUNTIF(Rights!H2:H200,"Missing end date")+COUNTIF(Rights!H2:H200,"WAITING ON RENEWAL")+COUNTIF(Rights!H2:H200,"ENDS SOON")

Summary D2 (Digest Lines):

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Rights!I2:I200,(Rights!H2:H200="PAST END DATE, ask business affairs")+(Rights!H2:H200="Missing end date")+(Rights!H2:H200="WAITING ON RENEWAL")+(Rights!H2:H200="ENDS SOON"))),"No usage end dates inside the warning window")

Both FILTER ranges run from row 2 to row 200. Blank rows have a blank Stage and match none of the four labels, so they are not counted. When nothing matches, FILTER errors and IFERROR supplies the fallback sentence. TEXTJOIN with CHAR(10) puts one asset on each line.

Zapier reads the values the sheet has stored, and TODAY() is a volatile function that Google Sheets recalculates on its own schedule. In File, then Settings, then Calculation, keep Calculation Mode on Automatic. Google's help page on calculation settings says Manual mode only calculates when you select Calculate, which would freeze every day count. If the day counts in an email still look stale, open the sheet shortly before the Zap runs.

Part 3: Build the Zap

Every name below was checked against Zapier's own app pages on 8 October 2026.

1. Trigger: Schedule by Zapier, event "Every Week". Pick a day and a morning time.

2. Action: Google Sheets, event "Lookup Spreadsheet Row". Connect your Google account. Pick the "Usage Rights Digest" spreadsheet and the Summary worksheet. Set Lookup Column to Key and Lookup Value to summary. If the step offers a setting to create a row when none is found, leave it off.

3. Action: Gmail, event "Send Email". In To, type your own address. Leave Cc and Bcc empty. Build the Subject and Body from fixed text and step 2 fields, in this order:

   • Subject: the text Usage rights digest: , the Action Count field, the text assets need a look.
   • Body: the text Warning window: , the Warn Days field, the text days, a line break, and the Digest Lines field.

   If the step offers a body type, choose plain text so the line breaks show.

There is no AI step, no Formatter step and no loop. If a Summary field is missing in step 3, go back to step 2 and re-test so Zapier reloads the headers.

Part 4: Test and refine

Run the Zap from the editor and compare the email with the Stage column. Change a Status to Pulled from use, re-test, and check the line drops out. Each run uses two action steps (the lookup and the email), so about two tasks a week. Zapier bills only action steps that complete successfully, and the trigger does not count.

Real Example: A Quarter-End Check

Invented data. Today is 2026-10-08. Warn Days is 45.

Check the arithmetic. From 8 October to 20 November is 23 days left in October plus 20, so 43. To 15 November is 23 plus 15, so 38. To 1 March 2027 is 23 + 30 + 31 + 31 + 28 + 1 = 144 (February 2027 has 28 days). From 8 October 2026 to 8 October 2027 is 365 days, and 2 October is 6 days earlier, so 359. 5 October is 3 days before today, 30 September is 8 days before, and 2 October is 6 days before. Both 43 and 38 are within 45, and 144 and 359 are not.

Action Count is 1 (PAST END DATE) + 1 (Missing end date) + 1 (WAITING ON RENEWAL) + 1 (ENDS SOON) = 4. The three Closed rows and the two Later rows stay out.

The subject reads Usage rights digest: 4 assets need a look, and the body reads:

Warning window: 45 days
LANTERN | Hero photo 03 | Photo | LIC-014 | ends 2026-10-05, 3 days ago | PAST END DATE, ask business affairs
LANTERN | Display font A | Font | LIC-021 | ends 2026-11-20, in 43 days | ENDS SOON
BEACON | Music track B | Music | LIC-033 | ends 2026-11-15, in 38 days | WAITING ON RENEWAL
BEACON | Illustration 07 | Illustration | LIC-040 | no end date entered | Missing end date

What you do next: for the first line, you ask business affairs whether the asset is still running anywhere. For the font, you check the contract for the date's meaning and raise renewal with whoever owns vendor contracts. For the music track, you check that the renewal request is moving. For the illustration, you open the contract and type the date, or switch Status to "No end date (per contract)" if it truly has none. In a quiet week the body is the fallback sentence and the count is 0.

What to Do When It Breaks

• No email arrives (silent failure) → An expiry reminder that dies quietly is worse than none, so treat a missing email as the alarm. Open Zap History in Zapier and check that the Zap is switched on, that the Google connection has not expired, and that Summary cell A2 still says summary exactly. If the Key was renamed or the worksheet was renamed, the lookup finds nothing and the run shows "Safely halted" with no email. Do not rely on an error email to catch a halt. Keep a weekly calendar reminder for the first month that says "Rights digest arrived?"
• An asset you know is expiring is missing → Look at its Stage. A Status of Pulled from use, Renewed (new row added) or No end date (per contract) closes the row. A blank Asset cell hides it.
• The Stage column shows a different label than expected → The Summary formulas match the label text exactly, including the comma and the parentheses. Re-copy the formulas rather than retyping.
• A renewal was agreed but the old row still shows → Set the old row to Renewed (new row added) and add a new row with the new end date from the new contract.
• #REF! errors → A tab was renamed. Rename it back to Rights or Summary.
• Rows past 200 are ignored → Extend the ranges and the fill-down.

Variations

• Simpler version: Hard-code 45 into the Stage formula and drop the Warn Days cell.
• Extended version: Build a second Zap on a different day with a different Warn Days only if you track two clients under separate policies, and give each its own spreadsheet and Summary row.

What to Do Next

• This week: Enter every asset on your live campaigns, with end dates typed from the contracts.
• This month: Make the entry a habit at the moment a license is signed.
• Advanced: Pair it with the deliverables digest, built in its own separate spreadsheet.

Advanced guide for art directors, agency or in-house. Zapier and Google change their screens often, so confirm each step name in the app as you build.