Weekly Monitoring Event Schedule Digest: One Monday Email of Events Coming Due or Overdue

What This Builds

Monitoring intervals live in the approved work plan, and the next event tends to be remembered by whoever did the last one. This build gives that memory a home. You type each event's last date and the interval from the work plan, the sheet works out the next due date, and every Monday you get one email listing the events coming due inside your look-ahead window, the events already past their due date and a count of events missing a date. The outcome it serves is a field team that is booked before an event is late, not after.

Prerequisites

• A new Google spreadsheet named "Monitoring Event Schedule", 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
• The interval for each monitoring event, taken from the approved work plan
• A check of your firm's policy, and of what the client allows, on event codes and project codes in an outside automation tool

The Concept

Think of a service reminder card on a vehicle. It does not know the manufacturer's schedule. You write the date and the interval on it once, and it tells you when the next one falls. Your Events tab works the same way. The interval is whatever your work plan says, typed by you. This guide states no monitoring frequency and no rule about one.

The Zap has a quirk you need to design around. If a Zapier lookup finds no matching row, the Zap halts, Zap History shows the run as safely halted, and no email is sent. So the Zap never searches the Events tab. It looks up one Summary row that always exists, and the sheet fills that row. A quiet week produces an email saying none and zero. There is no AI step, no loop, no second lookup and no formula in Zapier, and every date and count is computed by the sheet.

Data caution. The sheet holds event codes, project codes, dates and intervals only. Client names, site names and addresses, well locations tied to a real site, results and report content stay in the firm's approved systems. Zapier is an outside tool, so check whether your firm and the client allow project 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 columns C and E as dates. Add the dropdown to F2 through F301 (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(OR(A2="",C2="",D2=""),"",C2+D2)

G2, filled down to row 301:

=IF(A2="","",IF(F2="yes","",IF(OR(C2="",D2=""),"MISSING DATE",IF(E2<TODAY(),"OVERDUE",IF(E2-TODAY()<=Summary!$B$4,"DUE SOON","")))))

Read the flag formula from the outside in:

• A blank row (no event code) returns blank.
• A closed event returns blank, whatever its dates say.
• An event with a code that lacks a last date or an interval returns MISSING DATE.
• A next due date before today returns OVERDUE.
• A next due date today or later, and no more than Look ahead days away, returns DUE SOON. A next due date of today is 0 days away and counts.
• Everything else returns blank.

The five closing parentheses at the end of the flag formula match the five IF functions open at that point. E2 returns blank when any input is missing, and G2 tests for the missing input before it ever reads E2, so a blank E2 is never compared with TODAY().

After each event you type the new last event date. Nothing else changes.

Part 2: Build the Summary tab

Add a second tab named Summary.

B2:

=COUNTIF(Events!G2:G301,"DUE SOON")

C2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Events!A2:A301&" "&Events!B2:B301&" ("&TEXT(Events!E2:E301,"yyyy-mm-dd")&")",Events!G2:G301="DUE SOON")),"none")

D2:

=COUNTIF(Events!G2:G301,"OVERDUE")

E2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Events!A2:A301&" "&Events!B2:B301&" ("&TEXT(Events!E2:E301,"yyyy-mm-dd")&")",Events!G2:G301="OVERDUE")),"none")

F2:

=COUNTIF(Events!G2:G301,"MISSING DATE")

Check the parentheses in each formula. Every range in 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. 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 event, which return blank, are never counted.

The look-ahead value of 14 is a starting choice. Because this digest arrives weekly, a window shorter than a week can let an event slip between two emails. Set the window to suit the lead time your team needs to book a field day.

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

The intervals here are invented to exercise the formulas and say nothing about any real monitoring frequency.

The Summary row should then read:

ME-02 shows the boundary: a next due date exactly 14 days out is inside a 14-day window. ME-05 shows that a date of today counts as due soon. ME-07 would be long overdue by its dates, and Closed = yes removes it from every list.

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

Due soon [Due Soon Count]: [Due Soon Events]
Overdue [Overdue Count]: [Overdue Events]
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 Events tab never leaves Google.

What leaves your sheet

Zapier receives the Summary row and the email text. That means the word summary, the Due Soon Count, the Due Soon Events text (event codes, project codes and next due dates), the Overdue Count, the Overdue Events text and the Missing Date Count. Zap History stores the data each step received and returned, so those values stay in your Zapier account. The last event dates, the intervals and the Closed flags stay in the Events tab. Check Zapier's own pages for how long Zap History is kept.

Use codes only. A client name, site name or address, well location that identifies a site, result value or any piece of a report 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-19

Setup: The seven invented rows from Part 3 and B4 set to 14. Input: At the scheduled hour on Monday the Zap fires and finds the summary row. Output:

Due soon 3: ME-01 P-14 (2026-10-26), ME-02 P-14 (2026-11-02), ME-05 P-16 (2026-10-19)
Overdue 1: ME-04 P-15 (2026-10-14)
Missing dates: 1

Check it by hand. ME-01 is due in 7 days, ME-02 in 14 and ME-05 today. ME-03 is 21 days out and waits. ME-04 was due on 2026-10-14, 30 days after 2026-09-14. ME-06 has no last date. ME-07 is closed. What you do next: You ask your project manager to confirm field days for ME-05 and ME-04, book ME-01 and ME-02 and look up the last date for ME-06 in your field records. The sample is invented. Your real intervals come from the approved work plan.

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.
• An event never appears → Confirm columns C and D hold a real date and a number and not text, and that Closed is not yes.
• Next due shows blank for a row you filled → One of A, C or D is empty. The formula waits for all three.
• A finished event keeps showing OVERDUE → Type the new last event date in column C. The interval stays the same.
• 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.

Variations

• Simpler version: Skip the Closed column and delete a retired location's row instead.
• Extended version: Raise the aim from a reminder to a booked field day. Add a Field day booked column and a Notes column that the formulas do not read, and use them to record who confirmed access and the lab. The email stays the same, and the columns help you act on it.

What to Do Next

• This week: Run the test rows. Then replace them with your live events.
• This month: Check each interval against the approved work plan and note the page it came from in a spare column.
• Advanced: Pair it with the Daily Lab Results Watch and the Weekly Equipment Calibration and Rental Return Digest, each in its own spreadsheet.

Advanced guide for environmental scientist 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.