Daily Deliverable and Review Deadline Digest: One Weekday Email of What Needs Action

What This Builds

Deadlines for reports, exhibits and memos tend to live in three places: a calendar, a project tracker and somebody's head. This build adds a fourth, deliberately small one. Each weekday morning you get an email that lists the deliverables whose review is coming due, the ones already past their due date, and a count of those with no date at all. The sheet decides what belongs on each list. The Zap only carries the result to your inbox.

Prerequisites

• A new Google spreadsheet named "Deliverable Deadlines", separate from any other tracker
• A Zapier account on a paid plan, because the Zap has three steps and the free plan allows two-step Zaps only. The plan is Professional ($29.99/month). That is the total ongoing cost, since the Google account is one you already have.
• A Google account connected to Zapier for Sheets and Gmail
• A check of your firm's policy on project codes in an outside automation tool

The Concept

A good morning briefing from a colleague does not say "I searched and found nothing, so I stayed silent." It says "nothing due" and moves on. A Zapier search step cannot do that. If a lookup finds no matching row, the Zap halts, Zap History shows the run as safely halted, and no email is sent.

The fix is a single row that always exists. Your Summary tab holds one row, with the word summary in its Key cell, and formulas fill the rest of that row from the Deliverables tab. Zapier looks up that row, which is always found, and mails it. A quiet day produces an email saying none and zero. There is no AI step, no loop and no second lookup, and every count is computed by the sheet.

Build It Step by Step

Part 1: Build the Deliverables tab

Lay out the columns first.

Format column C as a date. F2, filled down to row 301:

=IF(A2="","",IF(E2="yes","",IF(C2="","MISSING DATE",IF(C2<TODAY(),"OVERDUE",IF(AND(D2<>"yes",C2-TODAY()<=Summary!$B$4),"REVIEW DUE","")))))

Read the formula from the outside in:

• A blank row (no deliverable code) returns blank.
• A delivered item returns blank, however late it was.
• An item with a code but no due date returns MISSING DATE.
• A due date before today returns OVERDUE. Marking the review as done does not clear this flag, because the deliverable has still not gone out.
• An item not yet reviewed, with a due date no more than Look ahead days away, returns REVIEW DUE. A blank Review done cell counts as not reviewed.
• Everything else returns blank.

Part 2: Build the Summary tab

Add a tab named Summary.

B2:

=COUNTIF(Deliverables!F2:F301,"REVIEW DUE")

C2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Deliverables!A2:A301&" "&Deliverables!B2:B301&" ("&TEXT(Deliverables!C2:C301,"yyyy-mm-dd")&")",Deliverables!F2:F301="REVIEW DUE")),"none")

D2:

=COUNTIF(Deliverables!F2:F301,"OVERDUE")

E2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Deliverables!A2:A301&" "&Deliverables!B2:B301&" ("&TEXT(Deliverables!C2:C301,"yyyy-mm-dd")&")",Deliverables!F2:F301="OVERDUE")),"none")

F2:

=COUNTIF(Deliverables!F2:F301,"MISSING DATE")

Check the parentheses in each formula. Every range in one FILTER runs from row 2 to row 301, so the heights match. The counts look for the exact flag words, so a filled-down formula on an empty row, which returns blank, is never counted.

The look-ahead value of 3 is a starting choice. Your review deadlines and windows are yours and your project lead's to set. This guide states no rule about them.

Keep Calculation Mode on Automatic (File, Settings, Calculation). Google's calculation settings page offers Automatic and Manual only and no scheduled recalculation, and TODAY() depends on recalculation. Open the sheet before relying on a count that looks stale. The spreadsheet's time zone (File, Settings, General) also decides what TODAY() means.

Part 3: Load the test rows

The rows below are invented. They assume a pretend TODAY() of Monday 2026-10-19 and B4 = 3. On another day, keep the same offsets from your real today.

The Summary row should then read:

D-105 shows that a finished review does not remove an overdue item from the list. D-106 shows that Delivered = yes clears everything.

Part 4: Create the Zap

