January 22, 2026

Google Sheets + VerifiEmail: cleaner email lists

Lisa Granqvist Partner Workflow Automation Expert

Get a free AI assessment → ⬇️ Use template

Your email list quietly rots. A few old addresses here, a couple typos there, then suddenly you’re fighting bounces, spam-folder placement, and “why did replies drop?” confusion.

Sheets email validation usually hits growth marketers first, but agency teams managing multiple client lists and founders sending newsletters feel it too. And honestly, it’s not the big mistakes that hurt. It’s the small ones, repeated every week.

This n8n workflow checks every email in Google Sheets with VerifiEmail, labels it Valid/Invalid/Risky, then sends a clean weekly report. You’ll see what’s happening to your list, and you’ll stop guessing.

How This Automation Works

The full n8n workflow, from trigger to final output:

n8n Workflow Template: Google Sheets + VerifiEmail: cleaner email lists

Click to explore

flowchart LR

    subgraph sg0["Weekly Schedule (Friday 5PM) Flow"]
        direction LR
        n0["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Calculate Statistics"]
        n1@{ icon: "mdi:play-circle", form: "rounded", label: "Weekly Schedule (Friday 5PM)", pos: "b", h: 48 }
        n2@{ icon: "mdi:database", form: "rounded", label: "Read Email List", pos: "b", h: 48 }
        n3@{ icon: "mdi:swap-vertical", form: "rounded", label: "Process Each Email", pos: "b", h: 48 }
        n4@{ icon: "mdi:swap-horizontal", form: "rounded", label: "Validate Email Address", pos: "b", h: 48 }
        n5@{ icon: "mdi:swap-horizontal", form: "rounded", label: "Check Validation Result", pos: "b", h: 48 }
        n6["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Process Valid Email"]
        n7["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Process Invalid Email"]
        n8@{ icon: "mdi:database", form: "rounded", label: "Update Valid Status", pos: "b", h: 48 }
        n9@{ icon: "mdi:database", form: "rounded", label: "Update Invalid Status", pos: "b", h: 48 }
        n10@{ icon: "mdi:message-outline", form: "rounded", label: "Send Weekly Report", pos: "b", h: 48 }
        n11["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/merge.svg' width='40' height='40' /></div><br/>Merge1"]
        n11 --> n3
        n2 --> n3
        n3 --> n0
        n3 --> n4
        n6 --> n8
        n8 --> n11
        n0 --> n10
        n7 --> n9
        n9 --> n11
        n4 --> n5
        n5 --> n6
        n5 --> n7
        n1 --> n2
    end

    %% Styling
    classDef trigger fill:#e8f5e9,stroke:#388e3c,stroke-width:2px
    classDef ai fill:#e3f2fd,stroke:#1976d2,stroke-width:2px
    classDef aiModel fill:#e8eaf6,stroke:#3f51b5,stroke-width:2px
    classDef decision fill:#fff8e1,stroke:#f9a825,stroke-width:2px
    classDef database fill:#fce4ec,stroke:#c2185b,stroke-width:2px
    classDef api fill:#fff3e0,stroke:#e65100,stroke-width:2px
    classDef code fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px
    classDef disabled stroke-dasharray: 5 5,opacity: 0.5
    class n1 trigger
    class n4,n5 decision
    class n2,n8,n9 database
    class n0,n6,n7 code
    classDef customIcon fill:none,stroke:none
    class n0,n6,n7,n11 customIcon

The Problem: Dirty Email Lists Wreck Deliverability

Email list hygiene is one of those jobs that feels “important” but never “urgent.” So it gets skipped. Then your Google Sheet becomes a junk drawer of leads from forms, imports, event lists, scraped contacts, old CRM exports, and “I’ll clean it later” rows. Every send after that is a bit riskier. More bounces. More spam complaints. Lower sender reputation. Even valid subscribers start missing your emails because mailbox providers decide you’re not consistently trustworthy.

The friction compounds. Here’s where it breaks down.

Someone exports a list into Google Sheets, but no one checks if the domains even have mail servers.
Manual spot-checking catches obvious typos, yet catch-all and disposable emails slip through and inflate your “sent” numbers.
Different teammates “clean” lists differently, which means results are inconsistent and hard to trust.
By the time you notice bounce spikes, you’ve already damaged deliverability for the next campaign.

The Solution: Weekly Google Sheets Email Hygiene, Automatically

This workflow runs on a schedule (every Friday at 5 PM) and treats your Google Sheet like a living list, not a one-time export. It reads every row, validates each email through VerifiEmail, and then writes the outcome back into the sheet as a clear status: Valid, Invalid, or Risky. While it’s processing, it keeps a running internal tally of what it finds. After the last email is checked, it calculates a simple health score and compiles an HTML report you can actually skim. Then it emails that report to you automatically through Gmail.

