n8n Webhook: Clean & Validate Leads with Gemini + Google Sheets

Turn messy inbound leads into clean, validated data—automatically

This n8n workflow exposes a POST webhook that cleans and normalizes incoming lead records with Google Gemini, validates them against strict formatting rules, and then upserts valid leads into Google Sheets—or quarantines failures with an HTTP 422 response.

What this workflow does

• Receives a JSON record via a webhook endpoint.
• Defines a target field schema and cleaning rules for the incoming data.
• Uses Google Gemini (PaLM) chat model to transform the raw record into minified JSON that matches the target schema.
• Validates the Gemini output by parsing JSON and checking required fields plus:
  • Email format
  • Phone in E.164 format
  • Date in YYYY-MM-DD format
• If validation fails, it sends the previous output and specific validation errors back to Gemini for a second repair pass, then validates again.
• Upserts valid records into a Google Sheets tab using email as the key and returns the cleaned JSON to the caller.
• If records still fail after two attempts, it appends them to a Google Sheets “Quarantine” tab and returns HTTP 422.

Use cases

• SaaS lead capture where form submissions arrive inconsistent or poorly formatted
• Normalizing leads from multiple sources (web forms, landing pages, partner referrals) into one Google Sheets CRM
• Automations that must reject invalid lead payloads quickly (with 422) while logging bad data for review

Technical details

• Webhook (POST endpoint)
• Google Gemini (PaLM) credentials used for both cleaning passes
• Google Sheets OAuth2 for writing to “Clean Records” and “Quarantine” tabs
• Nodes used include: if, set, code, webhook, and sticky note

Setup: configure Gemini API credentials, set Google Sheets OAuth2, ensure the spreadsheet has “Clean Records” and “Quarantine” tabs, and update the target schema and rule text used by the workflow.