Weekly Report and Review Deadline Digest: One Monday Email of What Needs Review or Is Late

What This Builds

Draft and review dates for reports scatter across emails, a project manager's calendar and your own notes. This build collects them in one small sheet. Every Monday you get one email listing the deliverables whose review is still open and whose due date falls inside your look-ahead window, the deliverables already past their due date and a count of deliverables with no date at all. The outcome it serves is a review that happens before the due date and not on it. The sheet decides what belongs on each list, and the Zap only carries the result to your inbox.

Prerequisites

• A new Google spreadsheet named "Report 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, and of what the client allows, on deliverable codes and project codes in an outside automation tool

The Concept

A good project coordinator does not stay silent when nothing is due. They say "nothing this week" and move 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 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 week produces an email that says none and zero. There is no AI step, no loop, no second lookup and no formula in Zapier, and every date comparison and count is computed by the sheet.

Due dates are the project manager's or the client's, typed by you. This guide states no agency deadline and no review period.

Data caution. The sheet holds deliverable codes, project codes and dates only. Client names, site names and addresses, report titles that identify a site, draft text and results stay in the firm's approved systems. Zapier is an outside tool, so check whether your firm and the client allow project codes to pass through it, and check Zapier's own pages for how long Zap History is kept. If the answer is no, use the sheet as a manual Monday checklist and skip the Zap.

Build It Step by Step

Part 1: Build the Deliverables tab

Name the first tab Deliverables. Lay out the columns first.

Format column C as a date. Add the dropdowns to D2 through E301 (Insert, then Dropdown, or Data, then Data validation, with the list yes and no). Menu names change, so look for the closest match.

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 whose review is not done, with a due date today or later and no more than Look ahead days away, returns REVIEW DUE. A blank Review done cell counts as not done, and a due date of today counts.
• Everything else returns blank.

The five closing parentheses at the end match the five IF functions open at that point, and the AND has its own closing parenthesis before the comma that starts the "REVIEW DUE" text.

Part 2: Build the Summary tab

Add a second 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 dates sit inside TEXT() so they join as 2026-10-20 and not as a serial number. IFERROR turns the empty-result error into the word none. The counts look for the exact flag words, so rows filled down past your last deliverable, which return blank, are never counted.

The look-ahead value of 7 is a starting choice. Because the email arrives weekly, a window shorter than a week can let a review slip between two emails. Set the window to suit your own review lead time.

Keep Calculation Mode on Automatic (File, Settings, Calculation). Google's calculation settings page offers Automatic and Manual only, with no scheduled recalculation, and TODAY() depends on recalculation. If a count looks stale, open the sheet before relying on the email. 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 = 7. On another day, keep the same offsets from your real today.

The Summary row should then read:

R-205 shows that a finished review does not remove an overdue item from the list. R-206 shows that Delivered = yes clears everything. R-204 shows that a reviewed item inside the window stays off the review list.

Part 4: Create the Zap

1. In Zapier, create a new Zap.
2. Choose Schedule by Zapier as the trigger and the Every Week event.
3. Choose Monday as the day (the field is labeled Day of the Week, or similar) and set the time of day for the email.
4. Add an action. Choose Google Sheets and the Lookup Spreadsheet Row event.
5. Pick the spreadsheet Report Deadlines and the worksheet Summary.
6. For the lookup column choose Key (column A). Labels may be worded a little differently.
7. For the lookup value type summary.
8. Leave the other optional settings at their defaults.
9. Add a second action. Choose Gmail and the Send Email event.
10. In To, type your own address only. The digest goes to you and no one else.
11. Give it a subject such as Report deadlines this week.
12. 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 lookup returns the entire matching row, and that row is all Zapier sees. The Deliverables tab never leaves Google.

What leaves your sheet

Zapier receives the Summary row and the email text. That means the word summary, the Review Due Count, the Review Due Items text (deliverable codes, project codes and due dates), the Overdue Count, the Overdue Items text and the Missing Date Count. Zap History stores the data each step received and returned, so those values stay in your Zapier account. The Review done and Delivered flags stay in the Deliverables tab. Check Zapier's own pages for how long Zap History is kept.

Use codes only. A client name, site name or address, report title that identifies a site, or any piece of a draft does not belong in this sheet.

Zapier bills only successful action steps as tasks. The trigger, a search that halts and a failed action are not counted, so a normal run uses two tasks.

Real Example: Monday 2026-10-19

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

Review due 2: R-201 P-14 (2026-10-20), R-202 P-14 (2026-10-26)
Overdue 1: R-205 P-16 (2026-10-16)
Missing dates: 1

Check it by hand. R-201 is 1 day out and R-202 is 7 days out, both inside the 7-day window with the review not done. R-203 is 8 days out and waits. R-204 is reviewed. R-205 is past due and undelivered. R-206 is delivered. R-207 has no date. What you do next: You chase the two reviews with your senior reviewer, tell your project manager that R-205 is late and ask for a due date for R-207. The sample is invented. Real due dates come from your project manager or the client.

What to Do When It Breaks

• A Monday passed with no email → This 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 that Monday. A "safely halted" run means the lookup found no row, so check that A2 on the Summary tab still says summary, because a renamed Key cell stops the lookup. Check that the spreadsheet and the Summary tab still have their original names. If Zapier reports an expired Google connection, reconnect it.
• 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. The dropdown prevents typos.
• A reviewed item shows as OVERDUE → That is the design. The review is done, but the deliverable has not gone out. Type yes in Delivered once it has.
• Dates show as numbers in the email → The TEXT wrapper was removed from the C2 or E2 formula. 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: Raise the aim from reminders to a review that is booked. Add a Reviewer column and a Review booked date column that the formulas do not read, and use them when you decide whom to chase first.

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 senior reviewer needs.
• Advanced: Pair it with the Daily Lab Results Watch and the Weekly Monitoring Event Schedule Digest, each in its own spreadsheet.

Advanced guide for environmental scientist 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.