Zapier Automation: A Compliance Filing Calendar That Reminds Itself

What This Builds

ERISA, Form 5500, ACA reporting, nondiscrimination testing and pay transparency filings across a dozen or more states each run on their own calendar, and right now you find out about a coming deadline only when you happen to check the tracker or someone mentions it in passing. This automation checks your compliance tracker every morning and sends you one digest email when a filing crosses the 90, 30 or 7 day mark, with a short AI-drafted summary of what is due and why it matters.

Prerequisites

• A Professional plan with multi-step Zaps and filters ($29.99/month)
• A compliance tracker spreadsheet in Google Sheets, one row per filing, with a due date column
• A Google account for Sheets and Gmail, free for personal use or your organization's paid Business Standard account if that's what your HR team already runs on
• 30 minutes to set up the tracker formula, 30 to 60 minutes to build and test the Zap

Total ongoing cost is the $29.99/month subscription. Google Sheets and Gmail add nothing new if your organization already runs on a Google account. On most mornings the lookup finds nothing and the run stops there, so the Zap uses only a few tasks on the days a filing is actually flagged. A run that fails partway through still uses the tasks it spent before the failure.

The Concept

This is a daily check, not a countdown timer. A Zapier "Delay" step can only hold a run for up to 30 days, which rules out a single automation that waits 90 days for a filing deadline. Instead, the tracker spreadsheet itself calculates how many days remain until each due date, and a Zap that runs every morning asks the spreadsheet a simple question: does anything cross the 90, 30 or 7 day mark today? When the answer is yes for one or more rows, Zapier compiles them into one list, asks an AI step to turn that list into a short plain-language summary, and emails it to you.

Think of it less like an alarm clock and more like a colleague who reads the whole tracker every morning before you do and only interrupts you when something is actually due soon.

Build It Step by Step

Part 1: Add a reminder-window formula to your tracker

In your compliance tracker spreadsheet, add a column called Reminder Window with a formula that flags a row when its due date is 90, 30 or 7 days away:

=IF(OR(F2-TODAY()=90, F2-TODAY()=30, F2-TODAY()=7), "Yes", "No")

Adjust F2 to whichever column holds the due date. Copy the formula down every row. Make sure the tracker also has a Filing Name column and a Notes column, then add one more column, Digest Line, that joins them into one readable line per filing:

=A2&" (due "&TEXT(F2,"mmm d, yyyy")&"): "&G2

Adjust A2, F2 and G2 to your Filing Name, due date and Notes columns. The AI step reads this column, so every filing arrives with its name, date and notes together.

Part 2: Build the Zap trigger and lookup

1. Go to zapier.com, click + Create, then Zap
2. Trigger app: Schedule by Zapier
3. Trigger event: Every Day, set to a time before you usually start work, such as 7:00 AM
4. Action 1, app: Google Sheets, event: Lookup Spreadsheet Rows (Advanced)
5. Connect your Google account, select the tracker spreadsheet, and set the lookup column to Reminder Window with the value Yes. Leave the option to continue when nothing is found turned off, so the run stops quietly on days with no match

What you should see: when you test this step, Zapier returns a list of every row currently flagged, which on most days will be empty and on a reminder day will hold one or more rows.

Part 3: Combine the matches and draft the digest

Because the lookup can return several rows at once, the next two steps turn that list into one message instead of several separate ones.

1. Action 2, app: Formatter by Zapier, event: Utilities
2. Transform: Line-item to Text
3. Input: map the Digest Line list from Action 1 and choose a new line as the separator

This produces one text block listing every filing that crossed a reminder threshold today, instead of requiring a Looping by Zapier step to handle each row separately. Looping works well when you need a separate action per row, such as sending an individual message for each item, but here you want a single digest, and joining the rows first with Formatter is the simpler, more reliable path.

1. Action 3, app: AI by Zapier
2. Prompt:

Here is a list of compliance filings crossing a reminder threshold today:

{{combined filing list from Formatter}}

Write a short email body, no more than 150 words, that groups these by how
urgent they are (7 days away first, then 30, then 90). For each filing, state
what it is, its due date and a one-line reminder of what typically needs to
happen before that date (recordkeeper submission, internal sign-off, state
filing portal, etc). Do not invent a filing requirement that is not in the
list. End with a line reminding the reader that a recordkeeper or TPA
typically handles the actual filing and this is a tracking reminder, not
the filing itself.

1. Action 4, app: Gmail, event: Send Email
2. To: your own email address
3. Subject: Compliance filing reminder: filings due soon
4. Body: the AI-drafted summary from Action 3

Part 4: Test end to end

1. In your tracker, temporarily set one row's due date so the Reminder Window formula returns "Yes"
2. Run a manual test of the full Zap from the Zapier editor
3. Confirm the email arrives with an accurate summary of the test row
4. Reset the test row's due date to its real value
5. Turn the Zap on

Real Example: A Quiet Tuesday That Wasn't

Setup: The Zap has run every morning for two months without sending anything, since nothing has crossed a threshold.

Trigger: On a Tuesday, the formula flags two rows: a state pay transparency filing 30 days out and a Form 5500 deadline 90 days out.

Output: A single email arrives at 7:05 AM titled "Compliance filing reminder: filings due soon," with the pay transparency filing listed first since it is more urgent, followed by the Form 5500 deadline with a note that the recordkeeper typically handles the actual submission.

Time saved: Before this automation, catching a 90-day filing early meant remembering to scroll the full tracker on a day nothing was obviously urgent. Now the tracker does that scan on its own, and the manual review drops from a standing weekly habit to a quick check of the inbox.

What to Do When It Breaks

• The Zap stops sending emails and you don't notice → This is the failure you won't see, since a Zap that silently stops running looks identical to a quiet week with nothing due. Turn on Zapier's built-in Zap health notifications under your account settings, and separately, glance at the tracker itself once a week regardless of whether an email has arrived, since a spreadsheet is still readable even if the automation feeding it goes quiet.
• The reminder email arrives with nothing useful in it → Check the Formatter step's input mapping first. If the Line-item to Text transform isn't receiving the Digest Line list from the lookup, the AI step gets an empty or malformed prompt and drafts something generic.
• Filings that should trigger don't → Open the tracker and manually check the Reminder Window formula against today's date. A common cause is a due date entered as text instead of a date value, which breaks the date subtraction the formula depends on.
• A filing shows up a day early, a day late or two days running → The spreadsheet's time zone and the Schedule by Zapier trigger's time zone probably differ, so TODAY() rolls over at a different moment than the Zap runs. Set the spreadsheet's time zone (File, then Settings) to match the one in the Zap's schedule trigger.

Variations

• Simpler version: Skip the AI drafting step and send the raw combined list from Formatter directly. Less polished, but faster to build and just as reliable for catching a deadline.
• Extended version: Add a second Google Sheets action that logs each digest sent, with a timestamp and the filings it covered, so you have an audit trail showing the reminder system caught each deadline on schedule.

What to Do Next

• This week: Add the Reminder Window formula to your tracker and confirm it flags the correct rows.
• This month: Build and test the full Zap, then let it run alongside your manual review for a few weeks before you trust it fully.
• Advanced: Connect this same tracker to the vendor and compliance Claude Project, so a filing flagged here is already backed by the contract and calendar context Claude has on file.

Advanced guide for compensation and benefits manager professionals. These techniques use more sophisticated AI features that may require paid subscriptions.