Zapier and Google Sheets: A Daily Digest of Client Documents to Chase

What This Builds

Every weekday morning you get one email in your own work inbox that lists the documents you requested from client households and have waited too long for. Each line shows the household, the document, how many days ago you asked, and a CHASE flag. When nothing is overdue, the email says so in one line.

The tracker is a single tab. You mark a document received when it arrives, and the sheet works out what is overdue from your own "chase after" number. Zapier delivers the result. You write each chase email yourself, and the lead advisor approves it before it goes out through the firm's archived email. The automation never contacts a client, and no AI service reads any of it.

Prerequisites

• A Google account with Google Sheets and Gmail (Business Standard, $14/user/month). Your firm may already provide this
• A Professional Zapier plan ($29.99/month). The Zap has a trigger plus two action steps, and Zapier's free plan has been limited to two-step Zaps, so check the current plan page before you start
• Your firm's chief compliance officer (CCO) has confirmed that Zapier and this Google account may hold the coded data described in the next section
• A habit of marking each document received the day it arrives

Total ongoing cost: one Zapier plan ($29.99/month) plus the Google account you already use ($14/user/month if you pay for it separately). There is no AI subscription in this build.

What Zapier Sees and Keeps

Zapier reads one Summary row and passes it to Gmail. Zap History stores the data from every step, so each day's digest text sits in Zapier's history.

What flows through: household codes such as HH-301, short document labels such as "2025 tax return" or "Client B pension statement", request dates, and the words Yes, No and Not needed.

What stays out of the sheet: client names, account numbers, Social Security numbers, dates of birth, addresses, email addresses, balances and any figure from a document. Label documents by type and role only. Ask your CCO whether Zapier and the Google account may hold even this coded data and how long Zap History should keep it. The digest email lands in your work mailbox, which the firm archives.

The Concept

Think of a tickler file that sorts itself. Each requested document is a card with a date on it, and every morning someone flips through the deck and hands you only the cards that have waited too long. The Summary tab is the stack of cards they hand you. It always has one row, and the row always has text, even if the text is "No documents to chase today".

That fixed row is what keeps the automation honest. When a Zapier lookup step finds no matching row, the Zap halts with "Safely halted" in Zap History, no later step runs and no email is sent. If the lookup targeted overdue documents directly, every quiet day would halt the Zap and you would not be able to tell a quiet day from a broken Zap. Because the lookup reads the Summary row, which always exists, the email arrives every day.

The sheet does the thinking: days waiting, overdue tests and counts. Zapier receives finished text.

Build It Step by Step

Part 1: Lay Out the Tabs

Create a spreadsheet with two tabs named exactly Documents and Summary.

Documents tab (one row per requested document, headers in row 1, data from row 2):

Make the dropdown with Data, Data validation, Dropdown and type the three values exactly. Format column E as a plain number.

Summary tab (headers in row 1, one data row in row 2):

Cell B2 is your setting. A document waiting that many days or more is flagged.

Part 2: The Days Waiting Formula

E2, filled down to row 200:

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

It stays blank when the Document or the Requested On date is blank, so empty rows and undated rows never produce a number. A document requested today gives 0.

Part 3: The Stage Formula

F2, filled down to row 200:

=IF(B2="","",IF(OR(D2="Yes",D2="Not needed"),"Closed",IF(C2="","Missing request date",IF(E2>=Summary!$B$2,"CHASE","Waiting"))))

The first test that is true wins:

1. Document blank: the cell stays blank.
2. Received is Yes or Not needed: "Closed". Closed is tested before any date, so a document that arrived late does not keep showing up as overdue.
3. Requested On blank: "Missing request date". Without a date the sheet cannot tell how long you have waited, so it asks you to fix the row.
4. Days Waiting at least Chase After Days: "CHASE".
5. Otherwise: "Waiting".

A Received cell left blank is treated like No, so the document stays in play. Check the parentheses: four IF functions open and four close at the end after "Waiting".

Part 4: The Digest Line Formula

G2, filled down to row 200:

=IF(B2="","",A2&" | "&B2&" | "&IF(C2="","request date missing","requested "&E2&" day(s) ago")&" | "&F2)

The blank-date guard makes a row with no date print "request date missing" instead of a calculation. When Document is blank the cell stays blank.

Part 5: The Summary Row Formulas

Row 2 of the Summary tab. Ranges run to row 200 and blank rows give an empty string in column F, which matches no label.

C2, Action Count (CHASE plus Missing request date):

=COUNTIF(Documents!$F$2:$F$200,"CHASE")+COUNTIF(Documents!$F$2:$F$200,"Missing request date")

D2, Waiting Count:

=COUNTIF(Documents!$F$2:$F$200,"Waiting")

E2, Digest Lines:

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Documents!$G$2:$G$200,(Documents!$F$2:$F$200="CHASE")+(Documents!$F$2:$F$200="Missing request date"))),"No documents to chase today")

