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. An n8n automation workflow template by iamvaar.

Community Metrics

Categories

• Automation
• AI

Tags

• n8n
• workflow
• google-sheets
• whatsapp-business-cloud
• code
• ai-agent
• structured-output-parser
• google-gemini-chat-model

Capabilities

• 6 nodes

Information