Automating lead scraping with n8n and Google Sheets

Finding and qualifying leads has always been one of the most time-consuming parts of sales and marketing. This workflow automates contact research end to end: an n8n pipeline that runs Apollo searches through Apify, cleans the results and stores them in Google Sheets, ready for the CRM.

Why automate lead scraping?

• Efficiency: generate hundreds of structured contacts in minutes instead of hours of manual research.
• Accuracy: eliminate typos, missed fields and formatting inconsistencies.
• Scalability: move from 50 to 500 leads by changing one parameter.
• Integration: results land directly in tools your sales process already uses.

Workflow overview

1. Trigger via another workflow’s execution.
2. Input setup with JSON parameters.
3. URL construction for the Apollo query.
4. Apify scraping execution.
5. Data extraction and cleaning.
6. Google Sheets storage.
7. Success notification.

Step 1: workflow triggering

The pipeline starts with a When Executed by Another Workflow node. That makes the scraper a reusable component: any campaign workflow can call it with its own parameters instead of duplicating the logic.

Step 2: JSON input preparation

A Set node structures the incoming parameters: job titles, geographic locations and industry keywords that define the target segment.

Step 3: Apollo URL construction

A Code node converts the structured input into a valid Apollo search URL, handling encoding and filter mapping:

• Job titles map to personTitles[]
• Locations map to personLocations[]
• Industry keywords map to qOrganizationKeywordTags[]

Step 4: Apify scraping

An HTTP Request node calls the Apify Apollo scraper actor, which can collect up to 500 records per run including personal and work email addresses where available.

Step 5: data extraction

A Set node isolates the fields that matter and drops the noise: full name, email address, LinkedIn profile URL, seniority, job title, company name, location, phone number where available, company website and industry.

Step 6: Google Sheets storage

Cleaned leads are appended to a designated Google Sheet. From there the list feeds CRM imports, enrichment tools or outreach sequences, and the sales team can review it without touching the pipeline.

Step 7: outcome reporting

A final node reports the result: “X new contacts have been added to the Google Sheet!” Simple, but it closes the loop and makes failures visible immediately.

Optimisation tips

• Filter strategically by title, location and industry; a smaller, more relevant list beats an inflated one.
• Watch API usage limits on both Apify and Apollo.
• Chain follow-up workflows for scoring or tagging before the CRM import.
• Trigger email sequences or LinkedIn campaigns automatically once new leads land.

Conclusion

This is an automated, scalable lead-generation pipeline that runs with a single click. It transforms hours of manual work into a repeatable system that feeds clean, ready-to-use leads straight into your sales pipeline, and because it is a reusable sub-workflow, every future campaign benefits from the same infrastructure.

The production version: qualification before volume

The workflow above describes the mechanical path, but the production design starts one step earlier. A useful lead is not simply a row containing a name and an email address. It is a person who fits an agreed account profile, has a plausible relationship to the buying problem and can be contacted lawfully in the relevant market. If those conditions are not explicit, automation only produces a larger pile for sales to reject.

Write the qualification contract before opening n8n. Define the industries, company sizes, countries, seniority bands, functional roles and exclusion rules. Decide which fields are mandatory and which may be unknown. Add an explanation field such as qualification_reason so a reviewer can see why the record passed. This makes the workflow testable. It also prevents a common failure mode where filters are changed casually for every campaign and nobody can later explain why quality moved.

I normally separate account qualification from contact qualification. First decide whether the company fits. Then decide which people within that company are likely to influence the problem. That separation prevents the system from collecting twenty contacts at a business that should never have entered the list.

Build a data contract for every row

Google Sheets is convenient as an approval surface, but it still needs a schema. Give each column one meaning and one format. A practical contract might include:

• source_record_id, which remains stable even if the person’s details change;
• source_url, so a reviewer can inspect where the record came from;
• company_domain, normalised to lowercase without protocol or path;
• first_name, last_name, job_title and location as separate fields;
• email_status, distinct from the email address itself;
• qualification_reason and qualification_score;
• campaign_id, captured_at and workflow_version for lineage;
• review_status, reviewer and reviewed_at for the human gate.

