Dispatch snow and ice crews from forecasts with Open-Meteo, GPT-4o and Gmail

Quick overview

This workflow checks Open-Meteo forecasts for sites stored in Google Sheets and automatically dispatches snow/ice crews when per-site triggers are met, using OpenAI to generate crew briefs and client notices, then notifying teams via Gmail and Telegram and tracking status through confirmation and follow-up webhooks.

How it works

1. Runs every 3 hours on a schedule or on demand via a webhook to start a forecast sweep.
2. Reads the Sites tab in Google Sheets, validates required fields (IDs, coordinates, triggers, and emails), and sends invalid rows to an admin Gmail address, a Telegram chat, and the Errors tab.
3. Fetches a 2-day hourly forecast from Open-Meteo for each valid site and evaluates the next 12 hours against the site’s snow trigger and freezing-precipitation criteria.
4. Logs each sweep to the CheckLog tab in Google Sheets with counts of checked sites, dispatches, no-action sites, and rejected rows.
5. When one or more sites need dispatch, sends the dispatch list to an OpenAI chat model to generate a storm summary, crew brief, and client notice for each site.
6. Creates a dispatch entry in the DispatchLog tab and sends the crew brief and client notice via Gmail, while also posting a dispatch alert to Telegram.
7. Receives crew completion updates via a confirmation webhook, updates the matching DispatchLog row, emails the client a completion notice, and alerts the owner in Telegram (or reports errors if the dispatch ID is missing/unknown).
8. Runs a daily sweep (or manual follow-up webhook) to find dispatches still marked “dispatched” after 6 hours and sends a Gmail and Telegram nudge, or reports that all dispatches are confirmed.

Setup

1. Create a Google Sheets workbook with tabs named Sites, DispatchLog, CheckLog, and Errors, and ensure columns match the fields the workflow reads and writes (for example: site_id, lat, lon, trigger_cm, crew_email, contact_email, dispatched_at, status, completed_at).
2. Add Google Sheets OAuth credentials and update the spreadsheet document ID (and sheet names if different) in all Google Sheets steps.
3. Add an OpenAI API credential (or compatible OpenAI chat provider) and select the model used to generate dispatch briefs.
4. Add Gmail OAuth2 credentials and replace the admin/owner email addresses where reports and invalid-row alerts are sent.
5. Add Telegram bot credentials and update the target chat ID used for dispatch, error, and follow-up notifications.
6. Copy the webhook URLs for /snow/check, /snow/confirm, and /snow/followup and share them with your team or connect them to your preferred crew reporting method.