Bulk e-mail verification in n8n with Google Sheets
- Who it is for
- n8n users with lead lists in Google Sheets
- The problem
- Calling a verification API once per row is slow and runs into rate limits, while a bulk API needs a job, a polling loop and a reliable way to map results back to the right rows.
- The solution
- One workflow takes up to 5,000 rows that have no result yet, sends their unique addresses as a single Cold Leads bulk job, polls the job every 5 seconds, fetches the results, routes each row by status and writes status, score, reason and a next step into the sheet. Run it again for the next 5,000.
- What you get
- Four filled columns per row (verify_status, verify_score, verify_reason, next_step) and a workflow you can run again until the whole sheet is done.
Addresses, IDs and results in the examples are illustrative. example.com is reserved for documentation, so a real check of these addresses returns invalid.
What the workflow does
- Read leads (Google Sheets): all rows of the sheet, each with its row_number.
- Pick next batch (Code): rows without a verify_status, addresses trimmed and lower-cased, duplicates and cells that are not e-mail addresses removed, at most 5,000. If nothing is left, the workflow stops here.
- Create job (HTTP Request): POST /api/v1/verify/bulk with the addresses. The answer is 202 with the job id.
- Wait 5 s, then Get job status (HTTP Request): GET /api/v1/verify/jobs/{id}. Finished? (If) sends the flow back to the wait until the status is done or failed.
- Get results (HTTP Request): the same request with results=1, which adds one entry per address: email, status, score and reason.
- Match rows (Code): every sheet row whose address is in the results, including duplicates, with its row_number.
- Route by status (Switch): valid, risky and invalid each go to their own Google Sheets update, which writes the three result columns and next_step (send, review or do not send).
Prepare the sheet and the credentials
- The sheet needs a header row with the columns email, verify_status, verify_score, verify_reason and next_step (other columns can stay).
- A Google Sheets credential in n8n with access to the spreadsheet.
- A Header Auth credential for Cold Leads: Name x-api-key, Value your secret key (sk_…) from Settings → API keys. The API is part of the Business plan.
The workflow
Copy the JSON and paste it onto an empty n8n canvas, or save it as a file and import it. Then open the four Google Sheets nodes, replace the spreadsheet URL and the sheet name (Leads) with yours and select your Google credential, and select the Cold Leads Header Auth credential in the three HTTP Request nodes.
{
"name": "Cold Leads: bulk-verify a Google Sheet",
"nodes": [
{
"parameters": {},
"id": "2fbec667-d7ba-567e-90cc-f8f765280c69",
"name": "Manual Trigger",
"type": "n8n-nodes-base.manualTrigger",
"typeVersion": 1,
"position": [
0,
300
]
},
{
"parameters": {
"documentId": {
"__rl": true,
"value": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit",
"mode": "url"
},
"sheetName": {
"__rl": true,
"value": "Leads",
"mode": "name"
},
"options": {}
},
"id": "fee88922-46ce-5cc3-adeb-fdea47dee333",
"name": "Read leads",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.7,
"position": [
220,
300
]
},
{
"parameters": {
"jsCode": "// Up to 5,000 unique addresses from rows that have no verify_status yet\nconst EMAIL_COLUMN = 'email'; // header of the column that holds the addresses\nconst EMAIL = /^[a-z0-9!#$%&'*+/=?^_`{|}~.-]+@[a-z0-9.-]+\\.[a-z]{2,}$/;\nconst batch = new Set();\nfor (const row of $input.all()) {\n if (row.json.verify_status) continue;\n const email = String(row.json[EMAIL_COLUMN] ?? '').trim().toLowerCase();\n if (EMAIL.test(email)) batch.add(email);\n if (batch.size === 5000) break;\n}\nif (batch.size === 0) return []; // nothing left to check: the run ends here\nreturn [{ json: { emails: [...batch], name: 'Google Sheet ' + new Date().toISOString().slice(0, 16) } }];"
},
"id": "22648923-73f7-5ad0-9048-b3c8ab82bceb",
"name": "Pick next batch",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
440,
300
]
},
{
"parameters": {
"method": "POST",
"url": "https://coldleads.app/api/v1/verify/bulk",
"authentication": "genericCredentialType",
"genericAuthType": "httpHeaderAuth",
"sendBody": true,
"specifyBody": "json",
"jsonBody": "={{ JSON.stringify({ emails: $json.emails, name: $json.name }) }}",
"options": {}
},
"id": "21d28c08-d217-5483-a469-7d8e1bb2b55c",
"name": "Create job",
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 4.3,
"position": [
660,
300
]
},
{
"parameters": {
"resume": "timeInterval",
"amount": 5,
"unit": "seconds"
},
"id": "027cfbd6-d040-569f-891b-27e82afb10dc",
"name": "Wait 5 s",
"type": "n8n-nodes-base.wait",
"typeVersion": 1.1,
"position": [
880,
300
],
"webhookId": "bcfa0fa7-0ddb-5ca6-b4cd-8f8683f5d382"
},
{
"parameters": {
"url": "=https://coldleads.app/api/v1/verify/jobs/{{ $('Create job').first().json.id }}",
"authentication": "genericCredentialType",
"genericAuthType": "httpHeaderAuth",
"options": {}
},
"id": "c89c51ab-9e45-5c03-a7cd-6e07f77ba4a1",
"name": "Get job status",
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 4.3,
"position": [
1100,
300
]
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 3
},
"conditions": [
{
"id": "bd05712a-13ee-5582-b4f0-395e0b0fbd68",
"leftValue": "={{ $json.status }}",
"rightValue": "done",
"operator": {
"type": "string",
"operation": "equals"
}
},
{
"id": "704c81e4-6294-54fe-acba-fb6b793532ae",
"leftValue": "={{ $json.status }}",
"rightValue": "failed",
"operator": {
"type": "string",
"operation": "equals"
}
}
],
"combinator": "or"
},
"options": {}
},
"id": "53044579-5bee-56e5-be83-ac265b532b1f",
"name": "Finished?",
"type": "n8n-nodes-base.if",
"typeVersion": 2.3,
"position": [
1320,
300
]
},
{
"parameters": {
"url": "=https://coldleads.app/api/v1/verify/jobs/{{ $('Create job').first().json.id }}?results=1",
"authentication": "genericCredentialType",
"genericAuthType": "httpHeaderAuth",
"options": {}
},
"id": "510e5c01-080d-5c15-9efc-217a39166c2f",
"name": "Get results",
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 4.3,
"position": [
1540,
200
]
},
{
"parameters": {
"jsCode": "// Every sheet row whose address is in the job results, duplicates included\nconst EMAIL_COLUMN = 'email';\nconst results = $input.first().json.results ?? [];\nconst byEmail = new Map(results.map((r) => [r.email, r]));\nconst out = [];\nfor (const row of $('Read leads').all()) {\n const r = byEmail.get(String(row.json[EMAIL_COLUMN] ?? '').trim().toLowerCase());\n if (r) {\n out.push({ json: { row_number: row.json.row_number, verify_status: r.status, verify_score: r.score, verify_reason: r.reason } });\n }\n}\nreturn out;"
},
"id": "e2a52d57-ceb5-5001-8583-05108037b70e",
"name": "Match rows",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
1760,
200
]
},
{
"parameters": {
"rules": {
"values": [
{
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 3
},
"conditions": [
{
"id": "acffb8aa-dc93-5aa6-a4be-faaad80ee30d",
"leftValue": "={{ $json.verify_status }}",
"rightValue": "valid",
"operator": {
"type": "string",
"operation": "equals"
}
}
],
"combinator": "and"
},
"renameOutput": true,
"outputKey": "valid"
},
{
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 3
},
"conditions": [
{
"id": "1c9e110f-be13-5915-99eb-4effd919e365",
"leftValue": "={{ $json.verify_status }}",
"rightValue": "risky",
"operator": {
"type": "string",
"operation": "equals"
}
}
],
"combinator": "and"
},
"renameOutput": true,
"outputKey": "risky"
},
{
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 3
},
"conditions": [
{
"id": "d7c0e0eb-7605-5aae-a8ba-44763748f248",
"leftValue": "={{ $json.verify_status }}",
"rightValue": "invalid",
"operator": {
"type": "string",
"operation": "equals"
}
}
],
"combinator": "and"
},
"renameOutput": true,
"outputKey": "invalid"
}
]
},
"options": {}
},
"id": "05d0a0b1-98ee-51d2-94a9-70bca0ca8679",
"name": "Route by status",
"type": "n8n-nodes-base.switch",
"typeVersion": 3.4,
"position": [
1980,
200
]
},
{
"parameters": {
"operation": "update",
"documentId": {
"__rl": true,
"value": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit",
"mode": "url"
},
"sheetName": {
"__rl": true,
"value": "Leads",
"mode": "name"
},
"columns": {
"mappingMode": "defineBelow",
"value": {
"row_number": "={{ $json.row_number }}",
"verify_status": "={{ $json.verify_status }}",
"verify_score": "={{ $json.verify_score }}",
"verify_reason": "={{ $json.verify_reason }}",
"next_step": "send"
},
"matchingColumns": [
"row_number"
],
"schema": [
{
"id": "verify_status",
"displayName": "verify_status",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "verify_score",
"displayName": "verify_score",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "verify_reason",
"displayName": "verify_reason",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "next_step",
"displayName": "next_step",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "row_number",
"displayName": "row_number",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": true,
"readOnly": true,
"removed": false
}
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {}
},
"id": "e6d03101-c54b-5b31-a792-fc2ebb2e77b0",
"name": "Write valid",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.7,
"position": [
2200,
60
]
},
{
"parameters": {
"operation": "update",
"documentId": {
"__rl": true,
"value": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit",
"mode": "url"
},
"sheetName": {
"__rl": true,
"value": "Leads",
"mode": "name"
},
"columns": {
"mappingMode": "defineBelow",
"value": {
"row_number": "={{ $json.row_number }}",
"verify_status": "={{ $json.verify_status }}",
"verify_score": "={{ $json.verify_score }}",
"verify_reason": "={{ $json.verify_reason }}",
"next_step": "review"
},
"matchingColumns": [
"row_number"
],
"schema": [
{
"id": "verify_status",
"displayName": "verify_status",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "verify_score",
"displayName": "verify_score",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "verify_reason",
"displayName": "verify_reason",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "next_step",
"displayName": "next_step",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "row_number",
"displayName": "row_number",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": true,
"readOnly": true,
"removed": false
}
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {}
},
"id": "2961af6e-c325-5235-a349-13ff161cd9e9",
"name": "Write risky",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.7,
"position": [
2200,
220
]
},
{
"parameters": {
"operation": "update",
"documentId": {
"__rl": true,
"value": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit",
"mode": "url"
},
"sheetName": {
"__rl": true,
"value": "Leads",
"mode": "name"
},
"columns": {
"mappingMode": "defineBelow",
"value": {
"row_number": "={{ $json.row_number }}",
"verify_status": "={{ $json.verify_status }}",
"verify_score": "={{ $json.verify_score }}",
"verify_reason": "={{ $json.verify_reason }}",
"next_step": "do not send"
},
"matchingColumns": [
"row_number"
],
"schema": [
{
"id": "verify_status",
"displayName": "verify_status",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "verify_score",
"displayName": "verify_score",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "verify_reason",
"displayName": "verify_reason",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "next_step",
"displayName": "next_step",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "row_number",
"displayName": "row_number",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": true,
"readOnly": true,
"removed": false
}
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {}
},
"id": "521d6f36-f36a-5009-ace8-f22cf8924b43",
"name": "Write invalid",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.7,
"position": [
2200,
380
]
}
],
"connections": {
"Manual Trigger": {
"main": [
[
{
"node": "Read leads",
"type": "main",
"index": 0
}
]
]
},
"Read leads": {
"main": [
[
{
"node": "Pick next batch",
"type": "main",
"index": 0
}
]
]
},
"Pick next batch": {
"main": [
[
{
"node": "Create job",
"type": "main",
"index": 0
}
]
]
},
"Create job": {
"main": [
[
{
"node": "Wait 5 s",
"type": "main",
"index": 0
}
]
]
},
"Wait 5 s": {
"main": [
[
{
"node": "Get job status",
"type": "main",
"index": 0
}
]
]
},
"Get job status": {
"main": [
[
{
"node": "Finished?",
"type": "main",
"index": 0
}
]
]
},
"Finished?": {
"main": [
[
{
"node": "Get results",
"type": "main",
"index": 0
}
],
[
{
"node": "Wait 5 s",
"type": "main",
"index": 0
}
]
]
},
"Get results": {
"main": [
[
{
"node": "Match rows",
"type": "main",
"index": 0
}
]
]
},
"Match rows": {
"main": [
[
{
"node": "Route by status",
"type": "main",
"index": 0
}
]
]
},
"Route by status": {
"main": [
[
{
"node": "Write valid",
"type": "main",
"index": 0
}
],
[
{
"node": "Write risky",
"type": "main",
"index": 0
}
],
[
{
"node": "Write invalid",
"type": "main",
"index": 0
}
]
]
}
},
"pinData": {},
"settings": {
"executionOrder": "v1"
}
}How the polling loop works
- Each status request also verifies the next batch of up to 25 addresses before it answers, and a scheduled worker advances open jobs in the background, so the loop moves the job forward instead of only watching it.
- One request every 5 seconds is 12 a minute, far below the limit of 120 requests per minute per key.
- status failed means the job stopped early, usually because credits ran out. Its results so far are still written; the rows without a result keep an empty verify_status and are picked up by the next run.
- Only one job is open per run. An account may have five queued or running jobs at once, including jobs started in the web app; beyond that, Create job fails with 429 too_many_jobs.
Routing and write-back
| verify_status | next_step | Typical verify_reason values |
|---|---|---|
| valid | send | ok (a mail server accepted the mailbox) or smtp_unreachable (the domain accepts mail, the mailbox was not checked) |
| risky | review | catch_all, smtp_unknown, or a disposable domain (score 40 or lower) |
| invalid | do not send | mailbox_missing, no_mx or syntax |
- Rows are updated by row_number, so duplicates and mixed-case addresses in the sheet all receive the result of their single check.
- Cells that are not e-mail addresses are never sent and keep an empty verify_status; filter them out or fix them in the sheet.
- To act on a bucket, add nodes after its Switch output, for example to add valid rows to a campaign tool.
Limits and costs
- 1 credit per unique address in a job, whatever the result, including results from the 30-day cache. Duplicates and cells that are not addresses are removed before the job is created and cost nothing.
- Up to 5,000 addresses per job, five open jobs per account, 120 requests per minute per key. Job results come back as JSON only.
- Credits are checked when the job is created (402 no_credits if the balance is smaller than the job) and charged as addresses are processed.
- The Business plan costs $99 a month and includes the API and 10,000 credits a month; extra packs of 1,000 credits cost $5. A 20,000-row sheet with 20,000 unique addresses needs 20,000 credits: if none of the month's 10,000 were used yet, those plus 10 packs ($50).
FAQ
How do I verify more than 5,000 rows?
Run the workflow again: each run takes the next 5,000 rows without a verify_status. If you schedule it, choose an interval longer than a job takes, so two runs never pick up the same rows; they would be charged twice.
Is there a Cold Leads node for n8n?
No. The workflow uses the standard HTTP Request node with a Header Auth credential; nothing else is needed.
Why did a run end with status failed?
The account ran out of credits during the job. The results it had are written; add credits in Settings → Billing and run the workflow again to finish the remaining rows.
Can I check one address at a time instead?
Yes, with an HTTP Request node that calls POST /api/v1/verify for each item. It costs the same 1 credit per address, but each address is its own request, and a key allows 120 requests per minute.