1. In Zapier, create a new Zap.
2. Choose Schedule by Zapier as the trigger and the Every Day event.
3. Set the time of day.
4. Set Trigger on weekends? to no.
5. Add an action. Choose Google Sheets and the Lookup Spreadsheet Row event.
6. Pick the spreadsheet Deliverable Deadlines and the worksheet Summary.
7. For the lookup column choose Key (column A). Labels may be worded a little differently.
8. For the lookup value type summary.
9. Leave the other optional settings of this step alone.
10. Add a second action. Choose Gmail and the Send Email event.
11. In To, type your own address only. The digest goes to you and no one else.
12. Give it a subject such as Deliverable deadlines.
13. Type the body below, replacing each bracketed name with the field of that name from the Google Sheets step.

Review due [Review Due Count]: [Review Due Items]
Overdue [Overdue Count]: [Overdue Items]
Missing dates: [Missing Date Count]

1. Test the Google Sheets step. It should return the whole Summary row.
2. Test the Gmail step and read the email.
3. Turn the Zap on.

The step 2 lookup returns the entire matching row, and it is the only row Zapier sees. The Deliverables tab never leaves Google.

What leaves your sheet

Zapier receives the Summary row and the email text. That means deliverable codes, project codes, due dates and counts. Zap History stores the data each step received and returned, so those values stay in your Zapier account. Check Zapier's own pages for how long.

Use codes only. A client name, matter name, case caption, confidential figure or any piece of a document does not belong in this sheet. Ask whether your firm's policy and the engagement allow even project codes in an outside tool before you build. If the answer is no, use this sheet as a manual checklist and skip the Zap.

Zapier bills only successful action steps as tasks. Triggers, a search that halts and failed actions are not counted.

Real Example: Monday 2026-10-19

Setup: The seven invented rows from Part 3 and B4 set to 3. Input: At the scheduled hour on Monday the Zap fires and finds the summary row. Output:

Review due 2: D-101 P-7 (2026-10-20), D-102 P-7 (2026-10-22)
Overdue 1: D-105 P-9 (2026-10-16)
Missing dates: 1

Check it by hand. D-101 is 1 day out and D-102 is 3 days out, both inside the 3-day window with no review. D-103 is 4 days out, so it waits. D-104 is reviewed. D-105 is past due and undelivered. D-106 is delivered. D-107 has no date. What you do next: You chase the two reviews, raise D-105 with your project lead and ask for a date on D-107. The sample is invented, and your own windows and review steps are set by your project lead.

What to Do When It Breaks

• A weekday passed with no email → The failure is silent, so build the habit of noticing it. Check that the Zap is switched on. Open Zap History and look for a run on that day. Check that A2 on the Summary tab still says summary, because a renamed Key cell stops the lookup from finding its row and halts the Zap. Check that the spreadsheet and the Summary tab still have their original names. Reconnect Google if Zapier reports an expired connection.
• Weekend emails arrive → Open the trigger and set Trigger on weekends? to no.
• Items never appear → Confirm column C holds real dates and not text, and that the Delivered cell is not yes.
• A reviewed item still shows as REVIEW DUE → Review done must read yes, spelled exactly that way. A dropdown prevents typos.
• Dates show as numbers in the email → The TEXT wrapper was removed from C2 or E2. Restore it from Part 2.
• Counts look stale → Open the sheet and keep Calculation Mode on Automatic.

Variations

• Simpler version: Drop Review done and flag only OVERDUE and MISSING DATE.
• Extended version: Build a second spreadsheet with its own Summary tab for another team's deliverables, plus its own Zap. Do not add another Key row to this sheet.

What to Do Next

• This week: Run the test rows. Then replace them with your live deliverables.
• This month: Adjust Look ahead (days) to match the review lead time your project lead wants.
• Advanced: Pair it with the Weekly Data Release Watch Digest and the Weekly Data Vintage Refresh Digest, each in its own spreadsheet.

Advanced guide for economist professionals. This build needs a paid Zapier plan. Zapier and Google update their screens often, so if a label differs from this guide, look for the closest match and check the vendor's help pages.