Analyze machine downtime and OEE with Google Sheets, OpenAI and WhatsApp

Description

Quick overview
This workflow captures per-shift machine production and downtime data via an n8n Form or an authenticated webhook, calculates OEE and TPM Six Big Losses from Google Sheets data, uses OpenAI for structured root-cause analysis, then sends a WhatsApp report with a Pareto chart and updates maintenance work orders.

How it works
Accepts shift production and downtime entries from an n8n Form and/or an authenticated webhook from an IoT/PLC gateway, normalizes and validates the payload, and upserts it into a Google Sheets “Shift Log” tab.
Runs on a schedule 15 minutes after each shift end to identify the completed shift and load the “Machine Master” and “Shift Log” data from Google Sheets.
Calculates plant and per-machine Availability, Performance, Quality, and OEE, breaks downtime into the TPM Six Big Losses, and flags critical machines and anomalies against a rolling 7-day baseline.
If no shift data is found, sends a WhatsApp alert to the plant head listing missing machine logs.
If shift data is found, sends the shift KPIs to an OpenAI-powered agent that queries Google Sheets “OEE History” and “Maintenance Tickets” to generate a structured RCA report with assignable actions.
Builds a WhatsApp-ready summary plus a QuickChart Pareto image URL, sends the report and chart to the plant head via WhatsApp, and appends per-machine KPI rows to the Google Sheets “OEE History” tab.
For each critical machine, looks up open tickets in Google Sheets and either updates the existing ticket or creates a new work order in the “Maintenance Tickets” tab, then sends a combined WhatsApp escalation message to maintenance.

Setup
Create a Google Sheets workbook with tabs named “Machine Master”, “Shift Log”, “OEE History”, and “Maintenance Tickets”, and populate “Machine Master” with machine_id (and optional line, ideal_cycle_time_sec, and active flags).
Add Google Sheets credentials and replace all REPLACE_WITH_GOOGLE_SHEET_ID values with your spreadsheet ID across the workflow.
Add an OpenAI credential for the Chat Model used by the AI agent.
Add WhatsApp Business Cloud credentials, set your phoneNumberId, and fill in the plant head and maintenance recipient phone numbers in the shift configuration.
If using the IoT/PLC feed, configure header authentication for the webhook and point your gateway to the webhook URL path (oee-iot-feed) with the matching header token.
Review and adjust shift times/cron schedule, OEE targets, critical thresholds, anomaly z-threshold, and breakdown alert minutes in the shift identification step before activating.