Weekly Deadline Digest: A Monday Email That Lists What Is Overdue or Due Soon Across Every Client

For Copyeditors and Proofreaders

Tools: Zapier + Google Sheets + Gmail | Time to build: 1 to 2 hours | Difficulty: Advanced Prerequisites: the Level 2 guide that builds the Deadlines tab with Gemini in Google Sheets (this guide uses the same tab and adds a Summary tab), and basic comfort with Google Sheets formulas

What This Builds

Three clients, nine active projects, and a due date for every stage of each one. The dates live in your head, in three inboxes and in a calendar you check when you remember. This build puts them in one sheet and sends you one email every Monday morning that lists only what needs action: stages that are overdue, stages with no date, and stages due within the look-ahead window you choose.

No AI is involved. The sheet does the sorting and the wording, and Zapier carries one finished block of text from the sheet to your own inbox. Nothing is sent to an author, an editor or a client. The email arrives every week, including a week when nothing is due, because the sheet always produces a digest to send.

Prerequisites

• The Deadlines tab from the Level 2 Gemini in Google Sheets deadline tracker guide, or a new Google Sheet where you build it from the table below
• A Google account with Gmail and Google Sheets
• A Zapier account on a paid plan, because this Zap has three steps and the free plan allows two-step Zaps only. Currently Professional at $29.99/month
• Permission to hold your client's project codes in a Google Sheet and a Zapier account. Check your contract or ask the managing editor (see "What Zapier Sees" below)

Total ongoing cost: the Zapier subscription above. Google Sheets and Gmail are part of the Google account you already use, and there is no AI subscription in this build. If your Google account is a business one, its cost comes from your Business Standard plan and not from this build.

The Concept

Think of a front-desk clerk who reads one index card to you every Monday. The clerk never opens your files and cannot read the shelves. The card is the only thing the clerk sees, so the whole job is writing a good card.

Your sheet writes the card. The Summary tab holds one row, and that row always exists, so the clerk always has something to read. Zapier looks up that row, then mails you its contents. That design is deliberate. A Zapier lookup that finds no row stops the Zap with a "Safely halted" status in Zap History. No later step runs, and no email goes out. A digest built from a list of overdue items would go silent in exactly the weeks when nothing is overdue, and you could not tell a quiet week from a broken Zap. A Summary row that always exists avoids that.

What Zapier Sees and Keeps

Zapier is not an AI step here, but it does handle your data. Every run stores the data each step received and returned in Zap History: the Summary row (the settings, the two counts and the full Digest Lines text) and the email address and subject the Gmail step used. Anything you put in those cells is therefore held by Zapier as well as by Google.

So the sheet holds codes and short labels only:

• Project codes such as "P104" and client codes such as "Client B", never an unpublished title, an author's name or a confidential client's name
• Stage names from the dropdown, dates and the Done value
• Never manuscript text, never amounts, never rates, and never contact details

Keep a private key (a separate note or a row on a tab Zapier does not read) that maps "P104" to the real title and "Client B" to the real client. Then check the client contract or the publisher's policy, because some contracts address even coded data held in outside services. If a client says no, leave that client out of the sheet and track their dates in your calendar.

Build It Step by Step

Part 1: Lay Out the Sheet

You need two tabs. Name them exactly Deadlines and Summary, because the formulas below refer to those names.

Deadlines tab, row 1 headers and columns:

Format column D as a date and column F as a plain number. (If Days Left shows up looking like a date, change its format to a plain number.) The dropdowns come from the Insert menu in Sheets. If you built the tab with the Level 2 guide, columns A to F already exist, and you only add Flag and Digest Line.

Summary tab, row 1 headers and the single data row in row 2:

Zapier reads row 1 as the field names, so keep those headers as written. Look Ahead Days is your Settings cell: change B2 and the whole sheet follows.

Part 2: Add the Formulas

Enter each formula in row 2 of its column and fill down to row 200. The ranges below stop at row 200. If you will ever have more rows, change every 200 to a larger number in all of the formulas, so that each range stays the same height.

