Triage construction project risk with Google Sheets, Open-Meteo, Gemini, and Gmail

Description

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

This workflow runs every weekday morning, reads active construction projects and related data from Google Sheets, pulls a 7-day forecast from Open-Meteo, computes delivery and cost risk scores, and uses Google Gemini to generate a structured escalation decision that is logged and emailed via Gmail.

How it works
Runs on a cron schedule at 7:00 AM Monday–Friday.
Reads all projects marked Active from a Google Sheets “Projects” tab and fetches a 7-day weather forecast for each project from the Open-Meteo API using its latitude and longitude.
Loads supporting project data (budget/accounting, procurement, labor, site reports, and disputes) from other tabs in the same Google Sheets document.
Computes per-project schedule, procurement, labor, disputes, weather, reporting integrity, and budget risk metrics, then derives a rule-based decision (CONTINUE, INTERVENE, or STOP_REVIEW) plus the most impactful mitigation lever and deadline.
Sends the computed metrics to Google Gemini to produce a validated decision JSON (headline, reasons, critical action, and confidence) while enforcing guardrails on any decision override.
Appends the final decision record to a Google Sheets “Decision_Log” tab.
Emails the project manager via Gmail only when the decision is INTERVENE or STOP_REVIEW.

Setup
Create and connect a Google Sheets Service Account credential with access to the spreadsheet, and update the spreadsheet ID and tab names (Projects, Budget_Accounting, Procurement, Labor, Site_Reports, Disputes, Decision_Log) to match your file.
Ensure your sheets contain the expected columns (for example project_id, status, latitude/longitude, planned and actual progress, procurement promised dates and critical_path, labor headcounts, dispute status/severity, and budget totals/spend/commitments).
Add a Google Gemini (PaLM) API credential for the Gemini chat model used in the decision step.
Add a Gmail OAuth2 credential and set the recipient(s) and email content fields as needed for your PM notifications.