Weekly Hearing and Interview Prep Digest: One Monday Email of Events That Still Need Prep

What This Builds

Prep meetings and exhibit sets slip as a hearing or interview date gets close, mostly because each event is on a calendar and its prep status is in someone's head. This build keeps one line per upcoming event in a small sheet and sends you one email every Monday listing the events inside your alert window where either the prep meeting has not been held or the exhibits are not ready. The outcome it serves is a prep meeting and a finished exhibit set before the event and not the night before. Past events stay quiet, and events with no date are counted so they get fixed.

Prerequisites

• A Google spreadsheet named "Hearing and Interview Prep", separate from any other tracker
• A Zapier account on a paid plan, because the Zap has three steps and the free plan allows two-step Zaps only. The plan is Professional ($29.99/month). That is the total ongoing cost, since the Google account is one you already have.
• A Google account connected to Zapier for Sheets and Gmail
• A check of your firm's policy on matter codes in an outside automation tool

The Concept

A stage manager keeps a call sheet: what is ready, what is not, and how many days remain. Nobody asks the stage manager to remember. The sheet remembers, and the stage manager reads it out each week. This build gives you that call sheet.

One technical point shapes the design. A Zapier search step that finds no row halts the Zap. Zap History shows the run as safely halted, and no email is sent. A week with no event needing prep would then look exactly like a broken Zap. So the Zap searches a Summary tab with one row that always exists. Its Key cell says summary, and formulas fill the rest of the row from the Events tab. A quiet week produces an email that says none and zero. There is no AI step, no Formatter step, no loop, no second lookup and no row trigger.

Event dates come from the notice or from the court's own record, and you type them. The alert window is a number you choose. This guide states no deadline for exhibits and no rule about preparation.

Data caution. The sheet holds matter codes, event type codes (HEARING or INTERVIEW), dates and yes or no only. Client names, A-numbers, receipt numbers, countries, case facts, the court or office name and the content of any account stay in your firm's approved systems. Zapier is an outside tool, so check whether your firm's policy allows matter codes to pass through it, and check Zapier's own pages for how long Zap History is kept. If the answer is no, use the sheet as a manual Monday checklist and skip the Zap.

Build It Step by Step

Part 1: Build the Events tab

Name the first tab Events. Lay out the columns first.

Format column C as a date. Add the dropdown to D2 through E301 (Insert, then Dropdown, or Data, then Data validation, with the list yes and no). Menu names change, so look for the closest match.

F2, filled down to row 301:

=IF(A2="","",IF(C2="","MISSING DATE",IF(C2<TODAY(),"",IF(AND(D2="yes",E2="yes"),"",IF(C2-TODAY()<=Summary!$B$4,"PREP NEEDED","")))))

Read the formula from the outside in:

• A blank row (no matter code) returns blank.
• An event with a code but no date returns MISSING DATE.
• An event dated before today returns blank. The event is past, so it drops off the list.
• An event where both the prep meeting and the exhibits are marked yes returns blank.
• An event dated today or later, no more than the alert days away, where either answer is not yes returns PREP NEEDED. A blank cell counts as not yes, and an event dated today counts.
• Everything else returns blank.

The five closing parentheses at the end match the five IF functions open at that point, and the AND has its own closing parenthesis before the comma that starts the empty text.

Part 2: Build the Summary tab

Add a second tab named Summary.

B2:

=COUNTIF(Events!F2:F301,"PREP NEEDED")

C2:

=IFERROR(TEXTJOIN(" | ",TRUE,FILTER(Events!A2:A301&" "&Events!B2:B301&" ("&TEXT(Events!C2:C301,"yyyy-mm-dd")&") prep "&Events!D2:D301&", exhibits "&Events!E2:E301,Events!F2:F301="PREP NEEDED")),"none")

D2:

=COUNTIF(Events!F2:F301,"MISSING DATE")

Check the parentheses in each formula. Every range inside the FILTER runs from row 2 to row 301, so the heights match. The dates sit inside TEXT() so they join as 2026-11-05 and not as a serial number. A row only reaches the list when it is flagged PREP NEEDED, and that flag needs a date, so TEXT() never sees a blank. IFERROR turns the empty-result error into the word none. The items are separated by a vertical bar because each item already contains a comma. The counts look for the exact flag words, so rows filled down past your last event are never counted.

If an item in the email shows nothing after the word prep, the Prep meeting held cell was left empty. Fill it with yes or no so the email shows the answer.

The value 21 is a starting choice, and it is yours to change. It is your own working window and not a rule.

Keep Calculation Mode on Automatic (File, Settings, Calculation). Google's calculation settings page offers Automatic and Manual only, with no scheduled recalculation, and TODAY() depends on recalculation. If a count looks stale, open the sheet before relying on the email. The spreadsheet's time zone (File, Settings, General) also decides what TODAY() means.

