Query and File Follow-Up Digest: A Weekly Email of Everything You Are Waiting On

For Copyeditors and Proofreaders

Tools: Zapier + Google Sheets + Gmail | Time to build: 1 to 2 hours | Difficulty: Advanced Prerequisites: basic comfort with Google Sheets formulas and the Level 1 late-file follow-up email prompt (the digest tells you who to nudge, and that prompt helps you write the nudge)

What This Builds

You sent a batch of queries to an author nine days ago. A production editor promised the final art log by the end of last week. A sign-off on the cover copy was due and has not come. Each of these is a small open loop, and a project stalls when nobody remembers to ask.

This build gives every loop a row in a sheet and sends you one email each week that lists only the ones that have waited longer than you decided is reasonable, plus any row with no date. You write and send every nudge yourself. The automation never contacts an author, an editor or a client. It also uses no AI, so no client information goes to an AI vendor: the sheet builds the digest, and Zapier carries it to your own inbox.

Prerequisites

• A new Google Sheet for this digest alone (its columns differ from the deadline and invoice sheets, so do not share one file)
• 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 come with the Google account you already use, and nothing in this build is an AI subscription. If you run a single Zapier plan for all three digest builds in this series, it is one subscription, not three.

The Concept

Picture a clerk who reads you one index card each week. The clerk cannot look through your filing cabinet, only at the card, so the card has to be written every week even when it says "nothing to report".

Your sheet writes the card. A Summary tab holds one row that always exists, and Zapier reads exactly that row and emails it to you. This matters because a Zapier lookup that finds no row stops the Zap with a "Safely halted" status in Zap History. No later step runs, so no email goes out. If the Zap searched for overdue items directly, a week with nothing overdue would produce silence, and you could not tell a good week from a dead Zap. With a Summary row, the email always comes.

What Zapier Sees and Keeps

Zap History stores the data each step received and returned. For this Zap that is the Summary row (the Nudge After Days setting, the Action Count and the full Digest Lines text), plus the email address and subject used by the Gmail step. Google holds the sheet and the email as well.

So put only these in the sheet:

• A project code such as "P104" and a short item label such as "Query batch 2" or "Final art log"
• The item type, a date and a status
• Never an unpublished title, an author's name, a confidential client's name, text from the manuscript, or a query's wording

A query batch is just "Query batch 2". What the queries say stays in your Word file. Check the client contract or the publisher's policy before you start, because some contracts speak even to coded data kept in outside services. If a client says no, track that client's follow-ups elsewhere.

Build It Step by Step

Part 1: Lay Out the Sheet

Create two tabs named exactly Follow-ups and Summary. The formulas refer to those names.

Follow-ups tab, row 1 headers:

Format column D as a date and column F as a plain number. Dropdowns come from the Insert menu in Sheets.

Summary tab, row 1 headers with one data row in row 2:

Zapier reads row 1 as field names, so keep these headers as written. Nudge After Days is your settings cell.

Part 2: Add the Formulas

Enter each formula in row 2 and fill down to row 200. If you will need more rows, change every 200 in every formula to the same larger number.

Follow-ups!F2 (Days Waiting):

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

Blank when the item or the since date is blank. A positive number is days waited. A negative number means the date is still in the future.

Follow-ups!G2 (Flag):

=IF(B2="","",IF(OR(E2="Received",E2="Withdrawn"),"Closed",IF(D2="","Missing date",IF(F2<0,"Not due yet",IF(F2>=Summary!$B$2,"NUDGE","Waiting")))))

Follow-ups!H2 (Digest Line):

=IF(B2="","",A2&" | "&B2&" | "&C2&" | "&IF(D2="","no date","since "&TEXT(D2,"yyyy-mm-dd")&", "&F2&IF(F2=1," day"," days"))&" | "&G2)

TEXT turns the date into readable text. Without it, a date joined into a sentence prints as a serial number such as 46300.

Summary!C2 (Action Count):

=COUNTIF('Follow-ups'!$G$2:$G$200,"NUDGE")+COUNTIF('Follow-ups'!$G$2:$G$200,"Missing date")

The tab name has a hyphen, so Sheets needs the single quotes around it.

Summary!D2 (Digest Lines):

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER('Follow-ups'!$H$2:$H$200,('Follow-ups'!$G$2:$G$200="NUDGE")+('Follow-ups'!$G$2:$G$200="Missing date")>0)),"Nothing waiting past your nudge window")

FILTER keeps the Digest Line of each NUDGE or Missing date row, and TEXTJOIN stacks them with line breaks. When nothing matches, FILTER returns an error, and IFERROR swaps in the fallback sentence, so a quiet week still produces a readable email. Every range runs from row 2 to row 200, so they have equal height.

The Flag formula reads Nudge After Days from Summary!$B$2, so changing that one cell changes every row.

Part 3: Walk the Flag Formula Through Each Case

Assume Nudge After Days is 5 and today is 2026-10-12.