The workflow starts with the scheduled trigger and pulls your email roster from Google Sheets. VerifiEmail runs checks like formatting, MX records, SMTP verification, disposable detection, and catch-all detection. Finally, n8n updates the sheet row-by-row and sends a weekly summary to your inbox.

What You Get: Automation vs. Results

What This Workflow Automates

Results You’ll Get

Pulls your email roster from Google Sheets on a weekly schedule.
Validates each email through VerifiEmail, one row at a time.
Routes each result into the right branch (Valid vs. Invalid/Risky) and updates status fields.
Calculates list stats and sends an HTML email report via Gmail.

Save about 2 hours every week compared to manual spot-checking and patchy tools.
Fewer hard bounces, which helps protect sender reputation over time.
A single source of truth in the sheet, so the whole team targets the same “clean” rows.
Clear “Risky” labeling for catch-all and disposable domains, so you can segment instead of guessing.
A weekly health score (0–100) that makes list quality obvious in seconds.

Example: What This Looks Like

Say your sheet has 800 leads from the past month. Manually, even a “quick” process is still a mess: export the list, upload to a validator, wait, download results, then copy statuses back into Sheets. Call it 10 minutes per batch, and you’ll do several batches plus cleanup, so you’re easily at about 2 hours. With this workflow, you add leads to the first three columns as you go, and Friday’s run handles validation automatically. You spend maybe 5 minutes reading the report and deciding what to do with Invalid and Risky rows.

What You’ll Need

n8n instance (try n8n Cloud free)
Self-hosting option if you prefer (Hostinger works well)
Google Sheets to store and update your email roster.
VerifiEmail to validate emails and flag risky domains.
VerifiEmail API key (get it from verifi.email).

Skill level: Beginner. You’ll connect credentials, select your spreadsheet, and edit the report recipient.

Don’t want to set this up yourself? Talk to an automation expert (free 15-minute consultation).

How It Works

A weekly schedule triggers the run. Every Friday at 5 PM, n8n starts the workflow automatically, so list hygiene doesn’t rely on someone remembering.

Your Google Sheet is pulled in once, then processed in a loop. The workflow fetches the roster and uses a “split in batches” pattern to process one email at a time, which keeps results clean and easier to debug.

Each email gets validated and categorized. VerifiEmail checks format, MX records, SMTP, disposable domains, and catch-all behavior, then an IF branch routes the row into the right status logic before updating the matching row_number in Sheets.

A report is generated and delivered. After the last row, n8n calculates totals plus a health score (0–100) and sends an HTML summary via Gmail so you can act fast.

You can easily modify the schedule to run daily instead of weekly based on your needs. See the full implementation guide below for customization options.

Step-by-Step Implementation Guide

Step 1: Configure the Scheduled Weekly Trigger

Set the workflow to run automatically every Friday at 5:00 PM.

Add the Scheduled Weekly Trigger node as the trigger.
Set the schedule rule to a cron expression of 0 17 * * 5 to run weekly at 5:00 PM.
Connect Scheduled Weekly Trigger to Fetch Email Roster.

Step 2: Connect Google Sheets

Pull the email list from Google Sheets and prepare it for validation.

Open Fetch Email Roster and select your spreadsheet Document.
Set Sheet Name to Sheet1 (gid 0).
Credential Required: Connect your googleSheetsOAuth2Api credentials in Fetch Email Roster.
Connect Fetch Email Roster to Iterate Email Batch.

Step 3: Configure Batch Processing and Email Verification

Process the list in batches, validate each email, and branch based on the result.

In Iterate Email Batch, keep the default batch settings unless you need to throttle processing.
Configure Verify Email Address with Email set to {{ $json.email }}.
Credential Required: Connect your verifiEmailApi credentials in Verify Email Address.
Set the Validation Branch Check condition to Boolean true with Left Value {{ $json.valid }}.
Connect Verify Email Address to Validation Branch Check, then connect the true output to Handle Approved Email and the false output to Handle Rejected Email.

Iterate Email Batch outputs to both Compute Metrics and Verify Email Address in parallel.

Step 4: Update Validation Results in Google Sheets

Write the validation results back into the spreadsheet for both approved and rejected emails.

In Update Approved Row, keep Operation set to update and map fields to: {{ $json.name }}, {{ $json.email }}, {{ $json.notes }}, {{ $json.status }}, {{ $json.checked_at }}, and {{ $json.row_number }}.
In Update Rejected Row, use the same field mappings and ensure Operation is update.
Credential Required: Connect your googleSheetsOAuth2Api credentials in both Update Approved Row and Update Rejected Row.
Connect Handle Approved Email → Update Approved Row → Combine Results.
Connect Handle Rejected Email → Update Rejected Row → Combine Results.
Connect Combine Results back to Iterate Email Batch to continue the loop.

⚠️ Common Pitfall: Make sure your sheet contains a row_number column for matching updates, or the update operations will fail.

Step 5: Set Up Metrics and Weekly Email Report

Compute validation statistics and send a weekly HTML report.

