Daily Notice Response Watch: One Morning Email of Notice Targets Coming Up or Missed

What This Builds

Notice response dates and your own internal targets live in the notice itself, the firm calendar, your case management system and a paralegal's tracker. This build adds a small sheet that holds one line per open notice and sends you one email every weekday morning. The email lists notices whose internal target is close, notices already past their target and notices whose response date, as you typed it from the notice, is behind you. The outcome it serves is a response that goes out on your internal target and not on the last day. The sheet decides what belongs on each list, and Zapier only carries the result to your inbox.

This is a backstop. It never replaces the firm's docketing system or your own calendar.

Prerequisites

• A Google spreadsheet named "Notice Response Watch", kept 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 good docketing clerk does not stay quiet when nothing is due. They say "nothing today" and you stop wondering. A Zapier search step cannot say that. If a lookup finds no matching row, the Zap halts, Zap History shows the run as safely halted, and no email goes out. A quiet day would look the same as a broken Zap.

The fix is a row that always exists. Your Summary tab holds one row with the word summary in its Key cell, and formulas fill the rest of that row from the Notices tab. Zapier looks up that row, which is always found, and mails it. A quiet day 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. The sheet computes every date comparison and every count.

Two dates matter here, and you type both. The response date comes from the notice itself. The internal target is the date you set for your own team, before the response date. The alert window is a number you choose. This guide states no response period and no rule about notices.

Data caution. The sheet holds matter codes, notice type codes, dates and yes or no only. Client names, A-numbers, receipt numbers, countries, notice text and case facts 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 morning check and skip the Zap.

Build It Step by Step

Part 1: Build the Notices tab

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

Format columns C and D as dates. Add the dropdown to E2 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(E2="yes","",IF(OR(C2="",D2=""),"MISSING DATE",IF(C2<TODAY(),"RESPONSE DATE PASSED",IF(D2<TODAY(),"PAST TARGET",IF(D2-TODAY()<=Summary!$B$4,"TARGET SOON",""))))))

Read the formula from the outside in:

• A blank row (no matter code) returns blank.
• A filed notice returns blank, however late it was.
• A notice with a code but a missing response date or a missing internal target returns MISSING DATE.
• A response date before today returns RESPONSE DATE PASSED. This check comes before the target check, so an unfiled notice whose response date has passed never shows as merely past target.
• An internal target before today returns PAST TARGET.
• A target today or later, and no more than the alert days away, returns TARGET SOON. A target of today counts.
• Everything else returns blank.

The six closing parentheses at the end match the six IF functions open at that point. The OR has its own closing parenthesis before the comma that starts the "MISSING DATE" text.

Part 2: Build the Summary tab

Add a second tab named Summary.

B2:

=COUNTIF(Notices!F2:F301,"TARGET SOON")

C2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Notices!A2:A301&" "&Notices!B2:B301&" (target "&TEXT(Notices!D2:D301,"yyyy-mm-dd")&")",Notices!F2:F301="TARGET SOON")),"none")

D2:

=COUNTIF(Notices!F2:F301,"PAST TARGET")

E2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Notices!A2:A301&" "&Notices!B2:B301&" (target "&TEXT(Notices!D2:D301,"yyyy-mm-dd")&")",Notices!F2:F301="PAST TARGET")),"none")

F2:

=COUNTIF(Notices!F2:F301,"RESPONSE DATE PASSED")

G2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Notices!A2:A301&" "&Notices!B2:B301&" (response "&TEXT(Notices!C2:C301,"yyyy-mm-dd")&")",Notices!F2:F301="RESPONSE DATE PASSED")),"none")

H2:

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

Check the parentheses in each formula. Every range inside one FILTER runs from row 2 to row 301, so the heights match. The dates sit inside TEXT() so they join as 2026-10-26 and not as a serial number. A row only reaches a FILTER list when its flag is set, and the flag is only set when both dates exist, so the TEXT() calls never see a blank date. IFERROR turns the empty-result error into the word none. The counts look for the exact flag words, so rows filled down past your last notice, which return blank, are never counted.

The alert days value of 5 is a starting choice, and it is yours to change. It is your own working window and not a rule about notices.

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 = 5. On another day, keep the same offsets from your real today.

The Summary row should then read:

