Weekly Missing-Documents Digest: One Monday Email of Items to Chase

What This Builds

Missing documents come back every week. A paralegal asks for a lease, a client says it is coming, and three weeks later nobody remembers who asked. This build keeps one line per requested item in a small sheet and sends you one email every Monday listing the items still not received after your own waiting period. The outcome it serves is no request sitting unanswered for weeks without anyone noticing. The sheet does the noticing, and you decide whom to ask and how.

Your paralegal follows up on your instruction. The Zap emails you and never emails a client.

Prerequisites

• A Google spreadsheet named "Missing Documents", 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 and item codes in an outside automation tool

The Concept

Picture a shared whiteboard that wipes itself clean of everything received and leaves only what is still owed. The sheet is that whiteboard. The Zap is the person who photographs it every Monday and slides the picture under your door.

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. If your Zap searched the item list directly, a week with nothing to chase would 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 Items 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.

The number of days to wait before chasing is your own choice, typed in a cell. This guide states no period for any document request.

Data caution. The sheet holds matter codes, short item codes such as PASSPORTCOPY, LEASE and PAYSTUBS, requested dates and yes or no only. Client names, document numbers, A-numbers, receipt numbers, countries and anything from inside a document stay in your firm's approved systems. Zapier is an outside tool, so check whether your firm's policy allows matter codes and item 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 Items tab

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

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

E2, filled down to row 301:

=IF(A2="","",IF(D2="yes","",IF(C2="","MISSING DATE",IF(TODAY()-C2>Summary!$B$4,"CHASE",""))))

Read the formula from the outside in:

• A blank row (no matter code) returns blank.
• A received item returns blank.
• An item with a code but no requested date returns MISSING DATE.
• An item whose requested date is more than Days before chasing days behind today returns CHASE. An item exactly at the limit does not, because the test is "more than".
• Everything else returns blank.

The four closing parentheses at the end match the four IF functions open at that point.

Part 2: Build the Summary tab

Add a second tab named Summary.

B2:

=COUNTIF(Items!E2:E301,"CHASE")

C2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Items!A2:A301&" "&Items!B2:B301&" (requested "&TEXT(Items!C2:C301,"yyyy-mm-dd")&")",Items!E2:E301="CHASE")),"none")

D2:

=COUNTIF(Items!E2:E301,"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-10-05 and not as a serial number. A row only reaches the list when it is flagged CHASE, and that flag needs a date, so TEXT() never sees a blank. 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 item are never counted.

The value 14 is a starting choice. Change B4 to the waiting period you want.

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

The Summary row should then read:

IM-26-038 LEASE shows the boundary: 14 days is not more than 14, so it waits a week. IM-26-031 BIRTHCERT is 28 days old, and Received = yes clears it.

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 Missing Documents 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 Missing documents to chase.
12. Type the body below, replacing each bracketed name with the field of that name from the Google Sheets step.

To chase [Chase Count]: [Chase Items]
Missing request 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 Items tab never leaves Google.

What leaves your sheet

Zapier receives the Summary row and the email text. That means the word summary, the Chase Count, the Chase Items text (matter codes, item codes and requested dates) and the Missing Date Count. Zap History stores the data each step received and returned, so those values stay in your Zapier account. The Received column and the rest of the Items tab stay in Google. Check Zapier's own pages for how long Zap History is kept.

Use codes only. A client name, a document number, a country or any detail from inside a document 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 14. Input: On Monday at the scheduled hour the Zap fires and finds the summary row. Output:

To chase 2: IM-26-031 PASSPORTCOPY (requested 2026-10-05), IM-26-040 PAYSTUBS (requested 2026-10-11)
Missing request dates: 1

Check it by hand. October 5 to October 26 is 21 days, and October 11 to October 26 is 15 days. Both are more than 14. October 12 is exactly 14 days back, so LEASE waits. October 20 is 6 days back. BIRTHCERT is received. TAXRETURN has no date. What you do next: You tell your paralegal which two items to follow up on and in what words. You decide whether the TAXRETURN request needs a date entered or a fresh request. The sample is invented. Real requests come from your own file.

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 nothing is outstanding.
• An item never appears → Confirm column C holds a real date and not text, and the Received cell is not yes.
• A received item still shows → Received must read yes, spelled exactly that way. The dropdown prevents typos.
• 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.
• A paralegal chases an item that arrived → The Received cell was not updated. Make that update part of how documents are logged in.

Variations

• Simpler version: Skip the dropdown and delete a row once the item arrives.
• Extended version: Raise the aim from a list to a closed loop. Add a Last chased date column that the formulas do not read, and use it when you decide which items need a different approach.

What to Do Next

• This week: Run the test rows. Then replace them with the items your office is waiting on.
• This month: Adjust Days before chasing to suit how your clients actually respond.
• Advanced: Pair it with the Daily Notice Response Watch and the Weekly Document Expiration 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.