Part 3: Load the test rows

The rows below are invented. They assume a pretend TODAY() of Monday 2026-10-26 and B4 = 21. On another day, keep the same offsets from your real today.

The Summary row should then read:

In the C2 cell the two items are separated by a vertical bar. IM-26-038 shows the boundary: 21 days is within the window, so it is flagged. IM-26-040 sits at 22 days and waits. IM-26-044 shows that both answers yes clear the flag. IM-26-047 shows that a past event drops off.

Part 4: Create the Zap

1. In Zapier, create a new Zap.
2. Choose Schedule by Zapier as the trigger and the Every Week event.
3. Choose Monday as the day (the field is labeled Day of the Week, or similar) and set the time of day for the email.
4. Add an action. Choose Google Sheets and the Lookup Spreadsheet Row event.
5. Pick the spreadsheet Hearing and Interview Prep and the worksheet Summary.
6. For the lookup column choose Key (column A). Labels may be worded a little differently.
7. For the lookup value type summary.
8. Leave the other optional settings at their defaults.
9. Add a second action. Choose Gmail and the Send Email event.
10. In To, type your own address only. The digest goes to you and no one else.
11. Give it a subject such as Prep needed this week.
12. Type the body below, replacing each bracketed name with the field of that name from the Google Sheets step.

Prep needed [Prep Needed Count]: [Prep Needed Items]
Missing event dates: [Missing Date Count]

1. Test the Google Sheets step. It should return the whole Summary row.
2. Test the Gmail step and read the email.
3. Turn the Zap on.

The lookup returns the entire matching row, and that row is all Zapier sees. The Events tab never leaves Google.

What leaves your sheet

Zapier receives the Summary row and the email text. That means the word summary, the Prep Needed Count, the Prep Needed Items text (matter codes, event type codes, event dates and the prep and exhibits answers of yes or no) and the Missing Date Count. Zap History stores the data each step received and returned, so those values stay in your Zapier account. The rest of the Events tab stays in Google. Check Zapier's own pages for how long Zap History is kept.

Use codes only. A client name, a court or office name, a country or any case fact does not belong in this sheet.

Zapier bills only successful action steps as tasks. The trigger, a search that halts and a failed action are not counted, so a normal run uses two tasks.

Real Example: Monday 2026-10-26

Setup: The six invented rows from Part 3 and B4 set to 21. Input: On Monday at the scheduled hour the Zap fires and finds the summary row. Output:

Prep needed 2: IM-26-031 INTERVIEW (2026-11-05) prep no, exhibits no | IM-26-038 HEARING (2026-11-16) prep yes, exhibits no
Missing event dates: 1

Check it by hand. November 5 is 10 days after October 26, and November 16 is 21 days after it. November 17 is 22 days out, so IM-26-040 waits. IM-26-044 has both answers yes. October 21 is past. IM-26-052 has no date. What you do next: You book the prep meeting for IM-26-031 and ask your paralegal where the exhibit set stands for both events. You find the date for IM-26-052 in the notice or the court's record and type it in. The sample is invented. Real dates come from the notice or the court's own record.

What to Do When It Breaks

• A Monday passed with no email → This failure is silent, so build the habit of noticing it. Check that the Zap is switched on. Open Zap History and look for a run that Monday. A "safely halted" run means the lookup found no row, so check that A2 on the Summary tab still says summary, because a renamed Key cell stops the lookup. Check that the spreadsheet and the Summary tab still have their original names. If Zapier reports an expired Google connection, reconnect it. A Monday with no email is never a sign that every event is prepared.
• An event never appears → Confirm column C holds a real date and not text, and check that the event is not already past.
• A finished event still shows → Both Prep meeting held and Exhibits ready must read yes, spelled exactly that way. The dropdown prevents typos.
• An event moved → Update the date in column C. The sheet does not learn of a rescheduling.
• Dates show as numbers in the email → The TEXT wrapper was removed from the C2 formula. Restore it from Part 2.
• Counts look stale → Open the sheet and keep Calculation Mode on Automatic.

Variations

• Simpler version: Drop the exhibits column and flag only events where the prep meeting is not held.
• Extended version: Raise the aim from a flag to a finished prep. Add columns for Interpreter confirmed and Client documents in hand that the formulas do not read, and use them when you plan the prep meeting.

What to Do Next

• This week: Run the test rows. Then replace them with your live events and compare the first email with your calendar.
• This month: Adjust Alert days to the lead time your own prep needs.
• Advanced: Pair it with the Daily Notice Response Watch and the Weekly Missing-Documents Digest, each in its own spreadsheet.

Advanced guide for immigration attorney professionals. This build needs a paid Zapier plan. Zapier and Google update their screens often, so if a label differs from this guide, look for the closest match and check the vendor's help pages.