Investigate fuel tank variances with Google Sheets, Gemini, and Slack

Description

Quick overview
Youtube Video: https://youtu.be/XHT46n4b1lg

This workflow receives a shift-close webhook and reconciles fuel tank wet-stock in Google Sheets, optionally using Google Gemini to explain anomalies, then logs results back to Google Sheets and posts investigation alerts to Slack.

How it works
Receives a POST webhook at shift close with a station_id and shift_id.
Looks up the station’s active tanks and the shift’s tank readings, nozzle totalizer sales, deliveries, and prior audit history from Google Sheets.
Calculates per-tank expected vs actual closing volume, variance, tolerance, direction (loss/surplus), baseline z-score, drift patterns, and a risk score, and flags data gaps or meter/delivery issues.
When the variance warrants review, sends the computed context to Google Gemini to produce a non-accusatory JSON investigation summary with likely causes and recommended actions.
Writes the full reconciliation record (including AI verdict/summary when present) to an Audit_Log sheet in Google Sheets.
Formats and posts Slack messages for tanks that need notification (alerts, critical variances, delivery shortfalls, or data gaps) and returns a JSON summary response to the webhook caller.

Setup
Create a Google Sheets Service Account credential in n8n and grant it access to the spreadsheet used for Tanks, Tank_Readings, Nozzle_Sales, Deliveries, and Audit_Log.
Update the Google Sheets document ID and ensure the sheet/tab names and required columns match what the workflow reads and appends.
Add a Google Gemini (Google PaLM) API credential for the LangChain Gemini chat model used for anomaly explanations.
Add Slack credentials, choose the target channel, and adjust the channel ID/message destination as needed.
Copy the webhook URL for the Shift Close endpoint and configure your POS/shift-close system to POST station_id and shift_id (and optionally shift_end) to it.