How to Build an AI Lead Processing Pipeline with Apify, Gemini & Google Sheets in n8n

Step 1 — When Executed by Another Workflow (Trigger)

This workflow runs as a sub-workflow called by a parent orchestrator. Using executeWorkflowTrigger with passthrough mode means any data the parent sends flows directly into this pipeline.

1. Add an Execute Workflow Trigger node.
2. Set Input Source to passthrough.
3. In your parent workflow, use an Execute Workflow node pointing to this workflow’s ID.

Step 2 — Trigger Apify Scrape (HTTP POST)

This HTTP Request node fires your Apify actor to start scraping.

1. Set Method to POST.
2. Set URL: https://api.apify.com/v2/acts/YOUR_ACTOR_ID/runs
3. Add Authorization header: Bearer YOUR_APIFY_API_TOKEN
4. Set Body to JSON with your actor’s input schema.

// Example: Apollo.io scraper input
{
  "searchUrl": "https://app.apollo.io/#/people?sortByField=...",
  "maxResults": 1000
}

Step 3 — Loop Over Items (SplitInBatches)

The SplitInBatches node processes data in chunks of 1,000 and routes through two paths.

1. Set Batch Size to 1000.
2. Set Options → Reset to false.

• Output 0 (current batch): routes to the AI Agent.
• Output 1 (done): routes to Wait → Apify GET → back to loop.

Step 4 — Wait 30s for Apify

Pauses 30 seconds for the Apify actor to complete before polling results.

1. Set Amount to 30, Unit to Seconds.

Step 5 — Fetch Apify Results (HTTP GET)

Retrieves all scraped leads from the last Apify actor run.

1. Set Method to GET.
2. Set URL: https://api.apify.com/v2/acts/YOUR_ACTOR_ID/runs/last/dataset/items
3. Same Authorization header as Step 2.

// Example Apify lead record
{
  "name": "Sarah Thompson",
  "email": "sarah.thompson@acmecorp.com",
  "phone": "+1 512 867 5309",
  "company": "Acme Corp",
  "title": "VP of Marketing",
  "location": "Austin, TX"
}

Step 6 — AI Lead Processing Agent (Google Gemini)

A Langchain AI Agent powered by Google Gemini validates and deduplicates leads using two Google Sheets tool calls.

Per batch, the agent:

1. Validates required fields: Name, Email, Company Name.
2. Deduplicates by checking existing emails in the sheet.
3. Generates Lead IDs: AP-DDMMYY-xxxx (e.g. AP-110426-0001).
4. Formats phones (prefers mobile, wraps in quotes) and location (City, Country).
5. Writes valid leads to Google Sheets one row at a time.
6. Returns one consolidated Telegram summary for the entire batch.

Step 7 — Append Lead to Google Sheets (AI Tool)

AI-callable tool that appends a validated lead row. The agent calls this once per valid lead.

1. Set Operation to Append, connect your Google Sheets OAuth2 credential.
2. Set Document ID (from sheet URL) and Sheet Name.
3. Map columns via $fromAI('FieldName') expressions.

Step 8 — Check for Duplicate Leads (AI Tool)

AI-callable tool that reads existing emails to detect duplicates before writing.

1. Set Operation to Read Rows, same document and sheet as Append tool.
2. Connect as an AI Tool to the agent.

Step 9 — Send Batch Report via Telegram

Sends the AI agent’s structured summary to your Telegram chat.

1. Set Chat ID to your chat ID (from @userinfobot).
2. Set Text to ={{ $json.output }}.

Sample Telegram output:
✅ Batch Complete
Total Processed: 47 | Added: 41 | Flagged: 4 | Skipped: 2

Contacts Added:
- James Carter -- Acme Corp -- james.carter@acmecorp.com
- Emily Rodriguez -- TechVentures -- emily.r@techventures.io
- Michael Chen -- GrowthLabs -- m.chen@growthlabs.com
(+38 more)

⚠️ Flagged: Lead 12 missing LinkedIn; Lead 28 missing Job Title
❌ Skipped: Lead 5 missing Email; Lead 33 missing Company Name