Weekly Document Expiration Digest: One Monday Email of Documents Expiring or Expired

What This Builds

Expiration dates sit inside passports, work permits and status documents, in client files that nobody reopens until a client calls. This build keeps one line per tracked document in a small sheet and sends you one email every Monday. The email lists documents expiring inside your own window where no renewal has started, documents already expired where no renewal has started, and a count of rows with no date. The outcome it serves is a conversation with the client before an expiration becomes a surprise. The sheet decides what belongs on each list. Whether and when anything can be renewed or filed is your call, from the document and your own research.

Prerequisites

• A Google spreadsheet named "Document Expirations", 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 pharmacy with a shelf of dated stock does not wait for a customer to find an expired bottle. Someone walks the shelf on a schedule. The sheet is the shelf list, and the Zap is the Monday walk.

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 nothing expiring 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 Documents 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.

Each expiration date is typed from the document itself. The alert window is a number you choose. This guide says nothing about when a renewal can be filed or must be filed.

Data caution. The sheet holds matter codes, document type codes (PASSPORT, EAD or STATUS), dates and yes or no only. Client names, document numbers, A-numbers, receipt numbers, countries, dates of birth and scans 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 Documents tab

Name the first tab Documents. 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(C2<TODAY(),"EXPIRED",IF(C2-TODAY()<=Summary!$B$4,"EXPIRING","")))))

Read the formula from the outside in:

• A blank row (no matter code) returns blank.
• A row where renewal has started returns blank, even if the document has expired.
• A row with a code but no expiration date returns MISSING DATE.
• An expiration date before today returns EXPIRED.
• An expiration date today or later, no more than the alert days away, returns EXPIRING. A date of today counts.
• Everything else returns blank.

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

Part 2: Build the Summary tab

Add a second tab named Summary.

B2:

=COUNTIF(Documents!E2:E301,"EXPIRING")

C2:

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

D2:

=COUNTIF(Documents!E2:E301,"EXPIRED")

E2:

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

F2:

=COUNTIF(Documents!E2:E301,"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 2027-03-01 and not as a serial number. A row only reaches a list when it is flagged EXPIRING or EXPIRED, and both flags need 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 document are never counted.

The value 180 is your own window and not a filing rule. Set it to the lead time you want for a conversation with a client.

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

The Summary row should then read:

IM-26-038 shows the boundary: 180 days is within the window, so it is flagged. IM-26-040 sits at 181 days and waits. IM-26-047 shows that Renewal started = yes clears the row. IM-26-044 shows an expired row.

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 Document Expirations 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 Document expirations this week.
12. Type the body below, replacing each bracketed name with the field of that name from the Google Sheets step.

Expiring [Expiring Count]: [Expiring Items]
Expired [Expired Count]: [Expired 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 Documents tab never leaves Google.

What leaves your sheet

Zapier receives the Summary row and the email text. That means the word summary, the Expiring Count, the Expiring Items text, the Expired Count, the Expired Items text and the Missing Date Count. Those items are matter codes, document type codes and dates. Zap History stores the data each step received and returned, so those values stay in your Zapier account. The Renewal started column and the rest of the Documents 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 a date of birth 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 180. Input: On Monday at the scheduled hour the Zap fires and finds the summary row. Output:

Expiring 2: IM-26-031 PASSPORT (2027-03-01), IM-26-038 EAD (2027-04-24)
Expired 1: IM-26-044 STATUS (2026-10-20)
Missing dates: 1

Check it by hand. From October 26 to March 1 is 126 days, and to April 24 is 180 days. April 25 is 181 days, so IM-26-040 waits. IM-26-044 is dated October 20, which is 6 days back. IM-26-047 has a renewal started. IM-26-052 has no date. What you do next: You open the file for IM-26-044 first and decide what the document's date means for the client. You plan a conversation with the other two clients and ask your paralegal for the missing date. The sample is invented. Real dates come from the documents.

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 expiring.
• A document never appears → Confirm column C holds a real date and not text, and the Renewal started cell is not yes.
• A renewed document still shows → Update the expiration date to the new document's date and reset Renewal started. The sheet does not learn of a renewal.
• Dates show as numbers in the email → The TEXT wrapper was removed from the C2 or E2 formula. Restore it from Part 2.
• Counts look stale → Open the sheet and keep Calculation Mode on Automatic.
• A typed date was wrong → The sheet repeats whatever was typed. Recheck the date against the document itself.

Variations

• Simpler version: Drop the Renewal started column and flag every document inside the window.
• Extended version: Raise the aim from a warning to a plan. Add a Client contacted date column that the formulas do not read, and use it when you decide whom to call first.

What to Do Next

• This week: Run the test rows. Then replace them with the documents on your live matters.
• This month: Adjust Alert days to suit your own planning.
• 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.