Weekly Compliance Overdue Digest: A Zapier and Google Sheets Automation That Emails You Who Is Late

What This Builds

Every Monday morning, an email lands in your inbox listing who is overdue on compliance training, how many days late each one is, and how the count breaks down by department. You paste a fresh LMS export into a Google Sheet once a week, and the sheet does the counting. Zapier reads one summary row from the sheet and sends you the email.

The digest goes to you and nobody else. You look at it, decide which department leaders need a message, and write that message yourself. The automation never contacts employees, their managers or anyone outside your inbox.

Prerequisites

• A Google account with Google Sheets and Gmail. Google Workspace pricing is on Google's own pricing page.
• A Zapier account on a plan that allows multi-step Zaps. Zapier's free plan allows two-step Zaps only, and this build has three steps (four with the optional AI step), so you need Professional or higher ($29.99/month at the entry level). That is the total ongoing cost of the finished build, since Sheets and Gmail are part of your existing Google account.
• Permission to use Zapier with compliance data. Check your company's AI and automation policy and approved tool list first, because Zapier will see the digest text.
• A weekly LMS export with these columns: employee code, department, course, due date, completed yes or no
• About an hour for the first build

What goes through Zapier. This build is designed to keep names and email addresses out of the sheet completely. Use the employee code from your LMS, or make up a code in your export step. Compliance records may be audited, so keep the sheet in your company's approved Google storage, shared with nobody except the people who already see this data. Understand what Zapier itself receives, because it is more than the email. The lookup step returns the entire Summary row to Zapier, including the list of employee codes, department names and course names. Zapier keeps the data for each step of each run in Zap History, where anyone with access to your Zapier account can open it. If the optional AI step is on, the fields you map into its prompt also go to the AI provider that Zapier uses for that step. Ask IT or your security team whether Zapier is an approved tool before you connect any real data.

The Concept

Think of a noticeboard that reprints itself every Monday. You keep the raw list in one place (the Tracker tab). A second place (the Summary tab) holds a single line that is always there and always says something, even if the message is "None this week". Zapier is the courier: every Monday it walks to that one line, picks it up, and drops it in your mailbox.

Why one line that always exists? A Zapier search step that finds no matching row stops the run. Zap History shows that run as Safely halted, and the steps after it either do not run or fail because they are missing data. So a design that looks up "the overdue rows" would send nothing on a quiet week, and you could not tell a quiet week from a broken Zap. Here the lookup always finds the Summary row, so an email arrives every week. A missing Monday email then means something is actually wrong.

Build It Step by Step

Part 1: Lay out the sheet

Create one Google Sheet with three tabs. The tab names matter, because the formulas use them.

Set the header names in row 1 of each tab exactly as shown. Columns A to E of the Tracker are yours to paste into. Columns F to H hold formulas.

Format column D of the Tracker as a date (Format, then Number, then Date). Type the word yes or no in column E. Any value other than yes is treated as not completed, which is the safe way round.

Check File, then Settings, then Calculation. Set recalculation to "On change and every hour" if the sheet offers it. TODAY() only updates when the sheet recalculates, and Zapier reads the values as they were last calculated.

Part 2: Tracker formulas

Put these in row 2 of the Tracker and copy each down to row 500.

F2, Days Overdue

=IF(OR($A2="",ISBLANK($D2),$E2="yes"),"",MAX(0,TODAY()-$D2))

G2, Overdue

=IF($A2="","",IF($F2="","no",IF($F2>0,"yes","no")))

H2, Digest Line

=IF($G2="yes",$A2&" | "&$B2&" | "&$C2&" | due "&TEXT($D2,"yyyy-mm-dd")&" | "&$F2&" days overdue","")

The Digest Line joins fields with " | " so a comma inside a course name cannot break anything. TEXT() turns the date into readable text, because a date joined directly into text appears as a five-digit serial number.

Walk each formula through four rows. Assume the sheet is calculated on Monday 2026-10-05.

