Weekly Deliverables Digest: A Monday Email of What Is Overdue, Due Soon or Missing a Date

What This Builds

Every Monday morning, one email lands in your own inbox listing the deliverables that need you this week: layout rounds that slipped, proofs and shoots inside your look-ahead window, and rows nobody gave a date. You keep one tracker in Google Sheets. The sheet works out what is late. Zapier only reads one finished line from the sheet and mails it to you.

Nothing is written by AI. The sheet builds the digest, so no client work goes to an AI vendor, and the email goes to you only. It is never copied to a client, a producer, a vendor or a freelancer.

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.
• Permission to run this at all. Read the "What Zapier Sees" section below first.

The Concept

Think of the sheet as a clerk who checks every deliverable each morning and writes a single note on a card. Zapier is the courier who picks up that card on Monday and drops it in your inbox. The courier never reads your project files and never decides what is late.

The design has one reason behind it. A Zapier search step that finds nothing stops the whole Zap ("Safely halted" in Zap History), and then no email is sent. That is the worst failure for a reminder, because a quiet week and a broken automation look the same. So the Zap never searches for overdue rows. It looks up one Summary row that always exists, and that row says "Nothing due in the look-ahead window" when there is nothing to report. The email arrives every week, busy or quiet.

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 counts, the look-ahead number and the digest lines. Those lines hold your project codes, deliverable labels, round numbers, due dates, owner initials and status words.

Keep the following out of the sheet entirely: unreleased campaign and product names, a client's real name where the work is confidential, talent and model names, amounts, rates and contract terms. Use a code such as "LANTERN" for the project and short labels such as "Hero layout" for the deliverable. Budgets and quotes stay in the producer's or finance's system. Before you build, check the agency's (or your company's) AI and software policy and the client contract, because some contracts treat even coded schedules as confidential and some policies limit which automation tools may touch client work.

Build It Step by Step

Part 1: Create the spreadsheet

Make a new Google spreadsheet named "Deliverables Digest". Give it two tabs: Deliverables and Summary. This spreadsheet is for this digest only. Do not add other trackers to it.

Deliverables tab, row 1 headers, one row per deliverable from row 2 down:

Add the dropdowns with Data, then Data validation, on columns C and G. Set column E to accept dates only. Format column H 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 of its column. Then fill it down to row 200. Rows past your last deliverable stay blank, and the Summary formulas ignore them.

H2 (Days Left):

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

It returns blank when the deliverable or the due date is blank. Otherwise it returns the number of days from today to the due date: positive for the future, zero for today, negative when late.

I2 (Stage):

=IF(B2="","",IF(G2="Yes","Closed",IF(E2="","Missing date",IF(H2<0,"OVERDUE",IF(H2<=Summary!$B$2,"DUE SOON","Later")))))

The order of the tests matters, and each test only runs if the one before it failed:

1. Deliverable blank: the row is empty, so Stage is blank. This keeps filled-down rows quiet.
2. Done is Yes: Stage is "Closed". This comes before the date tests because a finished item keeps its old due date. Test the date first and every completed deliverable would read OVERDUE forever.
3. Due Date blank: "Missing date". Days Left is blank here, so the formula must not compare it, which is why this test sits before the date comparisons.
4. Days Left below 0: "OVERDUE".
5. Days Left at most the Look Ahead Days in Summary!B2: "DUE SOON". A deliverable due today (0) and one due exactly on the last look-ahead day both count.
6. Anything else: "Later".

J2 (Digest Line):

=IF(B2="","",A2&" | "&B2&" | "&C2&IF(D2="",""," | round "&D2)&" | due "&IF(E2="","no date",TEXT(E2,"yyyy-mm-dd")&", "&IF(H2<0,-H2&IF(H2=-1," day"," days")&" overdue",IF(H2=0,"today","in "&H2&IF(H2=1," day"," days"))))&" | "&F2&" | "&I2)

It joins Project, Deliverable and Type, adds "round N" only when Round is filled, then the due date as text (TEXT keeps it from showing as a serial number), the distance in days, the Owner and the Stage. A blank Deliverable gives a blank line. A blank Due Date gives "due no date", and the date branch never touches the blank Days Left.

Summary C2 (Action Count):

=COUNTIF(Deliverables!I2:I200,"OVERDUE")+COUNTIF(Deliverables!I2:I200,"Missing date")+COUNTIF(Deliverables!I2:I200,"DUE SOON")

Summary D2 (Overdue Count):

=COUNTIF(Deliverables!I2:I200,"OVERDUE")

Summary E2 (Digest Lines):

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Deliverables!J2:J200,(Deliverables!I2:I200="OVERDUE")+(Deliverables!I2:I200="Missing date")+(Deliverables!I2:I200="DUE SOON"))),"Nothing due in the look-ahead window")

