Monitor warehouse receiving performance with Google Sheets, Gemini, and Gmail

Quick overview This workflow runs hourly to analyze warehouse receiving logs from Google Sheets, calculates per-user performance and compliance metrics, enriches results with department data, uses Google Gemini to generate recommendations for flagged users, emails managers via Gmail when escalation is needed, and appends results to an analytics dashboard sheet. How it works 1. Runs every hour on a schedule trigger. 2. Reads receiving log rows from Google Sheets and normalizes key fields like duration, errors, and compliance. 3. Aggregates the log data by userid and computes performance status, severity score, and alert tier based on time, errors, and compliance rate. 4. Looks up each user’s department and manager email in a Google Sheets “Departments” tab and attaches this context to the analysis. 5. For users not rated “good,” sends the metrics to Google Gemini to generate structured root-cause and action-step recommendations. 6. If the alert tier is not “none,” sends an info/warning or critical escalation email via Gmail (using manageremail when available). 7. Appends the final per-user analysis (metrics, issues, recommendations, and whether an email was sent) to a Google Sheets “AnalyticsDashboard” tab. Setup 1. Connect Google Sheets OAuth2 credentials and set the spreadsheet and tab IDs for ReceivingLogs, Departments, and AnalyticsDashboard. 2. Ensure ReceivingLogs includes userid, duration, errors, and compliance columns, and ensure userid matches the Departments sheet. 3. Populate the Departments sheet with userid, department, and manageremail values for routing alerts. 4. Add Google Gemini (PaLM) API credentials for the recommendation step. 5. Add Gmail OAuth2 credentials and confirm the recipient behavior (manageremail fallback address) and email content meet your escalation requirements. Requirements - n8n instance (self-hosted or cloud) - Google Sheets account with: ReceivingLogs sheet, Departments sheet, AnalyticsDashboard sheet - Google Gemini API credentials - Gmail account (for sending emails) - Structured data with the following fields: userid, duration, errors, compliance Customization - Schedule Node: Change frequency (e.g., daily instead of hourly) - Performance Thresholds (Code Node): Adjust Duration limit (default: 15 min), Error threshold (default: 5) and Compliance threshold (default: 90%) - AI Prompt: Modify tone, format, or output structure - Email Content: Customize subject lines, messaging and Add CC/BCC recipients - Google Sheets Mapping: Add more fields or modify column mappings Additional info Who’s It For - Warehouse and logistics managers - Procurement and operations teams - Process improvement analysts - Businesses using Google Sheets for operational tracking - Organizations looking to automate performance monitoring Add-Ons & Enhancements - Slack or Microsoft Teams alerts - Dashboard visualization (Power BI / Looker Studio) - Historical trend analysis - Auto task creation in tools like Jira or Trello - Role-based escalation (multi-level approvals) Use Case Examples 1. Monitor warehouse staff performance in real-time 2. Detect compliance violations in procurement processes 3. Identify employees with frequent operational errors 4. Automate manager notifications for performance issues 5. Build a performance analytics dashboard for leadership There can be many more use cases depending on your business workflow and data structure. Troubleshooting Guide | Issue | Possible Cause | Solution | |------|--------------|---------| | No data fetched | Incorrect Google Sheets connection | Verify credentials and sheet ID | | Incorrect calculations | Data format mismatch | Ensure numeric fields are properly formatted | | AI not generating output | Gemini API issue | Check API key and quota | | Emails not sent | Gmail credentials missing | Reconnect Gmail OAuth | | Missing department info | userid mismatch | Ensure consistent IDs across sheets | | Workflow not triggering | Schedule misconfigured | Verify trigger