• Overdue row: A is filled, D is a date, E is not yes. So F returns TODAY() minus the due date, which is 17. G sees 17 is above 0 and says yes. H builds the line.
• Not-yet-due row: TODAY() minus the due date is negative 7, and MAX(0, ...) turns it into 0. G sees 0 is not above 0 and says no. A row due today also shows 0 and is not overdue.
• Completed row: E is yes, so the OR in F is true and F stays empty. G sees the empty F and says no.
• Blank due date row: ISBLANK(D2) is true, so F stays empty and G says no. This row is not counted as overdue, which is why the Summary tab has a separate Missing Due Dates count. Otherwise a row with no due date would disappear silently.

Part 3: Lists tab formulas

In Lists, type your department names in A2 to A11 (up to ten, spelled exactly as in your LMS export). Put these in row 2 and copy them down to row 11.

B2, Overdue Count

=IF($A2="","",COUNTIFS(Tracker!$B$2:$B$500,$A2,Tracker!$G$2:$G$500,"yes"))

C2, Line

=IF($A2="","",IF($B2=0,"",$A2&": "&$B2))

With the four example rows plus a second overdue Sales row (E-150, Code of Conduct, due 2026-09-25), the Sales row gives B = 2 and C = "Sales: 2". Support, Finance and Operations give B = 0 and C empty. An unused row (A blank) gives empty for both.

Part 4: Summary tab formulas

Type the word summary (lowercase) in A2. Then enter these in row 2. There is no row 3. The Summary tab has exactly one data row.

B2, Overdue Count

=COUNTIF(Tracker!G2:G500,"yes")

C2, Digest Lines

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Tracker!H2:H500,Tracker!G2:G500="yes")),"None this week")

D2, By Department

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Lists!C2:C11,Lists!C2:C11<>"")),"None this week")

E2, Missing Due Dates

=COUNTIFS(Tracker!A2:A500,"<>",Tracker!D2:D500,"",Tracker!E2:E500,"<>yes")

F2, Unlisted Overdue

=B2-SUM(Lists!B2:B11)

G2, Rows In Tracker

=COUNTA(Tracker!A2:A500)

H2, Report Date

=TEXT(TODAY(),"yyyy-mm-dd")

Check each range before you move on. The two ranges inside the FILTER in C2 are both rows 2 to 500. The FILTER in D2 uses rows 2 to 11 on both sides. Every bracket is closed.

What the example data produces on 2026-10-05:

• B2: 2 (E-104 and E-150).
• C2: two lines, one per overdue row, separated by a line break. When nobody is overdue, FILTER finds nothing, IFERROR catches the error, and C2 reads "None this week".
• D2: "Sales: 2". On a quiet week it also reads "None this week".
• E2: 1 (the Operations row with no due date).
• F2: 0. If a department appears in the export that you did not list on the Lists tab, this number goes above 0, which tells you to add it.
• G2: 5 rows in the tracker, which you can compare with the row count of your export.

Part 5: Build the Zap

Open Zapier and create a new Zap.

1. Trigger: Choose Schedule by Zapier and pick the event Every Week. Pick the Day of the Week (Monday), the Time of Day (for example 8 in the morning), and the timezone. Zapier's trigger steps do not use tasks.
2. Action: Add Google Sheets and choose the event Lookup Spreadsheet Row. Connect your Google account. Set Spreadsheet to your tracker, Worksheet to Summary, Lookup column to Key, and Lookup value to summary. Run the test. It returns the one row, with the fields Overdue Count, Digest Lines, By Department, Missing Due Dates, Unlisted Overdue, Rows In Tracker and Report Date. If the fields do not appear, use the refresh option on the step so Zapier re-reads the header row.
3. Optional action: Add AI by Zapier and choose Analyze and Return Data. See Part 6.
4. Action: Add Gmail and choose Send Email. Fill in To with your own address. Subject: "Compliance overdue digest" followed by the Report Date field. If the step has a Body type option, choose plain text, because plain text keeps the line breaks. Build the body from the mapped fields:

Overdue count: [Overdue Count]
By department:
[By Department]

Details (employee code | department | course | due date | days overdue):
[Digest Lines]

Rows with no due date: [Missing Due Dates]
Overdue rows in a department not on the Lists tab: [Unlisted Overdue]
Rows in tracker: [Rows In Tracker]

In Zapier's editor, each square-bracket item is a field you insert from step 2 with the field picker. If you added the AI step, put its summary field at the top of the body.

1. Turn the Zap on.

Part 6: The optional AI summary