IM-26-051 shows the boundary: 5 days is not more than 5, so it is flagged. IM-26-052 sits at 7 days and stays blank. IM-26-040 has both a passed response date and a passed target, and the response date check wins. IM-26-047 shows that Filed = yes clears everything. IM-26-049 shows that a target of today still counts. A blank row returns blank and is not counted.

Part 4: Create the Zap

1. In Zapier, create a new Zap.
2. Choose Schedule by Zapier as the trigger and the Every Day event.
3. Set the time of day for the email, such as early morning before your first call.
4. If the trigger offers a weekend option (look for Trigger on weekends?), set it to no.
5. Add an action. Choose Google Sheets and the Lookup Spreadsheet Row event.
6. Pick the spreadsheet Notice Response Watch and the worksheet Summary.
7. For the lookup column choose Key (column A). Labels may be worded a little differently.
8. For the lookup value type summary.
9. Leave the other optional settings at their defaults.
10. Add a second action. Choose Gmail and the Send Email event.
11. In To, type your own address only. The digest goes to you and no one else.
12. Give it a subject such as Notice watch for today.
13. Type the body below, replacing each bracketed name with the field of that name from the Google Sheets step.

Target soon [Target Soon Count]: [Target Soon Items]
Past target [Past Target Count]: [Past Target Items]
Response date passed [Response Date Passed Count]: [Response Date Passed Items]
Missing 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 Notices tab never leaves Google.

What leaves your sheet

Zapier receives the Summary row and the email text. That means the word summary, the Target Soon Count, the Target Soon Items text, the Past Target Count, the Past Target Items text, the Response Date Passed Count, the Response Date Passed Items text and the Missing Date Count. Those items are matter codes, notice type codes and dates. Zap History stores the data each step received and returned, so those values stay in your Zapier account. The Filed column and the rest of the Notices tab stay in Google. Check Zapier's own pages for how long Zap History is kept.

Use codes only. A client name, A-number, receipt number, country or any line of a notice 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 eight invented rows from Part 3 and B4 set to 5. Input: At the scheduled hour the Zap fires and finds the summary row. Output:

Target soon 3: IM-26-044 RFE (target 2026-10-28), IM-26-051 NOID (target 2026-10-31), IM-26-049 RFE (target 2026-10-26)
Past target 1: IM-26-038 RFE (target 2026-10-23)
Response date passed 1: IM-26-040 OTHER (response 2026-10-22)
Missing dates: 1

Check it by hand. IM-26-044 is 2 days from its target, IM-26-051 is 5 and IM-26-049 is 0, all inside the window of 5 days with the notice unfiled. IM-26-052 is 7 days out and waits. IM-26-038 is 3 days past its target. IM-26-040 has a response date 4 days behind. IM-26-047 is filed. IM-26-055 has no response date. What you do next: You open the notice and the file for IM-26-040 first, because the date typed in the sheet is behind you and Filed is not yes. You ask the responsible paralegal where IM-26-038 stands, and you ask for the response date from the notice for IM-26-055. The sample is invented. Real dates come from the notices and from your own targets.

What to Do When It Breaks

• A weekday 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 morning. 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 morning with no email is never a sign that nothing is pending.
• A notice never appears → Confirm column A holds the matter code, column D holds a real date and not text, and the Filed cell is not yes.
• A filed notice still shows → Filed must read yes, spelled exactly that way. The dropdown prevents typos.
• A notice shows MISSING DATE → One of the two dates is blank. Read the response date off the notice and set your own internal target.
• Dates show as numbers in the email → The TEXT wrapper was removed from the C2, E2 or G2 formula. Restore it from Part 2.
• Counts look stale → Open the sheet and keep Calculation Mode on Automatic.
• Counts differ from your calendar → Trust the notice and your docketing system. Then fix the sheet. This email is a backstop.

Variations

• Simpler version: Drop the internal target and flag only RESPONSE DATE PASSED and MISSING DATE.
• Extended version: Raise the aim from reminders to a response that is staffed. Add an Owner column (attorney, paralegal or client) and a Documents gathered yes or no column that the formulas do not read, and use them when you decide whom to ask first.

What to Do Next

• This week: Run the test rows. Then replace them with your live notices and compare the first email with your docket.
• This month: Adjust Alert days to the lead time your team needs for a response.
• Advanced: Pair it with the Weekly Missing-Documents Digest and the Weekly Document Expiration Digest, each in its own spreadsheet. Reader-facing background on bigger asks is in the welcome article at /welcome/aim-higher.

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.