• A blank row. Item is blank, so the first test returns an empty string, and so do the other columns. COUNTIF looks only for the labels NUDGE and Missing date, so blank rows are never counted.
• Closed. Status is Received or Withdrawn, so the Flag is Closed, however old the Since Date. Closed is tested early so a settled item does not keep reappearing as a nudge.
• Missing date. The item exists, the status is Waiting and Since Date is blank. This test comes before the day tests because a blank Days Waiting is empty text, and Sheets treats empty text as larger than any number, which would otherwise label the row NUDGE by mistake.
• Not due yet. Since Date is a promised date still ahead of you, for example 2026-10-14. Days Waiting is -2. Below zero, so Not due yet. It is not in the digest.
• NUDGE. Since Date 2026-10-05 gives 7 days, which is at least 5. A Since Date of 2026-10-07 gives exactly 5, and the test is "at least", so it also says NUDGE.
• Waiting. Since Date 2026-10-09 gives 3 days. Below 5 and not negative, so Waiting.

Part 4: Build the Zap

Open zapier.com, create a new Zap and add three steps. Check each event name against the list Zapier shows. These names were current on 2026-10-09.

Step 1. Trigger: Schedule by Zapier, event Every Week. Choose the day and the time of day. Monday morning works well. Test the trigger.

Step 2. Action: Google Sheets, event Lookup Spreadsheet Row. Connect your Google account, choose this spreadsheet and the Summary worksheet. Set the lookup column to Key and the lookup value to summary. Leave off any option that creates a row when none is found. Test: Zapier should show one row with fields named Nudge After Days, Action Count and Digest Lines.

Step 3. Action: Gmail, event Send Email.

• To: your own address, typed in. No one else.
• Subject: build it from three pieces in this order: the text "Follow-up digest: ", the Action Count field from Step 2, and the text " to chase".
• Body: insert the Digest Lines field from Step 2. If the step offers a body type, choose plain text so the line breaks stay.

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

Each run uses its successful action steps (the lookup and the send) as tasks. The schedule trigger is not counted. Your plan's task allowance is on Zapier's pricing page.

Part 5: Send the Nudges Yourself

When a line appears in the digest, write the message. The Level 1 late-file follow-up prompt gives you a courteous draft from a project code, the missing item, the promised date and what slips. Read it, adjust it to your voice and send it from your own mail. Then change the row's Status when the item arrives, or update Since Date if you agree a new date.

Part 6: Keep the Sheet Honest

• TODAY() is recalculated by Sheets, not by Zapier. Google's calculation settings offer Automatic and Manual modes and no scheduled recalculation, so keep Calculation Mode on Automatic, and open the sheet before the run if the counts look a day behind.
• Cut Received and Withdrawn rows to an Archive tab now and then so the 200 rows last.

Real Example

Invented date for TODAY(): Monday 2026-10-12. Nudge After Days: 5. All codes and labels are invented.

Check each difference: the 12th minus the 5th is 7. The 12th minus the 9th is 3. The 14th is 2 days after the 12th, so -2. September 28 to September 30 is 2 days, plus 12 days in October, which is 14. The 12th minus the 7th is 5.

Summary row result:

• Action Count: 2 NUDGE + 1 Missing date = 3
• Digest Lines (in sheet order):

P104 | Query batch 2 | Query answers | since 2026-10-05, 7 days | NUDGE
P111 | Cover copy sign-off | Approval | no date | Missing date
P112 | Index approval | Approval | since 2026-10-07, 5 days | NUDGE

The email: subject "Follow-up digest: 3 to chase", sent to you on Monday morning with those three lines as the body. P107 is still inside its window, P109 is not due yet, and P102 is closed, so none of them appear. You then write two nudges and fix one missing date yourself.

What to Do When It Breaks

• No email arrived. This failure is silent. Check that the Zap is switched on, that Summary!A2 still holds exactly the word summary, and that the Google connection has not expired. Open Zap History and look for a run on the scheduled day. A "Safely halted" status on the lookup step means the Key no longer matches. No run at all means the Zap was off.
• Set a check on the check. A monthly calendar reminder that asks "Did the follow-up digest arrive in the last four weeks?" catches a dead Zap before it costs you a missed nudge.
• A date prints as a five-digit number. The Digest Line formula has lost its TEXT wrapper, or the Since Date was typed as text. Re-enter the date.
• A blank date shows as NUDGE. The Missing date test sits after the day tests in your Flag formula. Move it ahead of them as in Part 2.
• The counts look a day old. Open the sheet, let it recalculate and confirm Calculation Mode is Automatic.
• The formulas return a sheet-name error. The tab name is not exactly Follow-ups, or the single quotes are missing in the Summary formulas.

Variations

• Simpler version: track only query batches, and delete the Item Type column.
• Extended version: run a second schedule for the same Summary row on Thursday so a missed Monday email does not cost a week. The reader is still the only recipient.

What to Do Next

• This week: enter every open loop with a Since Date and run the test email.
• This month: adjust Nudge After Days to fit how each kind of item behaves in practice. Authors and production departments move at different speeds.
• Advanced: combine with the deadline digest and the invoice follow-up digest, each in its own spreadsheet.

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.