An AI by Zapier step can turn the counts into three sentences you can read on your phone. Choose the Standard model tier. Zapier's help page says a Standard step uses one task per run, and higher tiers use more, so an AI step can use more than one task a run. Zapier bills only action steps that run successfully. Triggers, and steps that error or halt, do not count. A run with the lookup, the AI step and the email therefore uses about three tasks, and two without the AI step.

Map only the counts into the prompt, not the Digest Lines. The AI step does not need employee codes to write a summary, and leaving them out keeps them away from the AI provider.

Write a three-sentence plain-English summary for a learning and development manager from these compliance figures. Use only the numbers given. Do not add advice or any figure that is not listed.

Overdue count: [Overdue Count]
Overdue by department: [By Department]
Rows with no due date: [Missing Due Dates]
Overdue rows in a department not on the list: [Unlisted Overdue]

Define one output field called Summary. Check the output against the sheet the first few weeks. Zapier keeps the AI step's input and output in Zap History along with every other step.

Part 7: Test the whole thing

1. Paste a small test export into the Tracker with one overdue row, one completed row and one blank-due-date row. Compare each formula result with the walk-through in Part 2.
2. Test the quiet week: set every row's Completed to yes. The Summary should show 0, "None this week" and "None this week". Run the Zap. The email should still arrive.
3. Delete the test data and paste a real export before you turn the Zap on.

Real Example: A Monday in October

Setup: A learning and development manager at a mid-sized company pastes the LMS export into the Tracker every Friday afternoon. The Zap runs Monday at 8.

Input: The Tracker holds a few hundred rows. Two are overdue: E-104 in Sales (17 days) and E-150 in Sales (10 days). One Operations row has no due date.

Output: At 8 the manager receives an email with the overdue count of 2, "Sales: 2", two detail lines, a note that one row has no due date, and zero unlisted overdue. The manager opens the LMS, finds the missing due date, and decides to message the Sales leader personally. Nothing else leaves the sheet.

Time saved: The weekly filter-and-count chore becomes a two-minute read. The manager still spends time on the human part: deciding what to tell each leader.

Overdue rows stay in the digest until you mark them yes in column E or the next export shows them as done. The automation never removes a row on its own.

What to Do When It Breaks

• A Monday passes with no email (the silent failure) → Open Zapier, then Zap History, and look at Monday's run. If there is no run at all, the Zap is off or the schedule is wrong, so check the toggle and the Day of the Week. If the run shows Safely halted, the Lookup step found no row with Key equal to summary. Check that A2 on the Summary tab still says summary, that the row still exists, and that the Worksheet in the step is Summary. If the run shows an error, open the step and read the message. Also check your Gmail spam folder and the Zap's owner, because a Zap owned by a person who left can lose its connections. To catch a stopped Zap, put a recurring reminder in your calendar for Monday at 9. If the digest is not there, look at Zap History straight away.
• The email arrives but the numbers look old → The sheet did not recalculate or you forgot to paste the new export. Check the Report Date and Rows In Tracker in the email. Set the calculation option to every hour.
• Dates show as five-digit numbers → A date in a formula was not wrapped in TEXT(). Recheck the H2 formula on the Tracker tab.
• Days Overdue shows #VALUE! → A pasted due date is text, not a date. Reformat column D as a date, or re-enter the dates.
• Unlisted Overdue is above zero → A department in the export is spelled differently from your Lists tab. Fix the spelling or add the department.
• The Zap turns itself off after errors → Zapier can turn a Zap off when it errors repeatedly. Read the error in Zap History, fix the cause and turn the Zap back on.
• Formula errors after adding rows past 500 → Extend every range from 500 to a higher number in all formulas, and keep all ranges in one FILTER the same height.

Variations

• Simpler version: Skip the AI step and the Lists tab. Send only the Overdue Count and Digest Lines.
• Extended version: Add a second sheet cell that lists overdue by course, using the same FILTER pattern, and add it to the email body.

What to Do Next

• This week: Run the test in Part 7. After that, paste a real export and watch the first Monday email closely.
• This month: Compare the digest with your LMS's own report for two weeks, and note any differences.
• Advanced: Build the vendor contract renewal alert with the same pattern. See the guide "Vendor Contract Renewal Alert".

Advanced guide for Learning & Development Manager professionals. These techniques use more sophisticated AI features that may require paid subscriptions.