Detect Missing Alcohol Test Records with Gemini, Gmail, Slack & Sheets

Find missing alcohol test records automatically—then notify drivers and managers in Gmail & Slack

This n8n workflow detects which rostered alcohol test checks are missing from your Google Sheets records, generates Japanese driver reminders with Google Gemini, emails them via Gmail, and posts a manager summary to Slack—without manual follow-ups.

What this workflow does

• Collect submissions: Drivers submit alcohol test results through an n8n Form. Each submission captures driver ID, shift type (before/after), reading, checker, and notes.
• Normalize and store records: The workflow creates a consistent record ID, timestamp, shift date, and a match key (driver + date + shift). It appends each submission to the TestRecords sheet in Google Sheets.
• Run a daily end-of-day check: At the end of each day, it loads that day’s roster and previously submitted test records from Google Sheets.
• Detect gaps: Using the match key, it compares rostered shifts to entries in TestRecords to find rostered checks with no corresponding submission.
• Generate reminders with Gemini: For each missing entry, it uses Google Gemini to draft a Japanese reminder to the driver and a one-line manager summary.
• Notify and log: The workflow sends the driver email via Gmail, posts the manager summary to Slack, and appends a “missing” row to MissingLog in Google Sheets.
• Confirm when complete: If no checks are missing, it posts a confirmation message to the manager’s Slack channel.

Use cases

• Daily compliance checks for alcohol testing programs
• Reducing missed follow-ups by auto-detecting absent submissions
• Centralized audit trail in Google Sheets (TestRecords + MissingLog)

Technical details

• Integrations / nodes: if, set, code, gmail, merge, slack
• APIs / credentials: Google Sheets OAuth2, Gmail OAuth2, Slack OAuth2, and Google Gemini (PaLM) API