Zapier and Google Sheets: A Monday Digest of Custodian Paperwork That Needs Action

What This Builds

Every Monday morning an email lands in your own work inbox with one subject line that tells you how many pieces of custodian paperwork need you this week, and a list of exactly those items: the NIGO rejections to fix, the signed forms waiting to be submitted, the transfers nobody has followed up on. You keep one tab updated as paperwork moves. The sheet decides what needs action and Zapier carries the finished list to your inbox.

Nothing in this build writes to a client, an advisor's client, a custodian or any outside professional. The email goes to you only, 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
• Written confirmation from your firm's chief compliance officer (CCO) that Zapier and this Google account may hold the coded data described in the next section
• About ten minutes a week to keep the Status column honest

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

This build moves data through a third-party automation service, so decide first what goes in the sheet. Zapier reads the Summary row and passes it to Gmail, and Zap History stores the data from each step. Whatever is in the Digest Lines cell sits in Zap History, so the digest text itself is stored by Zapier.

What flows through: household codes such as HH-112, short item labels such as "Transfer, Client A Rollover IRA", status values, form types and follow-up dates.

What stays out of the sheet entirely: client names, account numbers, Social Security numbers, dates of birth, addresses, email addresses and balances. Use household codes and account labels you invented. Ask your CCO whether Zapier and the Google account may hold even this coded data, and ask how long Zap History should be kept. The digest email itself lands in your work mailbox, which the firm archives.

The Concept

Think of the sheet as a clerk who reads your tracker every morning and writes one note on a sticky pad. The Summary tab is that sticky pad. It always has exactly one row, and that row always has something in it, even if the note says "nothing needs action". Zapier's only job is to pick up the pad on Monday and mail it to you.

That design matters. A Zapier lookup step that finds no matching row stops the Zap, and Zap History records it as "Safely halted". No later step runs, so no email goes out and you never learn that anything happened. Because the Summary row always exists, the lookup always finds it and the email goes out every week, including the weeks when the list is empty.

The sheet also does all the thinking. Zapier is never asked to compare dates, filter statuses or count rows.

Build It Step by Step

Part 1: Lay Out the Tabs

You need two tabs. The tab names matter because the formulas and the Zap refer to them. Create a spreadsheet and name the tabs Paperwork and Summary exactly.

Paperwork tab (one row per piece of custodian paperwork, headers in row 1, data from row 2):

Set up the dropdowns with Data, Data validation, Dropdown, and type the values exactly as shown. The formulas match these words exactly, so a dropdown prevents typos. Format columns D and E as dates.

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

Part 2: The Stage Formula

Put this in cell G2 of the Paperwork tab and fill it down to row 200:

=IF(B2="","",IF(OR(F2="Complete",F2="Cancelled"),"Closed",IF(F2="NIGO","NIGO, fix and resubmit",IF(F2="Signed","Signed, ready to submit",IF(F2="Not started","Not started",IF(OR(F2="Sent for signature",F2="Submitted"),IF(E2="","Missing follow-up date",IF(E2<=TODAY(),"FOLLOW UP","Waiting")),"Not started"))))))

Read it from the outside in. If Item is blank, the row is empty and the cell stays blank. Next comes the Closed test, then one test per status. Only the two statuses that mean "waiting on someone else" (Sent for signature and Submitted) look at the follow-up date. A Status you left blank on a filled row falls through to the last "Not started", which is a reasonable place for it to land.

Walk it through one row per label:

• Item blank. The first test succeeds and the cell returns nothing.
• Status Complete or Cancelled. Returns "Closed".
• Status NIGO. Returns "NIGO, fix and resubmit".
• Status Signed. Returns "Signed, ready to submit".
• Status Not started. Returns "Not started".
• Status Submitted, Follow-up Due blank. Returns "Missing follow-up date".
• Status Sent for signature, Follow-up Due on or before today. Returns "FOLLOW UP".
• Status Submitted, Follow-up Due after today. Returns "Waiting".