Normalisation should happen before deduplication. Two values such as https://example.com/ and www.example.com need to resolve to the same domain. Titles should be trimmed, whitespace collapsed and obvious encoding errors rejected. Do not guess missing emails or invent company properties. Unknown is a valid data state; fabricated certainty is not.

Deduplication that survives repeated campaigns

A row-level duplicate check inside one sheet is not enough. The same person can appear in another campaign, with a new title, a different source URL or a changed email. Use a hierarchy of identifiers. Prefer a stable provider ID when it is available. Then compare a normalised professional profile URL. After that, use a compound key such as lowercase email or full name plus company domain.

When a match is found, update the existing record only under explicit rules. Preserve the original capture date, append the new campaign identifier and record which fields changed. If the title has changed, that may be valuable buying context rather than a reason to overwrite history silently. For larger programmes, move the canonical record store from Sheets to the CRM or a small database and keep the sheet as a review queue.

Idempotency matters here. Running the same execution twice should not create twice the work. Store an execution ID, an input fingerprint and the result of each completed batch. If a network request times out after the provider accepted it, the workflow can check the prior execution before retrying.

Error handling and operational visibility

Production automation needs an error branch for each external dependency. Rate limits, expired credentials, changed API responses and temporary spreadsheet locks are normal operating events. Configure retry policies for transient failures, but send schema errors and authentication failures to a human immediately. Repeating a malformed request ten times only consumes quota.

Create a compact execution summary containing the input query, records received, records rejected, duplicates found, records awaiting review and errors. Send it to the channel where the owner already works. A workflow is not monitored because n8n has an execution history; it is monitored when a named person sees a meaningful signal and knows what to do next.

Keep rejected records with a reason for a limited period. Rejection data is useful for improving qualification rules. If most records fail because job titles are too broad, fix the search. If a provider starts returning incomplete company domains, the issue is the source or mapping rather than the sales team.

Privacy, terms and responsible outreach

Technical access does not automatically create permission to collect or contact. Review the source platform’s terms, applicable privacy law, legitimate-interest assessment and local electronic-marketing rules with qualified counsel. Minimise the fields you store. Define retention periods. Provide a suppression mechanism and make sure every downstream sequence honours it.

Never use the workflow to bypass access controls, scrape private areas or rotate identities to defeat limits. The sustainable approach is an approved API or licensed provider, a documented purpose and restrained collection. Contact data also needs role-based access. A public sheet link is not an acceptable prospect database.

The outreach itself should use the qualification context, not pretend to know the person personally. A good first message explains a relevant business observation and offers a useful next step. Automated false familiarity damages trust faster than manual generic outreach.

A staged rollout plan

Start with twenty records. Review every field and every qualification decision. Adjust the contract, then run one hundred and compare acceptance by sales. Only increase volume after the accepted-record rate, bounce risk and time saved are visible. Set a stop condition if quality falls below the threshold.

The first production week should require approval before CRM insertion. In the second stage, clearly qualified records can flow automatically while borderline records remain in the sheet. In the third stage, connect campaign enrolment only if suppression, ownership and consent rules are enforced centrally.

Measure more than the number of rows. Track provider acceptance, qualification acceptance, CRM duplicate rate, valid-email rate, positive response rate, meetings and pipeline. Cost per accepted lead is more informative than cost per scraped lead. Review those measures by source, campaign and workflow version so improvements can be attributed.

A pre-launch checklist

Before switching on the schedule, confirm that the search criteria have an owner, the data contract is documented, credentials use the smallest necessary permissions and test data cannot reach live outreach. Trigger rate limits intentionally. Remove one required field from a sample response and verify that the record is quarantined. Submit a duplicate from a previous campaign. Revoke a credential and confirm that an alert contains enough context to resolve the failure without exposing the secret.

Finally, document the pause control. A marketer should be able to stop new collection and downstream enrolment without editing nodes. Reliable automation is not defined by how quickly it runs when everything is perfect. It is defined by how predictably it behaves when sources change, data is incomplete and a human needs to intervene.