{"product_id":"detect-missing-alcohol-test-records-with-gemini-gmail-slack-sheets","title":"Detect Missing Alcohol Test Records with Gemini, Gmail, Slack \u0026 Sheets","description":"\u003ch3\u003eFind missing alcohol test records automatically—then notify drivers and managers in Gmail \u0026amp; Slack\u003c\/h3\u003e\n\u003cp\u003eThis 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.\u003c\/p\u003e\n\n\u003ch3\u003eWhat this workflow does\u003c\/h3\u003e\n\u003cul\u003e\n  \u003cli\u003e\n\u003cstrong\u003eCollect submissions:\u003c\/strong\u003e Drivers submit alcohol test results through an \u003cstrong\u003en8n Form\u003c\/strong\u003e. Each submission captures driver ID, shift type (before\/after), reading, checker, and notes.\u003c\/li\u003e\n  \u003cli\u003e\n\u003cstrong\u003eNormalize and store records:\u003c\/strong\u003e The workflow creates a consistent record ID, timestamp, shift date, and a \u003cstrong\u003ematch key\u003c\/strong\u003e (driver + date + shift). It appends each submission to the \u003cstrong\u003eTestRecords\u003c\/strong\u003e sheet in \u003cstrong\u003eGoogle Sheets\u003c\/strong\u003e.\u003c\/li\u003e\n  \u003cli\u003e\n\u003cstrong\u003eRun a daily end-of-day check:\u003c\/strong\u003e At the end of each day, it loads that day’s roster and previously submitted test records from Google Sheets.\u003c\/li\u003e\n  \u003cli\u003e\n\u003cstrong\u003eDetect gaps:\u003c\/strong\u003e Using the match key, it compares rostered shifts to entries in \u003cstrong\u003eTestRecords\u003c\/strong\u003e to find rostered checks with \u003cem\u003eno corresponding submission\u003c\/em\u003e.\u003c\/li\u003e\n  \u003cli\u003e\n\u003cstrong\u003eGenerate reminders with Gemini:\u003c\/strong\u003e For each missing entry, it uses \u003cstrong\u003eGoogle Gemini\u003c\/strong\u003e to draft a \u003cstrong\u003eJapanese\u003c\/strong\u003e reminder to the driver and a one-line manager summary.\u003c\/li\u003e\n  \u003cli\u003e\n\u003cstrong\u003eNotify and log:\u003c\/strong\u003e The workflow sends the driver email via \u003cstrong\u003eGmail\u003c\/strong\u003e, posts the manager summary to \u003cstrong\u003eSlack\u003c\/strong\u003e, and appends a “missing” row to \u003cstrong\u003eMissingLog\u003c\/strong\u003e in Google Sheets.\u003c\/li\u003e\n  \u003cli\u003e\n\u003cstrong\u003eConfirm when complete:\u003c\/strong\u003e If no checks are missing, it posts a confirmation message to the manager’s Slack channel.\u003c\/li\u003e\n\u003c\/ul\u003e\n\n\u003ch3\u003eUse cases\u003c\/h3\u003e\n\u003cul\u003e\n  \u003cli\u003eDaily compliance checks for alcohol testing programs\u003c\/li\u003e\n  \u003cli\u003eReducing missed follow-ups by auto-detecting absent submissions\u003c\/li\u003e\n  \u003cli\u003eCentralized audit trail in Google Sheets (TestRecords + MissingLog)\u003c\/li\u003e\n\u003c\/ul\u003e\n\n\u003ch3\u003eTechnical details\u003c\/h3\u003e\n\u003cul\u003e\n  \u003cli\u003e\n\u003cstrong\u003eIntegrations \/ nodes:\u003c\/strong\u003e if, set, code, gmail, merge, slack\u003c\/li\u003e\n  \u003cli\u003e\n\u003cstrong\u003eAPIs \/ credentials:\u003c\/strong\u003e Google Sheets OAuth2, Gmail OAuth2, Slack OAuth2, and \u003cstrong\u003eGoogle Gemini (PaLM) API\u003c\/strong\u003e\n\u003c\/li\u003e\n\u003c\/ul\u003e","brand":"N8N Commerce","offers":[{"title":"Default Title","offer_id":45967479963827,"sku":"N8N-19276","price":59.99,"currency_code":"GBP","in_stock":true}],"thumbnail_url":"\/\/cdn.shopify.com\/s\/files\/1\/0749\/6279\/6723\/files\/Ff2jcGDa2J-evgx3QandR_L2hNQ0vu.png?v=1788685421","url":"https:\/\/buyflowscripts.com\/products\/detect-missing-alcohol-test-records-with-gemini-gmail-slack-sheets","provider":"N8N Commerce","version":"1.0","type":"link"}