Pool Service Visit Report Automation with n8n, Sheets & Gemini

Automate Pool Service Visit Reports end-to-end with n8n, Google Sheets, and Gemini

Turn messy technician notes into structured, decision-ready pool service visit reports automatically—using an n8n Form intake, Google Sheets history lookup, and Google Gemini report extraction, then log visits/work orders and trigger alerts via Gmail, Slack, and Telegram.

What this workflow does

• Captures visit submissions: Receives pool service visit notes from an n8n Form including customer ID, technician notes, and optional water readings.
• Validates customer identity: Looks up the customer in Google Sheets. If the customer ID is unknown, it sends a Telegram alert.
• Enriches notes with history & flags issues: Pulls recent visit history from Google Sheets, combines it with the new readings, and generates rule-based flags for out-of-range chemistry and high filter pressure.
• Uses Google Gemini for structured reporting: Sends the combined context to Google Gemini to extract structured fields (condition, issues, actions, follow-up, summaries) and validates the output against a strict JSON schema.
• Creates the visit record in Sheets: Builds a visit record with an HTML customer report, calculates urgency/priority/next visit date, and appends it to the Google Sheets Visits table.
• Handles AI failure safely: If the AI step fails, logs a manual-review record and posts a Slack alert.
• Generates work orders & updates schedules: When follow-up is needed, creates entries in the Google Sheets Work Orders table and updates customer status/next visit date.
• Escalates urgency and approval: If urgent, sends operations alerts via Slack. If manager approval is required, sends an approve/hold link via Gmail and waits for the decision.

Use cases

• Pool technicians submit visit notes from the field; the system auto-creates reports and schedules follow-ups.
• Operations teams receive immediate Slack alerts for urgent visits and work-order creation.
• Managers use Gmail approve/hold decisions to control final actions when necessary.

Technical details

• Integrations / nodes: if, code, wait, gmail, slack, telegram
• Data sources: Google Sheets (customer lookup, visit history, Visits, Work Orders tables)
• AI step: Google Gemini with strict JSON schema validation