Detect campus energy leaks with Google Sheets, Google Calendar and Gemini

Quick overview

Youtube Video: https://www.youtube.com/shorts/vwW0553ZAJE

This workflow monitors campus building power use by combining BMS telemetry with smart meter snapshots, Sheets baselines, Calendar occupancy signals, and local weather, then uses Gemini to diagnose after-hours energy leaks, alerts the right Slack channels, logs ROI to Sheets, and opens a CMMS work order.

How it works

1. Runs every 15 minutes and can also be triggered by a BMS telemetry webhook to start an energy monitoring cycle.
2. Pulls live smart meter snapshots from an internal HTTP API, then splits the payload into one item per campus building.
3. Looks up each building’s baseline thresholds and metadata in Google Sheets, checks the building’s Google Calendar for current events/holidays, and fetches current outdoor conditions from the Open-Meteo API.
4. Calculates the expected occupancy state, applies weather/grace-period adjustments, computes excess kW and financial waste estimates, and flags buildings that exceed the unoccupied baseline.
5. Keeps only active leaks (e.g., after-hours/holiday over-baseline conditions) and deduplicates alerts so each building only triggers once per hour.
6. Uses Google Gemini with a Google Sheets tool that queries historical HVAC override logs to produce a structured root-cause diagnosis, technician checklist, and CFO ROI headline.
7. Routes CRITICAL vs WARNING leaks to different Slack channels, appends the incident and savings estimates to a Google Sheets ROI ledger, and creates a CMMS work order via an HTTP request for critical alerts.

Setup

1. Configure the HTTP Header Auth credentials for the smart meter API endpoint and the CMMS (MaintainX) work order API, and update the URLs if your endpoints differ.
2. Add Google Service Account credentials and ensure the Google Sheets file contains the Building_Baselines, HVAC_Override_History, and CFO_Savings_Ledger tabs with the expected columns (e.g., building_id, calendar_id, baselines, rate_per_kwh, latitude/longitude, grace_period_mins).
3. Add Google Calendar OAuth2 credentials and ensure each building record in the baseline sheet references a valid calendar ID.
4. Add Google Gemini (PaLM) credentials for the AI diagnosis step.
5. Add Slack credentials and update the target channel IDs for urgent dispatch and operations digest notifications.
6. If using the webhook trigger, copy the production webhook URL from n8n and configure your BMS to POST telemetry to /bms-telemetry-ingest.