Deadlines!F2 (Days Left):

=IF(OR(A2="",D2=""),"",D2-TODAY())

Blank when the project or the due date is blank. Otherwise the due date minus today: positive means days remaining, negative means days overdue.

Deadlines!G2 (Flag):

=IF(A2="","",IF(E2="Yes","Closed",IF(D2="","Missing date",IF(F2<0,"OVERDUE",IF(F2<=Summary!$B$2,"DUE SOON","Later")))))

The column is called Flag because Stage is already the name of your workflow column.

Deadlines!H2 (Digest Line):

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

The TEXT function turns the date into readable text. Without it, a date joined into a sentence turns into a serial number such as 46307.

Summary!C2 (Action Count):

=COUNTIF(Deadlines!$G$2:$G$200,"OVERDUE")+COUNTIF(Deadlines!$G$2:$G$200,"Missing date")+COUNTIF(Deadlines!$G$2:$G$200,"DUE SOON")

Summary!D2 (Overdue Count):

=COUNTIF(Deadlines!$G$2:$G$200,"OVERDUE")

Summary!E2 (Digest Lines):

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Deadlines!$H$2:$H$200,(Deadlines!$G$2:$G$200="OVERDUE")+(Deadlines!$G$2:$G$200="Missing date")+(Deadlines!$G$2:$G$200="DUE SOON")>0)),"Nothing due in the look-ahead window")

FILTER keeps the Digest Line of every row whose Flag is one of the three action labels. TEXTJOIN stacks those lines with a line break (CHAR(10)) between them and skips empty results. When FILTER finds no matching row it returns an error, and IFERROR replaces that error with the fallback sentence. That is what makes the quiet-week email say something.

Both ranges inside FILTER, and every range in the COUNTIF formulas, run from row 2 to row 200, so they are the same height.

Part 3: Walk the Flag Formula Through Each Case

Assume Look Ahead Days is 7 and today is 2026-10-12.

• A blank row. Project is blank, so the first test (A2="") returns an empty string. Days Left, Flag and Digest Line are all blank, and COUNTIF never counts them because it looks for specific labels only. This is why you can fill the formulas down past your last real row.
• Closed. Done is Yes, so the Flag is Closed whatever the date says. Closed is tested before anything involving dates. Otherwise a finished stage whose due date has passed would be flagged OVERDUE and would keep appearing in every digest.
• Missing date. The project is filled in, Done is not Yes, and Due Date is blank. The Flag says Missing date. This test comes before the Days Left tests for a reason: a blank Days Left is empty text, and Sheets treats empty text as larger than any number, so the later tests would label the row Later by mistake.
• OVERDUE. Due 2026-10-10 gives a Days Left of -2. Below zero, so OVERDUE.
• DUE SOON. Due 2026-10-15 gives 3, and 3 is not above 7. A stage due exactly 7 days out also counts as DUE SOON, since the test is "at most".
• Later. Due 2026-10-30 gives 18, which is more than 7. Later.

Part 4: Build the Zap

Open zapier.com, choose to create a new Zap, and add three steps. Check each event name against the list Zapier shows. These are the names as of 2026-10-09.

Step 1. Trigger: Schedule by Zapier, event Every Week. Pick the day (Monday) and the time of day (early morning). Test the trigger. It needs no data from you.

Step 2. Action: Google Sheets, event Lookup Spreadsheet Row. Connect your Google account. Choose your digest spreadsheet and the Summary worksheet. For the lookup column choose Key, and for the lookup value type summary. Leave off any option that creates a row when none is found. Run the test: Zapier should show one row with fields named Look Ahead Days, Action Count, Overdue Count and Digest Lines.

Step 3. Action: Gmail, event Send Email.

• To: your own email address, typed in. Nobody else.
• Subject: build it from five pieces in this order: the text "Deadline digest: ", the Action Count field from Step 2, the text " to act on, ", the Overdue Count field, and the text " overdue".
• Body: insert the Digest Lines field from Step 2 and nothing else. If the step offers a body type, choose plain text so the line breaks survive.

