Daily Lab Results Watch: One Weekday Email of Batches Due Soon, Overdue and Missing a Date

What This Builds

A late lab report usually surfaces when the project manager asks about it. This build moves that moment to the morning the date matters. Each weekday you get one email listing the sample batches whose results are due within your look-ahead window, the batches already past their due date, and a count of batches with no due date at all. The sheet decides what belongs on each list. The Zap only carries the result to your inbox. The outcome it serves is simple: no batch passes the date the lab confirmed without someone asking the lab about it.

Prerequisites

• A Google spreadsheet named "Lab Results 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, and of what the client allows, on batch codes and project codes in an outside automation tool

The Concept

A good colleague who checks the lab board each morning does not say "I looked and found nothing, so I stayed quiet." They say "nothing due" and move on. A Zapier search step cannot do that. If a lookup finds no matching row, the Zap halts, Zap History shows the run as safely halted, and no email goes out. The step after it never runs.

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 Batches 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 loop, no second lookup and no formula in Zapier. The sheet computes every count and every date comparison.

Data caution. This build is meant to run on codes and dates only. Client names, site names and addresses, owner names, lab reports, results and sample details stay in the firm's approved systems and out of this sheet and out of Zapier. The sheet holds a batch code, a project code, a short lab name and dates. Zapier is an outside tool, so check whether your firm and the client allow even project codes to pass through it. Check Zapier's own data and retention pages for how long Zap History is kept. If the answer is no, use the sheet as a manual morning checklist and skip the Zap.

Build It Step by Step

Part 1: Build the Batches tab

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

Format columns D 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.

The results due date comes from the lab's confirmation for that batch. This guide states no turnaround and no deadline. Column D is a record only, and the flag formula does not read it.

G2, filled down to row 301:

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

Read the formula from the outside in:

• A blank row (no batch code) returns blank.
• A batch marked received returns blank, even if it has no due date.
• A batch with a code, not received and no due date returns MISSING DATE.
• A due date before today returns OVERDUE.
• A due date today or later, and no more than Look ahead days away, returns DUE SOON. A due date of today is 0 days away and counts.
• Everything else returns blank.

Check the five closing parentheses at the end of the formula against the five IF functions that are still open at that point.

Part 2: Build the Summary tab

Add a second tab named Summary.

B2:

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

C2:

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

D2:

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

E2:

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

F2:

=COUNTIF(Batches!G2:G301,"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-20 and not as a serial number. FILTER returns an error when nothing matches, and IFERROR turns that into the word none. The counts look for the exact flag words, so a formula filled down an empty row, which returns blank, is never counted.

The look-ahead value of 2 is a starting choice. The window is yours to set.

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

The Summary row should then read:

LB-02 shows the boundary: a due date exactly 2 days out is inside a 2-day window. LB-07 shows that a due date of today counts as due soon. LB-05 shows that Received = yes clears a batch whose date has long passed. A blank row 9 returns blank and adds nothing to any count.

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.
4. Set Trigger on weekends? to no.
5. Add an action. Choose Google Sheets and the Lookup Spreadsheet Row event.
6. Pick the spreadsheet Lab Results 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 Lab results watch.
13. 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 Batches]
Overdue [Overdue Count]: [Overdue Batches]
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 Batches 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 Batches text (batch codes, project codes and results due dates), the Overdue Count, the Overdue Batches 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 lab names, the shipped dates and the received flags stay in the Batches tab. Check Zapier's own pages for how long Zap History is kept.

Use codes only. A client name, site name or address, owner name, result value or any piece of a lab 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, one for the lookup and one for the email.

Real Example: Monday 2026-10-19

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

Due soon 3: LB-01 P-14 (2026-10-20), LB-02 P-14 (2026-10-21), LB-07 P-14 (2026-10-19)
Overdue 1: LB-04 P-15 (2026-10-16)
Missing dates: 1

Check it by hand against the sheet. LB-01 is 1 day out, LB-02 is 2 days out and LB-07 is due today, all inside the 2-day window. LB-03 is 3 days out and waits. LB-04 is past its date and not received. LB-05 is received. LB-06 has no date. What you do next: You call or email the lab about LB-04, ask Lab B to confirm a date for LB-06 and note the three due batches for the week. The sample is invented. The dates in your real sheet come from the lab's own confirmations.

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 on that day. 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 day with no run at all points to the Zap being off or the trigger's time and weekday settings having changed.
• Emails arrive on weekends → Open the trigger and set Trigger on weekends? to no.
• A batch never appears → Confirm column E holds a real date and not text, and that Received is not yes.
• A received batch still shows DUE SOON → 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 or E2 formula. Restore it from Part 2.
• Counts look stale → Open the sheet and keep Calculation Mode on Automatic.
• C2 shows none but B2 says a number above zero → A range inside the FILTER is shorter or longer than the others, or the flag word is spelled differently. Compare the row numbers and the quoted text.

Variations

• Simpler version: Drop the Missing Date count and flag only OVERDUE and DUE SOON.
• Extended version: Raise the aim from reminders to follow-through. Add a Last chased column to the Batches tab, type the date you last contacted the lab, and read it when you decide whom to call first. Keep it in the sheet, not in the email.

What to Do Next

• This week: Run the test rows. Then replace them with your live batches.
• This month: Adjust Look ahead (days) to match how much warning you want before a confirmed date.
• Advanced: Pair it with the Weekly Monitoring Event Schedule Digest, each in its own spreadsheet with its own Summary tab.

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.