Validate staff certifications for upcoming sessions with Google Calendar and Slack

Quick overview This workflow runs weekly to check upcoming Google Calendar sessions against a Google Sheets roster and staffing map, validates required certifications using rules from an n8n Data Table, then posts a coverage report to Slack and emails renewal reminders via Gmail. How it works 1. Runs weekly on a schedule and loads configuration values like the Google Sheet ID, Calendar ID, Slack channel ID, and lookahead window. 2. Reads the staff roster (person, email, certification, expiry) and session staffing assignments from Google Sheets, and loads per-session coverage rules from an n8n Data Table. 3. Fetches all Google Calendar events in the lookahead window (including expanded recurring instances) and extracts a bracketed session code (for example, FA-101) from each event title. 4. Matches each event to staffing and rules using the session code, then compares each assigned staffer’s certification expiry date to the session date to calculate certified headcount and any shortfall. 5. Generates a Slack-ready report that lists under-covered sessions with a minimal renewal plan, warns about unmatched events that cannot be validated, and summarizes fully covered sessions. 6. Posts the report to Slack and sends one Gmail renewal email per staffer per lapsed certification, listing all affected upcoming sessions and the earliest renew-by date. Setup 1. Add OAuth credentials for Google Sheets, Google Calendar, Slack, and Gmail. 2. Update the configuration values for rosterSheetId, calendarId, slackChannelId, and lookaheadWeeks. 3. Create a Google Sheet with a Roster tab (columns: Person, Email, Certification, Expiry) and a Staffing tab (columns: SessionCode, Person), and ensure names and codes match exactly across tabs. 4. Populate the “Certification Lapse Radar Rules” n8n Data Table with sessiontype, requiredcert, and mincertified values, making sure requiredcert matches the Certification text used in the roster. 5. Ensure each relevant Google Calendar event title contains a SESSIONCODE token (for example, First Aid Refresher FA-101) so events can be matched and validated. Requirements - View access is enough on both the spreadsheet and the calendar, every Google node here reads and none of them write back. - A Gmail account cleared to email your staff, one message goes out per person per lapsed certification on every run with no batching or throttling. - A Slack app with the chat:write scope, and the channel's ID value rather than its name, the node resolves the channel in id mode so a name string will not match. - An n8n instance new enough to offer Data Tables, the coverage rules are read through a Data Table node and there is no Sheet fallback. - Person values spelled identically across the Roster and Staffing tabs, the join trims whitespace but is case sensitive, so Dana Ruiz, dana ruiz and D. Ruiz are three different people. - A numeric mincertified on every rule row, a blank or non-numeric value reads as zero and every session of that type then reports as covered no matter who is assigned. - An Email value on the roster row of anyone you expect to hear from the workflow, a lapsed staffer with no email still appears in the Slack fix line but is skipped when the notices are built. Customization - Widening lookaheadWeeks in Workflow Configuration catches lapses earlier, at the cost of pulling in every unrelated calendar event in the window, each of which prints its own unmatched warning line. - The title token pattern is the regex /(A-Za-z0-9-+)/ in Extract Session Code Token, edit it to fit a naming convention you already use instead of retitling every event, the captured token is uppercased so title case in event titles is fine. - Session type comes from sessionCode.split('-')0 in Validate Certs On Session Date, change the separator there, or drop the split to treat the whole code as the type. - The pass test in Validate Certs On Session Date is cert.expiry.toISODate() = sessionDateIso, set that const to sessionDate.plus(