Both ranges in FILTER run from row 2 to row 200, so they are the same height. Blank rows have a blank Stage, so they match none of the three labels and are never counted. When nothing matches, FILTER returns an error, and IFERROR swaps in the fallback sentence. TEXTJOIN puts one digest line on each line of the cell (CHAR(10) is a line break).

A note on "today": 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 a digest still look a day or more old, open the sheet shortly before the Zap runs.

Part 3: Build the Zap

Create a new Zap with these three steps. Every name below was checked against Zapier's own app pages on 8 October 2026.

1. Trigger: Schedule by Zapier, event "Every Week". Choose Monday and a morning time. This trigger needs no spreadsheet row and no new data.

2. Action: Google Sheets, event "Lookup Spreadsheet Row". Connect your Google account. Pick the "Deliverables 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. This lookup always finds the one Summary row, so the Zap does not halt.

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

   • Subject: the text Deliverables digest: , the Action Count field, the text actions,, the Overdue Count field, the text overdue.
   • Body: the text Look ahead window: , the Look Ahead 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. Test each step as you go. If a field you need is missing in step 3, go back to step 2 and re-test it so Zapier reloads the Summary headers.

Part 4: Test and refine

Run the Zap once from the editor and read the email. Compare the digest lines with the Stage column on the Deliverables tab. Then change one Done cell to Yes, re-test, and check the line disappears. Change the Look Ahead Days and re-test. Rename nothing.

Zapier cost. Each run uses two action steps (the lookup and the email), so about two tasks a week when it succeeds. Zapier bills only action steps that complete successfully. The trigger does not count.

Real Example: Four Projects Slipping on a Monday

Invented data. Today is 2026-10-12 (a Monday). Look Ahead Days is 7. Projects are coded LANTERN and BEACON.

Check the arithmetic: 9 October is 3 days before 12 October, so -3. 19 October is 7 days after, which is not more than 7, so it is DUE SOON. 20 October is 8 days after, so it is Later. 2 October is 10 days before. The Closed row has Done set to Yes, so its past date does not matter.

Counts: OVERDUE is 2 (rows 2 and 8), Missing date is 1 (row 5), DUE SOON is 2 (rows 3 and 4). Action Count is 2 + 1 + 2 = 5. Overdue Count is 2.

The email subject reads Deliverables digest: 5 actions, 2 overdue, and the body reads:

Look ahead window: 7 days
LANTERN | Hero layout | Layout round | round 2 | due 2026-10-09, 3 days overdue | AB | OVERDUE
LANTERN | OOH 48-sheet | Proof | due 2026-10-12, today | CD | DUE SOON
LANTERN | Shoot day 1 | Shoot | due 2026-10-19, in 7 days | AB | DUE SOON
LANTERN | Press check | Press check | due no date | JK | Missing date
BEACON | Retouch files | Final files | due 2026-10-02, 10 days overdue | AB | OVERDUE

Five lines for five actions, in sheet order. In a quiet week the body ends with "Nothing due in the look-ahead window" and the subject shows 0 actions, 0 overdue. You still get the email.

If you already keep a format list in Excel from the Level 2 adaptation matrix, it can stay there. This tracker is the one the digest reads, with one row per thing you owe someone, not one row per size.

What to Do When It Breaks

• No email arrives on Monday (silent failure) → Nothing alerts you when a reminder stops, so treat a missing email as the signal. Open Zap History in Zapier. Check, in order, that the Zap is switched on, that the Google connection has not expired (reconnect it if Zapier says so), and that the Summary cell A2 still says summary exactly. A renamed Key or a changed worksheet name makes the lookup find nothing, and the run shows "Safely halted" with no email. Do not count on an error email to catch a halt. Add a recurring Monday calendar reminder at noon that says "Digest arrived?" for the first month.
• The digest shows "#REF!" or an error → A tab was renamed. The formulas refer to Deliverables and Summary by name. Rename the tab back, or edit the formulas.
• Day counts look a day old → See the note under the formulas. Check that Calculation Mode is Automatic, and open the sheet before the run.
• A row never appears → Check its Stage. A blank Deliverable cell makes the row invisible. A Due Date typed as text instead of a date will break Days Left, so keep the date validation on.
• Every line says OVERDUE → Column G was left blank instead of Yes. Only the word Yes closes a row.
• Rows past 200 are ignored → Extend the ranges and the fill-down.

Variations

• Simpler version: Skip the Look Ahead Days setting and type 7 into the DUE SOON formula. You lose the ability to change the window without editing formulas.
• Extended version: Add a Friday copy by duplicating the Zap with a different schedule. Do not add a second Summary row to this sheet.

What to Do Next

• This week: Enter your live projects, test one Monday, and compare the email with what you already know is late.
• This month: Retire the sticky notes. Add the next round of a deliverable as a new row so the history stays visible.
• Advanced: Build the usage-rights end date digest 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.