Keep the JavaScript in Compute Metrics as provided to calculate totals, percentages, and health scores.
Confirm Compute Metrics connects to Dispatch Weekly Report.
In Dispatch Weekly Report, set Send To to [YOUR_EMAIL].
Set Subject to =📧 Email Hygiene Report - {{ $now.format("MMM DD, YYYY") }}.
Keep the Message HTML template as-is to render the styled report using values like {{ $json.health_score }} and {{ $json.valid }}.
Credential Required: Connect your gmailOAuth2 credentials in Dispatch Weekly Report.

If you want the report to link to the correct sheet, update the fallback URL in Compute Metrics where https://docs.google.com/spreadsheets/d/[YOUR_ID]/edit#gid=0 appears.

Step 6: Test and Activate Your Workflow

Run a manual test to confirm validation, updates, and reporting work end-to-end.

Click Execute Workflow to run the flow from Scheduled Weekly Trigger or manually trigger Fetch Email Roster for a quick test.
Confirm rows in the sheet are updated by Update Approved Row and Update Rejected Row with status and notes.
Verify that Dispatch Weekly Report sends an email with the HTML report and correct statistics.
Once validated, toggle the workflow to Active so it runs automatically every Friday at 5:00 PM.

🔒

Unlock Full Step-by-Step Guide

Get the complete implementation guide + downloadable template

Common Gotchas

Google Sheets credentials can expire or need specific permissions. If things break, check the Google Sheets credential in n8n and confirm the spreadsheet is shared to the connected Google account.
If you’re using Wait nodes or external rendering, processing times vary. Bump up the wait duration if downstream nodes fail on empty responses.
VerifiEmail API errors are usually a key, quota, or rate-limit issue. Check the “Verify Email Address” node response and your VerifiEmail dashboard before changing the workflow logic.

Frequently Asked Questions

How long does it take to set up this Sheets email validation automation?

About 30 minutes if your Google and VerifiEmail accounts are ready.

Do I need coding skills to automate Sheets email validation?

No. You will connect credentials and pick the right spreadsheet and sheet tab. The only “technical” part is making sure your column names match exactly.

Is n8n free to use for this Sheets email validation workflow?

Yes. n8n has a free self-hosted option and a free trial on n8n Cloud. Cloud plans start at $20/month for higher volume. You’ll also need to factor in VerifiEmail API usage costs based on how many emails you validate each week.

Where can I host n8n to run this automation?

Two options: n8n Cloud (managed, easiest setup) or self-hosting on a VPS. For self-hosting, Hostinger VPS is affordable and handles n8n well. Self-hosting gives you unlimited executions but requires basic server management.

Can I customize this Sheets email validation workflow for daily checks instead of Fridays?

Yes, and it’s a simple change. Update the “Scheduled Weekly Trigger” cron expression to your preferred cadence (daily, Mondays only, first of the month). Common tweaks also include changing the HTML branding in the “Dispatch Weekly Report” Gmail node, and adding an archive tab that appends Invalid rows for recordkeeping.

Why is my VerifiEmail connection failing in this workflow?

Most of the time it’s an invalid or expired API key in the “Verify Email Address” node. Update the credential in n8n, then run a test with a simple address like [email protected] to confirm the API responds. If the key is fine, check for quota limits or throttling, especially on larger lists. Also confirm your workflow isn’t sending empty emails (blank cells), because validation APIs can reject empty inputs.

How many emails can this Sheets email validation automation handle?

A few thousand per run is typical, assuming your API plan supports it.

Is this Sheets email validation automation better than using Zapier or Make?

Often, yes, if you care about control and cost. n8n’s loop-and-branch approach is straightforward here: read rows, validate, update, merge results, then compute stats once at the end. On Zapier, multi-step looping and batch updates can get expensive fast, and you’re more constrained when you want custom scoring or richer HTML reports. Make can do it, but the scenario can become fiddly as your list grows and you add branches for “Risky” handling. If you want help choosing, Talk to an automation expert and you’ll get a clear recommendation based on your volume.

Clean lists make everything else work better, from deliverability to reporting to segmentation. Set this up once, let Friday’s run do the boring part, and keep your targeting sharp.

Need Help Setting This Up?

Our automation experts can build and customize this workflow for your specific needs. Free 15-minute consultation—no commitment required.

Lisa Granqvist

Workflow Automation Expert

Expert in workflow automation and no-code tools.