Closed is tested first because finished rows should leave the formula before any date logic touches them. A Complete row usually still has an old follow-up date in column E. If a date test ever ran ahead of the Closed test, that row would show FOLLOW UP forever. Keeping Closed first also means you can add new date-based rules later without having to think about finished rows.

Check the parentheses once. The formula nests eight IF functions. The two innermost close right after "Waiting", then "Not started" is the fallback for the sixth, and six closing parentheses finish the remaining six. If Sheets complains, retype the ending.

Part 3: The Digest Line Formula

Put this in H2 and fill it down to row 200:

=IF(B2="","",A2&" | "&B2&" | "&F2&" | "&IF(E2="","no follow-up date","follow-up "&TEXT(E2,"yyyy-mm-dd"))&" | "&G2)

It joins Household, Item, Status, the follow-up date and the Stage with " | ". The date is wrapped in TEXT because a date joined into text without it prints as a serial number such as 46300. The blank-date guard sits in front of the TEXT call, so an empty Follow-up Due prints "no follow-up date". When Item is blank the whole cell stays blank.

Part 4: The Summary Row Formulas

Put each of these in row 2 of the Summary tab. The ranges end at row 200 so they cover the filled-down formulas. Blank rows return an empty string in column G, which matches none of the labels, so they are never counted.

B2, Action Count (the five action labels added together):

=COUNTIF(Paperwork!$G$2:$G$200,"NIGO, fix and resubmit")+COUNTIF(Paperwork!$G$2:$G$200,"Signed, ready to submit")+COUNTIF(Paperwork!$G$2:$G$200,"Not started")+COUNTIF(Paperwork!$G$2:$G$200,"Missing follow-up date")+COUNTIF(Paperwork!$G$2:$G$200,"FOLLOW UP")

C2, Open Count (the action count plus the rows that are simply waiting):

=B2+COUNTIF(Paperwork!$G$2:$G$200,"Waiting")

D2, Digest Lines (the Digest Line text of every action row, one per line):

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Paperwork!$H$2:$H$200,(Paperwork!$G$2:$G$200="NIGO, fix and resubmit")+(Paperwork!$G$2:$G$200="Signed, ready to submit")+(Paperwork!$G$2:$G$200="Not started")+(Paperwork!$G$2:$G$200="Missing follow-up date")+(Paperwork!$G$2:$G$200="FOLLOW UP"))),"Nothing needs action this week")

The commas inside the label strings are fine, because they sit inside quotation marks. Every range in the FILTER is rows 2 to 200, so all the ranges are the same height, which FILTER requires. The five tests are added together, which works as OR. When no row matches, FILTER returns an error and IFERROR substitutes the fallback sentence. TEXTJOIN with TRUE skips the empty strings.

Turn on text wrapping in D2 so you can read the result, then type a few test rows into the Paperwork tab and watch the Summary row change.

Part 5: Build the Zap

Open Zapier and create a new Zap with three steps.

1. Trigger: Schedule by Zapier, event Every Week. Set Day of the Week to Monday and Time of Day to early morning. Set the Timezone Override if your firm sits in a different time zone from your Zapier account.

2. Action: Google Sheets, event Lookup Spreadsheet Row. Connect your Google account, choose the spreadsheet and set Worksheet to Summary. Set Lookup column to Key and Lookup value to summary. Leave any option that creates a row when nothing is found switched off, because nothing should ever be written to your sheet. Test the step. The result should show Action Count, Open Count and Digest Lines. If a column is missing, use the refresh-fields option in the step and test again.

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

   • To: your own work address, typed in. Leave Cc and Bcc empty.
   • Subject: the text Paperwork digest: , then the Action Count field from step 2, then the text items need action.
   • Body type: plain text, so the line breaks in Digest Lines survive.
   • Body: the text Open items including waiting: , then the Open Count field, then two line breaks, then the Digest Lines field.

   Test the step and check your inbox.

