Plan solar revenue recovery visits with Google Sheets, Gemini and WhatsApp

Description

Quick overview
Youtube Video: https://youtu.be/QH0AlnWC0sw?si=Ze4Kj0QfNike9iby

This workflow runs every morning and pulls solar project, visit, approval, parts, payment, and messaging history data from Sheets, uses Gemini to generate a revenue-unblocking dispatch plan, then validates it with guardrails, writes an action plan back to Google Sheets, and sends approved WhatsApp template messages.

How it works
Runs on a schedule at 7:00 AM Monday through Saturday.
Loads configuration values and reads Sites, Scheduled_Visits, Utility_Approvals, Technicians, Payment_Milestones, Parts_Required, and Outbound_Log from Google Sheets.
Joins the sheet data into a single context that calculates readiness blockers (utility approval, parts ETA, customer availability), technician proximity, and blocked/overdue payment totals.
Sends the pre-computed context to Google Gemini to produce a structured plan including technician visits, office actions, deferred/cancelled visits, and outbound messages for customers, technicians, the utility officer, and the office.
Applies deterministic guardrails to remove invalid visits and suppress outbound messages that violate rules like utility/parts constraints, availability, capacity limits, cooldowns, and payment reminder caps.
Flattens the validated plan into rows and appends a daily record set (summary, visit plan, actions, messages, and guardrail notes) to the Action_Plan sheet in Google Sheets.
Resolves recipient phone numbers from Google Sheets and config, enforces dry-run routing when enabled, sends WhatsApp template messages, and logs send results to the Outbound_Log sheet.

Setup
Create a Google Sheets workbook with the required tabs (Sites, Scheduled_Visits, Utility_Approvals, Technicians, Payment_Milestones, Parts_Required, Action_Plan, and Outbound_Log) and update the spreadsheet ID in the workflow config.
Add a Google Sheets Service Account credential with access to the spreadsheet and select it on all Google Sheets nodes.
Add a Google Gemini (Google PaLM) API credential for the agent’s chat model.
Add WhatsApp Business Cloud API credentials, set your WhatsApp Phone Number ID and approved template name/language in the workflow config, and ensure the template accepts one body parameter.
Fill in config values like send_mode (dry_run/live), test_phone, and office_phone, then activate the workflow and switch to live only after verifying the Action_Plan output and message logs.