Weekly Data Release Watch Digest: A Monday Email That Names the Series to Pull

What This Builds

Every Monday morning an email lands in your own inbox with three lines: the series whose next release falls inside your look-ahead window, the series whose release date has already passed without a pull, and a count of series with no date typed in. You stop scanning a calendar and a tracker by hand. The sheet does all the counting. Zapier only reads one finished row and mails it to you.

Prerequisites

• The Data Release Watch spreadsheet, with a Releases tab and a Summary tab laid out as in Part 1
• A Zapier account on a paid plan, because this Zap has three steps and the free plan allows two-step Zaps only. The plan is Professional ($29.99/month). That is the whole ongoing cost, since you already have the Google account the sheet and the email use.
• A Google account connected to Zapier for both Sheets and Gmail
• Your firm's policy on putting project codes into an outside automation tool, checked before you start (see "What leaves your sheet" below)

The Concept

Think of the Summary tab as a noticeboard with exactly one notice pinned to it. The Releases tab can grow, shrink or be messy. The notice is rewritten by formulas every time the sheet recalculates, and it is always there, even when the answer is "nothing due". Zapier walks up to the noticeboard on a schedule, copies the notice and mails it to you.

Why not let Zapier search the Releases tab for rows that are due? A Zapier lookup that finds no row stops the Zap. Zap History then shows the run as safely halted, no later step runs and no email goes out. A quiet week would look exactly like a broken Zap. Reading a Summary row that always exists removes that problem, so the Zap sends every Monday whether the news is good or bad. There is also no AI step in this build, so no figure is generated by anything. The sheet's formulas compute every count.

Build It Step by Step

Part 1: Check the spreadsheet

The spreadsheet is named "Data Release Watch". It has two tabs.

Tab Releases

E2, filled down to row 301:

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

Tab Summary

B2:

=COUNTIF(Releases!E2:E301,"DUE SOON")

C2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Releases!A2:A301&" ("&TEXT(Releases!C2:C301,"yyyy-mm-dd")&")",Releases!E2:E301="DUE SOON")),"none")

D2:

=COUNTIF(Releases!E2:E301,"OVERDUE")

E2:

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

F2:

=COUNTIF(Releases!E2:E301,"MISSING DATE")

Read each formula once more before moving on. The parentheses must balance, and every range inside one FILTER runs from row 2 to row 301, so the ranges have equal height. Format column C of the Releases tab as a date. A date typed as text will not compare with TODAY().

The look-ahead window in B4 is your choice, or your project lead's. No rule sets it.

How each case flows through the flag formula

• A blank row has an empty A cell, so E returns blank. Nothing counts it, because the counts look for the exact words DUE SOON, OVERDUE and MISSING DATE.
• A row marked Pulled = yes returns blank, whatever its date says.
• A row with a series code but no date returns MISSING DATE. The date comparisons never run on an empty cell.
• A date before today returns OVERDUE.
• A date between today and today plus B4 returns DUE SOON. The boundary day counts, so a date exactly B4 days away is included.
• Any later date returns blank.

Keep Calculation Mode on Automatic (File, Settings, Calculation). Google's calculation settings page lists Automatic and Manual modes only, and no scheduled recalculation. TODAY() changes when the sheet recalculates, so open the sheet before Monday's run if the counts ever look stale. Also check the spreadsheet's time zone under File, Settings, General, because TODAY() follows it.

Part 2: Load the test rows

These rows are invented. They are written for a pretend TODAY() of Monday 2026-10-19 with B4 = 7. If you build on another day, type each date at the same offset from your real today, shown in the last column.

With those rows, the Summary tab should show these values:

If a cell differs, fix the sheet now. Zapier will faithfully mail whatever the Summary row says, including a wrong answer.

Part 3: Create the Zap

1. Open Zapier and create a new Zap.
2. Choose Schedule by Zapier as the trigger app, then the Every Week event.
3. Pick the day, Monday, and the hour you want the email.
4. Check the time zone Zapier shows for the schedule.
5. Add an action step. Choose Google Sheets, then the Lookup Spreadsheet Row event.
6. Connect your Google account and pick the spreadsheet Data Release Watch.
7. Pick the worksheet Summary.
8. For the lookup column, choose Key (column A). Field labels may be worded a little differently from these names.
9. For the lookup value, type summary exactly as it appears in A2.
10. Leave any other optional settings of this step at their defaults.
11. Add a second action step. Choose Gmail, then the Send Email event.
12. In the To field, type your own address and no one else's. The digest goes to you only.
13. Type a subject such as Data release watch.
14. In the body, type the text below. Replace each bracketed name with the field of that name from the Google Sheets step, inserted from the field picker.