{
"id": "",
"meta": {
"instanceId": "e9d0021fc444007a9600e6059c3dd247617609762169b41d8be0802c2a963450"
},
"name": "Automated Email List Cleaning",
"tags": [],
"nodes": [
{
"id": "flowpast-topbar-9252",
"name": "Flowpast Branding",
"type": "n8n-nodes-base.stickyNote",
"position": [
0,
20
],
"parameters": {
"color": 7,
"width": 1280,
"height": 80,
"content": "## Flowpast.com | Automation Workflow Library\n**\ud83d\udcd6 Full tutorial & setup guide:** flowpast.com"
},
"typeVersion": 1
},
{
"id": "8a19fa06-c280-4907-a551-3b34ca0f993c",
"name": "Compute Metrics",
"type": "n8n-nodes-base.code",
"position": [
800,
565
],
"parameters": {
"jsCode": "// Get all processed items from the loop\nconst items = $input.all();\n\n// Log for debugging\nconsole.log(`Processing ${items.length} items for statistics`);\n\n// Initialize counters\nconst stats = {\n  total: items.length,\n  valid: 0,\n  invalid: 0,\n  risky: 0,\n  unknown: 0,\n  processed_at: new Date().toLocaleString('en-IN', {\n    dateStyle: 'full',\n    timeStyle: 'short'\n  }),\n  sheet_url: $('Fetch Email Roster').params?.documentId?.__rl?.cachedResultUrl || 'https://docs.google.com/spreadsheets/d/[YOUR_ID]/edit#gid=0'\n};\n\n// Count each category\nitems.forEach(item => {\n  const status = item.json.status;\n  \n  if (status === 'valid') {\n    stats.valid++;\n  } else if (status === 'invalid') {\n    stats.invalid++;\n  } else if (status === 'risky') {\n    stats.risky++;\n  } else {\n    stats.unknown++;\n  }\n});\n\n// Calculate percentages (handle division by zero)\nif (stats.total > 0) {\n  stats.valid_percentage = ((stats.valid / stats.total) * 100).toFixed(1);\n  stats.invalid_percentage = ((stats.invalid / stats.total) * 100).toFixed(1);\n  stats.risky_percentage = ((stats.risky / stats.total) * 100).toFixed(1);\n  stats.unknown_percentage = ((stats.unknown / stats.total) * 100).toFixed(1);\n} else {\n  stats.valid_percentage = '0.0';\n  stats.invalid_percentage = '0.0';\n  stats.risky_percentage = '0.0';\n  stats.unknown_percentage = '0.0';\n}\n\n// Calculate list health score (0-100)\n// Formula: \n// - Valid emails: +100 points per email (percentage of total)\n// - Invalid emails: -20 points per email (penalty)\n// - Risky emails: -10 points per email (smaller penalty)\nstats.health_score = Math.max(0, Math.min(100, Math.round(\n  (stats.valid / stats.total) * 100 - \n  (stats.invalid / stats.total) * 20 - \n  (stats.risky / stats.total) * 10\n)));\n\n// Determine health status\nif (stats.health_score >= 80) {\n  stats.health_status = 'Excellent \u2705';\n  stats.health_color = '#4CAF50';\n} else if (stats.health_score >= 60) {\n  stats.health_status = 'Good \u26a0\ufe0f';\n  stats.health_color = '#ff9800';\n} else {\n  stats.health_status = 'Needs Attention \u274c';\n  stats.health_color = '#f44336';\n}\n\n// Create summary text for notifications\nstats.summary = `\ud83d\udcca Email List Validation Complete\n\n\u2705 Valid: ${stats.valid} (${stats.valid_percentage}%)\n\u274c Invalid: ${stats.invalid} (${stats.invalid_percentage}%)\n\u26a0\ufe0f Risky: ${stats.risky} (${stats.risky_percentage}%)\n\u2753 Unknown: ${stats.unknown} (${stats.unknown_percentage}%)\n\n\ud83d\udcc8 List Health Score: ${stats.health_score}/100 - ${stats.health_status}\n\ud83d\udce7 Total Processed: ${stats.total} emails\n\ud83d\udd50 Completed: ${stats.processed_at}`;\n\n// Create detailed lists for the report\nstats.email_details = {\n  valid_emails: items\n    .filter(item => item.json.status === 'valid')\n    .map(item => item.json.email),\n  \n  invalid_emails: items\n    .filter(item => item.json.status === 'invalid')\n    .map(item => ({\n      email: item.json.email,\n      reason: item.json.notes\n    })),\n  \n  risky_emails: items\n    .filter(item => item.json.status === 'risky')\n    .map(item => ({\n      email: item.json.email,\n      reason: item.json.notes\n    }))\n};\n\n// Log summary for debugging\nconsole.log(stats.summary);\n\n// Return the complete statistics object\nreturn stats;"
},
"typeVersion": 2
},
{
"id": "d6827158-4e8d-4806-a04b-7f9f35384532",
"name": "Scheduled Weekly Trigger",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
1180,
120
],
"parameters": {
"rule": {
"interval": [
{
"field": "cronExpression",
"expression": "0 17 * * 5"
}
]
}
},
"typeVersion": 1.2
},
{
"id": "0084d5ab-cdf7-4f5e-969e-711eee3bbcd7",
"name": "Fetch Email Roster",
"type": "n8n-nodes-base.googleSheets",
"position": [
1125,
215
],
"parameters": {
"options": [],
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/[YOUR_ID]/edit#gid=0",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.7
},
{
"id": "6d5bacd8-88cc-4979-a445-eba35f7b60f6",
"name": "Iterate Email Batch",
"type": "n8n-nodes-base.splitInBatches",
"position": [
900,
125
],
"parameters": {
"options": []
},
"typeVersion": 3
},
{
"id": "e2bf73b2-46f6-4661-8c74-5297b37318ad",
"name": "Verify Email Address",
"type": "n8n-nodes-verifiemail.verifiEmail",
"position": [
765,
285
],
"parameters": {
"email": "={{ $json.email }}"
},
"credentials": {
"verifiEmailApi": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 1
},
{
"id": "63fdab5f-ce90-4700-a343-f5823f17a236",
"name": "Validation Branch Check",
"type": "n8n-nodes-base.if",
"position": [
575,
200
],
"parameters": {
"options": [],
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "a3f7a8d6-9219-44b7-be4b-0f8d5d5aa5c5",
"operator": {
"type": "boolean",
"operation": "true",
"singleValue": true
},
"leftValue": "={{ $json.valid }}",
"rightValue": ""
}
]
}
},
"typeVersion": 2.2
},
{
"id": "e71190c0-fdc1-49ac-ae76-742dbea0782d",
"name": "Handle Approved Email",
"type": "n8n-nodes-base.code",
"position": [
430,
120
],
"parameters": {
"jsCode": "// Get the validation result from current input\nconst validation = $input.first().json;\n\n// Get the ORIGINAL data from Loop Over Items node\nconst loopData = $('Iterate Email Batch').item.json;\n\n// Process valid email\nlet status = 'valid';\nlet notes = 'Email verified successfully';\n\n// Check for any warnings even if valid\nif (validation.details?.disposable === true) {\n  status = 'risky';\n  notes = 'Valid but disposable email address';\n}\n\nconst checkedAt = new Date().toLocaleString('en-IN', { \n  year: 'numeric',\n  month: '2-digit',\n  day: '2-digit',\n  hour: '2-digit',\n  minute: '2-digit',\n  hour12: true\n});\n\n// Return merged data - combining Loop data + validation results\nreturn {\n  row_number: loopData.row_number,\n  name: loopData.name,\n  email: loopData.email,\n  status: status,\n  checked_at: checkedAt,\n  notes: notes,\n  is_valid: true\n};"
},
"typeVersion": 2
},
{
"id": "08f26195-97eb-4cf0-989e-2b686c7e4bd7",
"name": "Handle Rejected Email",
"type": "n8n-nodes-base.code",
"position": [
490,
325
],
"parameters": {
"jsCode": "// Get the validation result\nconst validation = $input.first().json;\n\n// Get original data from Loop Over Items\nconst loopData = $('Iterate Email Batch').item.json;\n\n// Process invalid email\nlet status = 'invalid';\nlet notes = 'Email validation failed';\n\n// Get more specific reason from validation details\nif (validation.details) {\n  if (validation.details.validMxRecord === false) {\n    notes = 'Invalid - No mail server found';\n  } else if (validation.details.rfcCompliant === false) {\n    notes = 'Invalid - Incorrect email format';\n  } else if (validation.details.spoofFree === false) {\n    notes = 'Invalid - Possible spoof/fake email';\n  }\n}\n\nconst checkedAt = new Date().toLocaleString('en-IN', { \n  year: 'numeric',\n  month: '2-digit',\n  day: '2-digit',\n  hour: '2-digit',\n  minute: '2-digit',\n  hour12: true\n});\n\n// Return merged data\nreturn {\n  row_number: loopData.row_number,\n  name: loopData.name,\n  email: loopData.email,\n  status: status,\n  checked_at: checkedAt,\n  notes: notes,\n  is_valid: false\n};"
},
"typeVersion": 2
},
{
"id": "5e98a36d-31c7-446b-a1ed-9988d3cb3ef3",
"name": "Update Approved Row",
"type": "n8n-nodes-base.googleSheets",
"position": [
205,
120
],
"parameters": {
"columns": {
"value": {
"name": "={{ $json.name }}",
"email": "={{ $json.email }}",
"notes": "={{ $json.notes }}",
"status": "={{ $json.status }}",
"checked_at": "={{ $json.checked_at }}",
"row_number": "={{ $json.row_number }}"
},
"schema": [
{
"id": "name",
"type": "string",
"display": true,
"required": false,
"displayName": "name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "email",
"type": "string",
"display": true,
"required": false,
"displayName": "email",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "status",
"type": "string",
"display": true,
"required": false,
"displayName": "status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "checked_at",
"type": "string",
"display": true,
"required": false,
"displayName": "checked_at",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "notes",
"type": "string",
"display": true,
"required": false,
"displayName": "notes",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "number",
"display": true,
"removed": false,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"row_number"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": [],
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/[YOUR_ID]/edit#gid=0",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.7
},
{
"id": "790a9728-2083-402d-8334-8254924c2e29",
"name": "Update Rejected Row",
"type": "n8n-nodes-base.googleSheets",
"position": [
230,
355
],
"parameters": {
"columns": {
"value": {
"name": "={{ $json.name }}",
"email": "={{ $json.email }}",
"notes": "={{ $json.notes }}",
"status": "={{ $json.status }}",
"checked_at": "={{ $json.checked_at }}",
"row_number": "={{ $json.row_number }}"
},
"schema": [
{
"id": "name",
"type": "string",
"display": true,
"required": false,
"displayName": "name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "email",
"type": "string",
"display": true,
"required": false,
"displayName": "email",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "status",
"type": "string",
"display": true,
"required": false,
"displayName": "status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "checked_at",
"type": "string",
"display": true,
"required": false,
"displayName": "checked_at",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "notes",
"type": "string",
"display": true,
"required": false,
"displayName": "notes",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "number",
"display": true,
"removed": false,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"row_number"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": [],
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/[YOUR_ID]/edit#gid=0",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.7
},
{
"id": "11a7ea29-2e24-4f9c-91dc-27afa0ca46f7",
"name": "Dispatch Weekly Report",
"type": "n8n-nodes-base.gmail",
"position": [
655,
480
],
"webhookId": "",
"parameters": {
"sendTo": "[YOUR_EMAIL]",
"message": "=<!DOCTYPE html>\n<html>\n<head>\n  <meta charset=\"UTF-8\">\n  <style>\n    body { \n      font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Arial, sans-serif;\n      line-height: 1.6; \n      color: #333;\n      margin: 0;\n      padding: 0;\n      background-color: #f5f5f5;\n    }\n    .container { \n      max-width: 650px; \n      margin: 20px auto; \n      background: white;\n      border-radius: 12px;\n      overflow: hidden;\n      box-shadow: 0 4px 6px rgba(0,0,0,0.1);\n    }\n    .header { \n      background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);\n      color: white; \n      padding: 40px 30px; \n      text-align: center;\n    }\n    .header h1 {\n      margin: 0 0 10px 0;\n      font-size: 28px;\n      font-weight: 600;\n    }\n    .header p {\n      margin: 0;\n      opacity: 0.9;\n      font-size: 16px;\n    }\n    .content { \n      padding: 30px;\n    }\n    .health-score {\n      background: {{ $json.health_color }};\n      color: white;\n      padding: 30px;\n      border-radius: 12px;\n      text-align: center;\n      margin: 0 0 30px 0;\n      box-shadow: 0 4px 12px rgba(0,0,0,0.15);\n    }\n    .health-score-label {\n      font-size: 14px;\n      text-transform: uppercase;\n      letter-spacing: 1px;\n      opacity: 0.9;\n      margin-bottom: 10px;\n    }\n    .health-score-number {\n      font-size: 56px;\n      font-weight: bold;\n      margin: 15px 0;\n      line-height: 1;\n    }\n    .health-score-status {\n      font-size: 18px;\n      font-weight: 500;\n      opacity: 0.95;\n    }\n    .stats-grid {\n      display: grid;\n      grid-template-columns: repeat(2, 1fr);\n      gap: 15px;\n      margin: 30px 0;\n    }\n    .stat-card {\n      background: #f8f9fa;\n      padding: 20px;\n      border-radius: 10px;\n      text-align: center;\n      border: 2px solid #e9ecef;\n      transition: transform 0.2s;\n    }\n    .stat-card:hover {\n      transform: translateY(-2px);\n      box-shadow: 0 4px 8px rgba(0,0,0,0.1);\n    }\n    .stat-label {\n      color: #6c757d;\n      font-size: 13px;\n      text-transform: uppercase;\n      letter-spacing: 0.5px;\n      margin-bottom: 8px;\n      font-weight: 600;\n    }\n    .stat-number {\n      font-size: 32px;\n      font-weight: bold;\n      margin: 8px 0;\n      line-height: 1;\n    }\n    .stat-percentage {\n      color: #6c757d;\n      font-size: 14px;\n    }\n    .valid { color: #28a745; }\n    .invalid { color: #dc3545; }\n    .risky { color: #fd7e14; }\n    .action-section {\n      background: #fff3cd;\n      border-left: 4px solid #ffc107;\n      padding: 20px;\n      margin: 25px 0;\n      border-radius: 8px;\n    }\n    .action-section h3 {\n      margin: 0 0 15px 0;\n      color: #856404;\n      font-size: 18px;\n    }\n    .action-section ul {\n      margin: 10px 0;\n      padding-left: 20px;\n    }\n    .action-section li {\n      margin: 8px 0;\n      color: #856404;\n    }\n    .btn {\n      display: inline-block;\n      background: #667eea;\n      color: white !important;\n      padding: 14px 32px;\n      text-decoration: none;\n      border-radius: 8px;\n      margin: 20px 0;\n      font-weight: 600;\n      font-size: 16px;\n      box-shadow: 0 4px 12px rgba(102, 126, 234, 0.4);\n      transition: all 0.3s;\n    }\n    .btn:hover {\n      background: #5568d3;\n      transform: translateY(-2px);\n      box-shadow: 0 6px 16px rgba(102, 126, 234, 0.5);\n    }\n    .summary-box {\n      background: white;\n      padding: 20px;\n      border-radius: 10px;\n      margin: 20px 0;\n      border: 2px solid #e9ecef;\n    }\n    .summary-box h3 {\n      margin: 0 0 15px 0;\n      font-size: 18px;\n      color: #495057;\n    }\n    .summary-box pre {\n      white-space: pre-wrap;\n      font-family: 'SF Mono', 'Monaco', 'Courier New', monospace;\n      font-size: 14px;\n      line-height: 1.8;\n      margin: 0;\n      color: #495057;\n    }\n    .insights-box {\n      background: linear-gradient(135deg, #e3f2fd 0%, #f3e5f5 100%);\n      padding: 25px;\n      border-radius: 10px;\n      margin: 25px 0;\n    }\n    .insights-box h3 {\n      margin: 0 0 15px 0;\n      color: #1976d2;\n      font-size: 18px;\n    }\n    .insight-item {\n      margin: 12px 0;\n      padding-left: 25px;\n      position: relative;\n    }\n    .insight-item:before {\n      content: '\u2713';\n      position: absolute;\n      left: 0;\n      color: #1976d2;\n      font-weight: bold;\n      font-size: 18px;\n    }\n    .email-list {\n      background: #f8f9fa;\n      padding: 15px;\n      border-radius: 8px;\n      margin: 15px 0;\n      max-height: 200px;\n      overflow-y: auto;\n    }\n    .email-list h4 {\n      margin: 0 0 10px 0;\n      font-size: 14px;\n      color: #495057;\n      text-transform: uppercase;\n      letter-spacing: 0.5px;\n    }\n    .email-item {\n      padding: 8px 12px;\n      margin: 5px 0;\n      background: white;\n      border-radius: 6px;\n      font-size: 13px;\n      border-left: 3px solid #dee2e6;\n    }\n    .email-item.invalid {\n      border-left-color: #dc3545;\n    }\n    .email-reason {\n      color: #6c757d;\n      font-size: 12px;\n      margin-top: 4px;\n    }\n    .footer {\n      background: #f8f9fa;\n      text-align: center;\n      color: #6c757d;\n      font-size: 13px;\n      padding: 25px;\n      border-top: 1px solid #dee2e6;\n    }\n    .footer p {\n      margin: 5px 0;\n    }\n    @media only screen and (max-width: 600px) {\n      .stats-grid {\n        grid-template-columns: 1fr;\n      }\n      .container {\n        margin: 10px;\n      }\n      .content {\n        padding: 20px;\n      }\n    }\n  </style>\n<link rel="preload" as="image" href="https://flowpast.s3.eu-north-1.amazonaws.com/featured_blog_images/105978.webp"><link rel="preload" as="image" href="https://flowpast.s3.eu-north-1.amazonaws.com/featured_blog_images/105978.webp"></head>
\n<body>\n  <div class=\"container\">\n    <div class=\"header\">\n      <h1>\ud83e\uddf9 Email List Hygiene Report</h1>\n      <p>Automated Weekly Validation Results</p>\n    </div>\n    \n    <div class=\"content\">\n      <!-- Health Score Section -->\n      <div class=\"health-score\">\n        <div class=\"health-score-label\">List Health Score</div>\n        <div class=\"health-score-number\">{{ $json.health_score }}/100</div>\n        <div class=\"health-score-status\">{{ $json.health_status }}</div>\n      </div>\n\n      <!-- Statistics Grid -->\n      <div class=\"stats-grid\">\n        <div class=\"stat-card\">\n          <div class=\"stat-label\">Valid Emails</div>\n          <div class=\"stat-number valid\">{{ $json.valid }}</div>\n          <div class=\"stat-percentage\">{{ $json.valid_percentage }}%</div>\n        </div>\n        \n        <div class=\"stat-card\">\n          <div class=\"stat-label\">Invalid Emails</div>\n          <div class=\"stat-number invalid\">{{ $json.invalid }}</div>\n          <div class=\"stat-percentage\">{{ $json.invalid_percentage }}%</div>\n        </div>\n        \n        <div class=\"stat-card\">\n          <div class=\"stat-label\">Risky Emails</div>\n          <div class=\"stat-number risky\">{{ $json.risky }}</div>\n          <div class=\"stat-percentage\">{{ $json.risky_percentage }}%</div>\n        </div>\n        \n        <div class=\"stat-card\">\n          <div class=\"stat-label\">Total Processed</div>\n          <div class=\"stat-number\">{{ $json.total }}</div>\n          <div class=\"stat-percentage\">100%</div>\n        </div>\n      </div>\n\n      <!-- Action Required Section -->\n      <div class=\"action-section\">\n        <h3>\u26a1 Action Required</h3>\n        <ul>\n          <li><strong>{{ $json.invalid }}</strong> invalid email(s) detected and flagged</li>\n          <li><strong>{{ $json.risky }}</strong> risky email(s) found (disposable/catch-all)</li>\n          <li>All email statuses have been automatically updated in your sheet</li>\n          <li>Review flagged emails and consider removal from active campaigns</li>\n        </ul>\n      </div>\n\n      <!-- Invalid Emails List -->\n      {{ $json.invalid > 0 ? '<div class=\"email-list\"><h4>\u274c Invalid Emails Detected</h4>' : '' }}\n      {{ $json.email_details.invalid_emails.map(e => `<div class=\"email-item invalid\"><strong>${e.email}</strong><div class=\"email-reason\">${e.reason}</div></div>`).join('') }}\n      {{ $json.invalid > 0 ? '</div>' : '' }}\n\n      <!-- CTA Button -->\n      <div style=\"text-align: center;\">\n        <a href=\"{{ $json.sheet_url }}\" class=\"btn\">\ud83d\udcca View Full Report in Google Sheets</a>\n      </div>\n\n      <!-- Text Summary -->\n      <div class=\"summary-box\">\n        <h3>\ud83d\udccb Quick Summary</h3>\n        <pre>{{ $json.summary }}</pre>\n      </div>\n\n      <!-- Insights Section -->\n      <div class=\"insights-box\">\n        <h3>\ud83d\udca1 What This Means for Your Campaigns</h3>\n        <div class=\"insight-item\">\n          <strong>Improved Deliverability:</strong> Valid emails ensure better inbox placement and sender reputation\n        </div>\n        <div class=\"insight-item\">\n          <strong>Cost Savings:</strong> Reduced bounces mean lower email service costs and fewer blacklist risks\n        </div>\n        <div class=\"insight-item\">\n          <strong>Better Metrics:</strong> Clean lists lead to higher open rates, click rates, and engagement\n        </div>\n        <div class=\"insight-item\">\n          <strong>Compliance Ready:</strong> Maintain GDPR/CAN-SPAM compliance with verified contacts\n        </div>\n      </div>\n    </div>\n\n    <!-- Footer -->\n    <div class=\"footer\">\n      <p>\ud83e\udd16 <strong>Automated by n8n Email Hygiene System</strong></p>\n      <p>Report generated: {{ $json.processed_at }}</p>\n      <p style=\"margin-top: 15px; opacity: 0.7;\">This report runs automatically every Friday at 5:00 PM</p>\n    </div>\n  </div>\n</body>\n</html>",
"options": [],
"subject": "=\ud83d\udce7 Email Hygiene Report - {{ $now.format(\"MMM DD, YYYY\") }}"
},
"credentials": {
"gmailOAuth2": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 2.1
},
{
"id": "d30f5ad6-778c-4891-90fc-c58fa49612e9",
"name": "Combine Results",
"type": "n8n-nodes-base.merge",
"position": [
0,
200
],
"parameters": [],
"typeVersion": 3.2
}
],
"active": false,
"pinData": [],
"settings": {
"executionOrder": "v1"
},
"versionId": "",
"connections": {
"Combine Results": {
"main": [
[
{
"node": "Iterate Email Batch",
"type": "main",
"index": 0
}
]
]
},
"Fetch Email Roster": {
"main": [
[
{
"node": "Iterate Email Batch",
"type": "main",
"index": 0
}
]
]
},
"Iterate Email Batch": {
"main": [
[
{
"node": "Compute Metrics",
"type": "main",
"index": 0
}
],
[
{
"node": "Verify Email Address",
"type": "main",
"index": 0
}
]
]
},
"Handle Approved Email": {
"main": [
[
{
"node": "Update Approved Row",
"type": "main",
"index": 0
}
]
]
},
"Update Approved Row": {
"main": [
[
{
"node": "Combine Results",
"type": "main",
"index": 0
}
]
]
},
"Compute Metrics": {
"main": [
[
{
"node": "Dispatch Weekly Report",
"type": "main",
"index": 0
}
]
]
},
"Handle Rejected Email": {
"main": [
[
{
"node": "Update Rejected Row",
"type": "main",
"index": 0
}
]
]
},
"Update Rejected Row": {
"main": [
[
{
"node": "Combine Results",
"type": "main",
"index": 1
}
]
]
},
"Verify Email Address": {
"main": [
[
{
"node": "Validation Branch Check",
"type": "main",
"index": 0
}
]
]
},
"Validation Branch Check": {
"main": [
[
{
"node": "Handle Approved Email",
"type": "main",
"index": 0
}
],
[
{
"node": "Handle Rejected Email",
"type": "main",
"index": 0
}
]
]
},
"Scheduled Weekly Trigger": {
"main": [
[
{
"node": "Fetch Email Roster",
"type": "main",
"index": 0
}
]
]
}
}
}