Test the step. Gmail sends a real email to you. Then turn the Zap on.

Each run uses successful action steps as tasks (the lookup and the send). The schedule trigger does not count as a task. Zapier's pricing page states your plan's monthly task allowance.

Part 5: Keep the Sheet Honest

• Open the sheet once the morning of the run if the counts look a day behind. TODAY() is recalculated by Sheets, not by Zapier. Google's calculation settings (File, then Settings, then Calculation) offer Automatic and Manual modes only, and no scheduled recalculation, so keep the mode on Automatic.
• Mark stages Yes in Done as you finish them. Closed rows stay out of the digest.
• Cut finished rows to an Archive tab every quarter or so, so the 200 rows last.

Real Example

Invented date for TODAY(): Monday 2026-10-12. Look Ahead Days: 7. All codes are invented.

Check the arithmetic by hand: 15 minus 12 is 3. October 30 is 18 days after October 12. The 10th is 2 days before the 12th. The 5th is 7 days before the 12th. The 19th is 7 days after the 12th. The 12th is today.

Summary row result:

• Action Count: 1 OVERDUE + 1 Missing date + 3 DUE SOON = 5
• Overdue Count: 1
• Digest Lines (in sheet order):

P104 | Client B | Copyedit | due 2026-10-15, in 3 days | DUE SOON
P107 | Client C | First proofs | due 2026-10-10, 2 days overdue | OVERDUE
P109 | Client B | Cleanup | no date | Missing date
P111 | Client C | Index check | due 2026-10-19, in 7 days | DUE SOON
P112 | Client D | Other | due 2026-10-12, today | DUE SOON

The email: subject "Deadline digest: 5 to act on, 1 overdue", sent to you on Monday morning, with those five lines as the body. P104's author review (Later), P102 (Closed) and the blank row are absent, as intended.

Time saved: the Monday sweep through inboxes and a calendar becomes a short read of one email. The build itself is a one-time cost.

What to Do When It Breaks

• No email arrived on Monday. This is the silent failure, because nothing alerts you. Check in order: is the Zap switched on in Zapier, did the Key cell (Summary!A2) stay exactly as the word summary, and has the Google connection expired (Zapier shows a reconnect prompt on the Zap or in its settings). Then open Zap History and look for a run on that day. A "Safely halted" status on the lookup step means the Key cell no longer matches. A scheduled day with no run at all means the Zap was off.
• Check the digest is alive on purpose. Put a calendar reminder for the first Monday of each month: "Did the deadline digest arrive in the last four weeks?"
• The counts look a day old. Open the sheet, let it recalculate, and confirm Calculation is Automatic. Do not look for a scheduled recalculation setting, because Google does not list one.
• A date prints as a five-digit number. The Digest Line formula is missing its TEXT wrapper, or Due Date was typed as text. Re-enter the date or re-copy the formula.
• The Flag is wrong for a blank Due Date. The Missing date test must sit before the Days Left tests. Compare your formula against Part 2.
• A stage you just entered is missing. Check that the formulas were filled down to that row and that the row is within row 200.
• The email body is one long line. Choose plain text as the body type in the Gmail step, or open the email in plain-text view.

Variations

• Simpler version: drop the Missing date and Closed logic, and flag only OVERDUE and DUE SOON. You will see fewer lines, and you will not notice a stage with no date.
• Extended version: add a second Zap on the same Summary row with a Monday afternoon time as a reminder, still to yourself. Or build the follow-up digest for queries and files (the next guide in this series), which lives in its own spreadsheet.

What to Do Next

• This week: enter every active stage with a real date, run the Zap test, and send the first digest.
• This month: adjust Look Ahead Days to match how far ahead you plan. A client with long turnaround may deserve a longer window.
• Advanced: pair this with the Level 4 follow-up digest and invoice digest, each in its own spreadsheet, so each Monday brings three short emails and no cross-contamination of columns.

Advanced guide for Copyeditors and Proofreaders. Zapier's menus and plan limits change. If a step looks different from this guide, search for the event name on the app's Zapier integrations page.