Both FILTER ranges are rows 2 to 200, so they are the same height. The two tests are added together to act as OR. When nothing matches, FILTER errors and IFERROR returns the fallback sentence. Reading Summary B2 from the Documents tab and the Documents tab from the Summary tab creates no loop, because B2 is a typed number. Turn on text wrapping in E2.

Part 6: Build the Zap

Create a new Zap with three steps.

1. Trigger: Schedule by Zapier, event Every Day. Set Time of Day to early morning. The step also has a "Trigger on weekends?" field. Set it to No to run on weekdays only. Set the Timezone Override if needed.

2. Action: Google Sheets, event Lookup Spreadsheet Row. Pick your Google account and spreadsheet. Worksheet: Summary. Lookup column: Key. Lookup value: summary. Leave any option that creates a row when nothing is found switched off. Test it. You should see Chase After Days, Action Count, Waiting Count and Digest Lines. If a field is missing, refresh the fields and test again.

3. Action: Gmail, event Send Email. Fill the fields like this.

   • To: your own work address, typed in. Leave Cc and Bcc empty.
   • Subject: the text Document chase digest: , then the Action Count field from step 2, then the text to chase.
   • Body type: plain text, so the line breaks in Digest Lines survive.
   • Body: the text Chase after (days): , then the Chase After Days field, then the text . Still waiting, not yet due: , then the Waiting Count field, then two line breaks, then the Digest Lines field.

   Test and check your inbox.

Publish the Zap. A daily Zap uses tasks every day it runs. Zapier's task usage page counts successful action steps as tasks, does not count triggers, and ties a search step's cost to its "proceed if nothing found" setting. Plan for up to two tasks per run and check your plan's task allowance against the number of days the Zap runs in a month.

If you built the paperwork tracker digest first, build this one in its own spreadsheet file. Each file keeps its own tab named Summary, so the formulas here work unchanged, and each Zap points at its own file.

Part 7: Test and Refine

• Set one Requested On date far enough back to cross your Chase After Days and test the Zap again.
• Mark every document Yes. You should receive "No documents to chase today" and a subject ending "0 to chase", which shows the email is sent on quiet days.
• Adjust Chase After Days until the daily list is the length you would actually act on.

Real Example: Six Requests, Chase After 7 Days

Invented example. TODAY() is 2026-10-12 and Chase After Days is 7. All households and documents are invented.

Row 4 is the boundary case: 7 days is at least 7, so it is CHASE. Rows 6 and 7 are old but Closed, because the Received test comes before the day count.

Action Count: two CHASE rows plus one Missing request date row, which is 3. Waiting Count: 1.

Digest Lines (rows 2, 4 and 5):

HH-301 | 2025 tax return | requested 12 day(s) ago | CHASE
HH-304 | Client A 401(k) statement | requested 7 day(s) ago | CHASE
HH-307 | Life insurance policy | request date missing | Missing request date

The email: subject "Document chase digest: 3 to chase", body "Chase after (days): 7. Still waiting, not yet due: 1" and the three lines.

You then write the chase emails for the two CHASE households yourself, and for HH-307 you first find the request date.

Time saved: the morning pass through every open request disappears. What remains is the short list of documents you actually act on.

What to Do When It Breaks

• A weekday passes with no email (the silent failure). The digest is built to arrive every day, so a missing email means the Zap did not finish. Check in order. Is the Zap switched on? Does Zap History show a "Safely halted" or error entry for that morning? Does Summary A2 still say summary and is the tab still named Summary? Has the Google connection expired and need reconnecting in Zapier? Check spam too. If the Zap runs on weekends and you do not want that, the "Trigger on weekends?" field is set to Yes.
• A document you received still shows up. Received is not set to Yes or Not needed, or the cell has a typo. Use the dropdown.
• Nothing is ever flagged. Chase After Days in Summary B2 is blank, text or very large. Type a number.
• A row shows a negative number of days. The Requested On date is in the future, almost always a typed year or day wrong. Fix the date.
• Digest Lines run together. Body type is not plain text.
• Missing request date appears every day. That is intended. Fill in the date or mark the row Closed.

Variations

• Simpler version: run the Zap weekly with the Every Week trigger and set Chase After Days to a larger number.
• Extended version: copy the spreadsheet for a different group of documents, such as new-household onboarding, and add a second Zap that points at the copy.

What to Do Next

• This week: build the tab, enter your open requests and run a test.
• This month: tune Chase After Days until the list matches how you work.
• Advanced: add the weekly paperwork digest and the annual review prep digest as separate spreadsheets and Zaps.

Advanced guide for paraplanner professionals. Zapier and Google interfaces change, so match the step names here against what you see on screen. Confirm with your CCO before any client-related data goes into a third-party service.