Zapier and Google Sheets: A Weekly Digest of Annual Reviews That Need Prep

What This Builds

Once a week, an email arrives in your own work inbox listing every annual review that is inside your prep window and not yet ready: documents still outstanding, packet not finished, review dates that have passed without the status being updated, and reviews with no date at all. It also lists the reviews that are ready, so you can see what is done as well as what is not.

You choose the prep window. At 21 days, a review three weeks away shows up as soon as it enters the window. The sheet does the date arithmetic and Zapier mails the result.

The automation sends nothing to clients and schedules no meetings. 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
• 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 updating Docs Status, Packet Status and Meeting Status when they change

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 from your sheet and hands it to Gmail. Zap History stores the data from each step, so the whole digest text is kept by Zapier.

What flows through: household codes such as HH-201, advisor initials, review dates, and the status words from the dropdowns.

What stays out of the sheet: client names, account numbers, Social Security numbers, dates of birth, addresses, email addresses and balances. Use household codes 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 lands in your work mailbox, which the firm archives.

The Concept

Picture a colleague who reads your review calendar every Monday and leaves one sticky note on your monitor. The Summary tab is that sticky note. It always has one row, and the row always says something, even if the note reads "No reviews in the prep window".

The reason for a fixed row is how Zapier handles searches. When a lookup step finds nothing, the Zap halts, Zap History shows "Safely halted", and no email goes out. You would never see the failure. Because the Summary row always exists, the lookup always succeeds, so the email arrives every week whatever the review list looks like.

All the date logic lives in the sheet. Zapier does not compare dates, count statuses or filter rows.

Build It Step by Step

Part 1: Lay Out the Tabs

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

Reviews tab (headers in row 1, data from row 2):

Create the dropdowns with Data, Data validation, Dropdown, typing the values exactly as shown. Format column G as a plain number, because a date minus a date can come out looking like a date.

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

Cell B2 is your setting. Change it whenever you want a longer or shorter lead time. It must hold a number, because a blank would be read as zero days.

Part 2: The Days Until Formula

G2, filled down to row 200:

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

The result is blank when the household or the review date is missing, so empty rows and rows without a date never produce a number. A review today gives 0, a review next week gives 7 and a review last week gives -7.

Part 3: The Stage Formula

H2, filled down to row 200:

=IF(A2="","",IF(OR(F2="Held",F2="Cancelled"),"Closed",IF(C2="","Missing review date",IF(G2<0,"DATE PASSED, update status",IF(G2<=Summary!$B$2,IF(D2<>"All received","Docs outstanding",IF(E2<>"Advisor reviewed","Packet not ready","Ready")),"Later")))))

The tests run in this order, and the first one that is true wins:

1. Household blank: the row is empty, so the cell stays blank.
2. Meeting Status Held or Cancelled: "Closed". Finished reviews leave first, so an old review date on a held meeting never raises a flag.
3. Review Date blank: "Missing review date".
4. Days Until below 0: "DATE PASSED, update status". The meeting date is behind you and nobody changed the status.
5. Days Until at most the Prep Window Days: the review is inside the window. If Docs Status is anything other than All received, "Docs outstanding". Otherwise, if Packet Status is anything other than Advisor reviewed, "Packet not ready". Otherwise "Ready".
6. Anything further out: "Later".

Walk through the awkward case. A review 14 days away with Docs Status All received and Packet Status Drafted. Test 5 is true because 14 is at most 21. Docs Status equals All received, so the docs test fails and the formula moves on. Packet Status is Drafted, which is not Advisor reviewed, so the result is "Packet not ready". It is not "Ready" until the packet is marked Advisor reviewed.

Check the parentheses. The formula nests five outer IF functions plus two inner ones. The two inner ones close right after "Ready", and the five closing parentheses at the very end close the outer five after "Later".

Part 4: The Digest Line Formula

I2, filled down to row 200:

=IF(A2="","",A2&" | "&B2&" | "&IF(C2="","review date missing","review "&TEXT(C2,"yyyy-mm-dd")&IF(G2<0,", "&ABS(G2)&" day(s) ago",", in "&G2&" day(s)"))&" | docs: "&D2&" | packet: "&E2&" | "&H2)

The review date goes through TEXT so it prints as 2026-10-26 rather than a serial number. The blank-date guard comes first, so a missing date prints "review date missing" and never touches G2. A past review prints "N day(s) ago" and a future one "in N day(s)".

Part 5: The Summary Row Formulas

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

C2, Action Count (DATE PASSED plus Missing review date plus Docs outstanding plus Packet not ready):

=COUNTIF(Reviews!$H$2:$H$200,"DATE PASSED, update status")+COUNTIF(Reviews!$H$2:$H$200,"Missing review date")+COUNTIF(Reviews!$H$2:$H$200,"Docs outstanding")+COUNTIF(Reviews!$H$2:$H$200,"Packet not ready")