Due soon [Due Soon Count]: [Due Soon Series]
Overdue [Overdue Count]: [Overdue Series]
Missing dates: [Missing Date Count]
Open the Data Release Watch sheet to see the rows.

1. Run the test on the Google Sheets step. Zapier should return the whole Summary row, with a value for each header in row 1.
2. Run the test on the Gmail step and read the email you receive.
3. Turn the Zap on.

The email shows what the sheet says and nothing more. It does not tell you what to pull or when a release will land. You type each release date from the agency's own calendar.

Part 4: Keep the sheet current

After you pull a series, type the following release date in column C and set Pulled back to no. Skip the second half and the series either stays hidden for good (Pulled left at yes) or flags OVERDUE against its old date (Pulled set back to no without a new date).

What leaves your sheet

Zapier receives the Summary row only: counts, series codes and dates. The Lookup Spreadsheet Row step returns one row when it finds a match, and your Releases tab is never sent. Zap History keeps the data each step received and returned, so the Summary values and the email text sit in your Zapier account for as long as Zapier's retention allows. Check Zapier's own pages for how long that is.

Use series codes and short agency names only. Leave out client names, matter names, case captions, confidential figures and document content. Some firms and some engagements do not allow even project codes in an outside tool. Check the firm's policy before you build, and ask your project lead if you are unsure. The email itself passes through Gmail, so the same codes-only rule applies there.

Billing follows the steps that succeed. Zapier counts successful action steps as tasks. Triggers, a search that halts and failed actions are not counted.

Real Example: The Week of 2026-10-19

Setup: The seven invented test rows from Part 2, a look-ahead of 7 days and the Zap set to Every Week on Monday. Input: On Monday 2026-10-19 the schedule fires. The lookup finds the row whose Key is summary. Output: The email reads:

Due soon 3: S-01 (2026-10-20), S-02 (2026-10-26), S-05 (2026-10-19)
Overdue 1: S-04 (2026-10-16)
Missing dates: 1
Open the Data Release Watch sheet to see the rows.

Check the arithmetic against the table. S-01 is 1 day out, S-02 is 7 days out and S-05 is today, so three series are due. S-04 is 3 days past. S-07 has no date. S-03 is 8 days out and sits outside the window, and S-06 is already pulled. What you do next: You decide which pulls to schedule. S-07 gets its date from the agency's calendar, and S-04 gets pulled or explained to your project lead.

The sample series are invented. No real release date, agency or data value appears here.

What to Do When It Breaks

• No email arrived on Monday → This is the failure you will not see. Check these in order. First, confirm the Zap is turned on. Second, open Zap History and look for a run on that date. Third, confirm cell A2 on the Summary tab still holds the word summary, because a renamed Key cell means the lookup finds no row and the Zap halts. Fourth, confirm the Summary tab and the spreadsheet keep their names, since the step points at them by name. Fifth, reconnect Google if Zapier shows an expired connection. A routine habit helps: if no digest arrives by mid-morning on a Monday, treat that as a fault, not a quiet week.
• The email says none for everything but you expect flags → Open the sheet and check that column C holds real dates and not text. Check Calculation Mode and reload the sheet. Then check the Look ahead value in B4.
• A series flags OVERDUE after you pulled it → Set Pulled to yes, or type the next release date and set it back to no.
• Dates show as five-digit numbers in the email → The TEXT wrapper in C2 or E2 was dropped when you edited the formula. Restore it from Part 1.
• The formula shows an error in C2 → Count the ranges. Every range inside one FILTER must span rows 2 to 301.
• The digest repeats a stale count → TODAY() has not recalculated. Open the sheet and keep Calculation Mode on Automatic.

Variations

• Simpler version: Skip the Zap and open the Summary tab each Monday.
• Extended version: Keep one digest per team by building a separate spreadsheet and Zap for each team. Do not add a second Summary row to this sheet.

What to Do Next

• This week: Run the Zap against the test rows. Then replace them with your real series codes and dates.
• This month: Try different look-ahead values and see which one gives you enough warning for your pulls.
• Advanced: Pair this with the Daily Deliverable and Review Deadline Digest and the Weekly Data Vintage Refresh Digest, each in its own spreadsheet.

Advanced guide for economist professionals. This build needs a paid Zapier plan. Zapier and Google change their screens often, so if a label differs from this guide, look for the closest match and check the vendor's help pages.