Turn the Zap on. Zapier's task usage page counts successful action steps as tasks and does not count triggers, and it ties the cost of a search step to its "proceed if nothing found" setting. Treat the run as up to two tasks per week and check your plan's task allowance.

Part 6: Test and Refine

• Change one Status in the sheet and run the Zap's test again to see the email change.
• Check that the email reads in a sensible order. Lines appear in the order of the rows on the Paperwork tab.
• Run the Zap with every row set to Complete. You should get "Nothing needs action this week" and a subject ending "0 items need action". That is the proof the email is sent every week even when the list is empty.

Update Status by hand whenever paperwork moves. The digest is only as current as the tab.

Real Example: A Monday With Seven Open Items

Invented example. TODAY() is 2026-10-12, a Monday. All households and items are invented.

Row 2: Submitted, and 2026-10-05 is before 2026-10-12, so FOLLOW UP. Row 3: Sent for signature, and 2026-10-20 is after today, so Waiting. Row 8 is Closed even though its follow-up date has passed. Row 9 is empty and returns blank in G and H.

Action Count: one NIGO, one Signed, one Not started, one Missing follow-up date and one FOLLOW UP, which is 5. Open Count: 5 plus one Waiting row, which is 6. Row 8 is Closed and row 9 is blank, so neither is counted.

Digest Lines (rows in sheet order, rows 2, 4, 5, 6 and 7):

HH-112 | Transfer, Client A Rollover IRA | Submitted | follow-up 2026-10-05 | FOLLOW UP
HH-125 | Account opening, Joint brokerage | NIGO | follow-up 2026-10-09 | NIGO, fix and resubmit
HH-131 | Distribution, Client A Individual IRA | Signed | follow-up 2026-10-15 | Signed, ready to submit
HH-140 | Transfer, Client B 403(b) | Not started | no follow-up date | Not started
HH-144 | Account opening, Trust account | Sent for signature | no follow-up date | Missing follow-up date

The email: subject "Paperwork digest: 5 items need action", body "Open items including waiting: 6" followed by the five lines above.

Time saved: the Monday scan down the whole tracker, checking each follow-up date by eye, becomes a read of one short email. The time you save depends on how long the tracker is. The real gain is that a NIGO row or a missed follow-up cannot sit unnoticed.

What to Do When It Breaks

• A Monday passes and no email arrives (the silent failure). The digest is built to arrive every week, so a missing email means the Zap did not run to the end. Check four things in order. First, is the Zap switched on? A Zap can end up switched off after repeated errors or by accident. Second, open Zap History and look for a "Safely halted" or error entry on Monday. Third, check that the Summary tab still has the word summary in A2 and has not been renamed or had its row deleted. Fourth, reconnect your Google account under Zapier's connections if the connection has expired. Also look in spam.
• The email arrives but says "Nothing needs action this week" when you know a row is NIGO. Check the Status spelling against the dropdown, check the row has an Item, and check the row is within rows 2 to 200. A row someone typed over a formula in column G will not count, so copy the formula back down.
• The Digest Lines run together on one line. Body type is probably set to HTML. Switch it to plain text and test again.
• Action Count shows an error. One of the COUNTIF ranges lost its sheet name after a tab rename. Re-enter the formulas from this guide.
• A date shows as a five-digit number in the email. The TEXT wrapper was removed from the Digest Line formula. Restore it.
• Zapier shows old column names. Refresh the fields in the lookup step after you rename any header.

Variations

• Simpler version: drop the Open Count line and send only the Action Count and the Digest Lines.
• Extended version: build the same pattern for other trackers, each in its own spreadsheet file with its own Summary tab and its own Zap. The annual review prep digest and the daily document collection digest guides use the same pattern.

What to Do Next

• This week: build it, run it for a Monday, and compare the email against your own scan of the tracker.
• This month: tune the Follow-up Due habit. A blank follow-up date on a submitted form is flagged every week until you set one.
• Advanced: add the review and document digests as further 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.