D2, Ready Count:

=COUNTIF(Reviews!$H$2:$H$200,"Ready")

E2, Digest Lines (the four action labels plus Ready, one line each):

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Reviews!$I$2:$I$200,(Reviews!$H$2:$H$200="DATE PASSED, update status")+(Reviews!$H$2:$H$200="Missing review date")+(Reviews!$H$2:$H$200="Docs outstanding")+(Reviews!$H$2:$H$200="Packet not ready")+(Reviews!$H$2:$H$200="Ready"))),"No reviews in the prep window")

All ranges in the FILTER are rows 2 to 200, so they are the same height. The tests are added together, which acts as OR. When nothing matches, FILTER errors and IFERROR returns the fallback sentence. There is no circular reference: Reviews column H reads the typed number in Summary B2, and the Summary formulas read Reviews column H and I only.

Turn on text wrapping in E2 and enter a few test rows to see it work.

Part 6: Build the Zap

Create a new Zap with three steps.

1. Trigger: Schedule by Zapier, event Every Week. Day of the Week: Monday (or whichever day you plan your week). Time of Day: early morning. Set the Timezone Override if needed.

2. Action: Google Sheets, event Lookup Spreadsheet Row. Pick your Google account and the 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 Prep Window Days, Action Count, Ready 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 Review prep digest: , then the Action Count field from step 2, then the text reviews need action.
   • Body type: plain text, so the line breaks in Digest Lines survive.
   • Body: the text Prep window in days: , then the Prep Window Days field, then the text . Ready: , then the Ready Count field, then two line breaks, then the Digest Lines field.

   Test the step and check your inbox.

Publish the Zap. 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 weekly run and check your plan's task allowance.

Part 7: Test and Refine

• Move one review date into the window and run the Zap test again to see it appear.
• Set every Meeting Status to Held. You should receive "No reviews in the prep window" with a subject ending "0 reviews need action". That confirms the email is sent when there is nothing to report.
• Adjust Prep Window Days in Summary B2 until the list length suits how you work.

Real Example: Seven Reviews, Prep Window 21 Days

Invented example. TODAY() is 2026-10-12 and Prep Window Days is 21, so the window closes on 2026-11-02. All households and initials are invented.

Row 3 is the case to study. The review is exactly 21 days away, which is at most 21, so it is inside the window. Docs are all received, but the packet is only Drafted, so it reads "Packet not ready". Row 5 is 57 days out (19 days left in October, 30 in November, 8 in December), so it is Later. Row 8 has a Days Until of -13 but is Closed because the meeting was held. Row 7 has no Days Until at all.

Action Count: one DATE PASSED, one Missing review date, one Docs outstanding and one Packet not ready, which is 4. Ready Count: 1.

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

HH-201 | JR | review 2026-10-26, in 14 day(s) | docs: Requested | packet: Not started | Docs outstanding
HH-205 | MK | review 2026-11-02, in 21 day(s) | docs: All received | packet: Drafted | Packet not ready
HH-209 | JR | review 2026-10-19, in 7 day(s) | docs: All received | packet: Advisor reviewed | Ready
HH-215 | JR | review 2026-10-05, 7 day(s) ago | docs: Partly received | packet: Drafted | DATE PASSED, update status
HH-218 | MK | review date missing | docs: Not requested | packet: Not started | Missing review date

The email: subject "Review prep digest: 4 reviews need action", body "Prep window in days: 21. Ready: 1" and the five lines above.

Time saved: the weekly walk through every household to see whose review is coming and what is still missing becomes a short email. The saving grows with the number of households you support.

What to Do When It Breaks

• A week passes with no email (the silent failure). The digest is designed to arrive weekly, 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 your Google connection expired, so that you need to reconnect it in Zapier? Also check spam.
• Everything shows Later or nothing is in the window. Prep Window Days in Summary B2 is blank, text or too small. Type a number.
• A review shows DATE PASSED after it happened. The Meeting Status is still Scheduled. Change it to Held and the row closes.
• Days Until looks like a date such as 1900-01-14. Column G is formatted as a date. Format it as a number.
• Digest Lines run together. Body type is not plain text.
• A new household is missing from the digest. It sits below row 200 or the formulas in G to I were not filled down to that row.

Variations

• Simpler version: drop the Ready lines by removing the Ready test from the FILTER and keep only the action labels.
• Extended version: build the paperwork or document digests with the same pattern, each in its own spreadsheet file with its own Summary tab and Zap. The two companion guides show the layouts.

What to Do Next

• This week: build it and compare the first email against your own review calendar.
• This month: try a second prep window value and see which one fits how long a packet really takes.
• Advanced: add the document collection digest as its own spreadsheet and Zap so documents and reviews each get their own email.

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.