Validate Email Lists Weekly with Google Sheets, VerifiEmail and Gmail Reports

Created by

Jitesh Dugar

Last update

Last update 22 days ago

Overview

Validates email lists through automated checks, categorizes results as Valid/Invalid/Risky, updates Google Sheets in real-time, and delivers HTML reports. Runs every Friday at 5 PM via cron scheduling.

Workflow Architecture

Schedule Trigger → Read Google Sheets → Loop (Process Each Email)
    → Validate API → IF Branch (Valid/Invalid)
    → Update Google Sheets → Merge → Loop Back
    → Calculate Statistics → Send Email Report

Loop Mechanism

Split in Batches processes one email at a time
Each email: validate → branch → update sheet → merge → continue
Loop accumulates all results internally
"Done" output triggers statistics calculation after all emails processed

Health Score Formula

Score = (Valid% × 100) - (Invalid% × 20) - (Risky% × 10)
Bounded: 0-100

Ranges:

80-100: Excellent (green)
60-79: Good (orange)
0-59: Needs Attention (red)

Prerequisites

Required:

Google account with Sheets access
Email validation API (VerifiEmail)
n8n v1.0+

Google Sheet Structure

Column	Type	Filled By
row_number	Number	Auto-generated
name	Text	You
email	Text	You
status	Text	Workflow
checked_at	Text	Workflow
notes	Text	Workflow

Only populate first three columns.

Setup

1. Import Template

Import JSON file to n8n via Workflows → Add workflow → Import from File

2. Configure Credentials

Google Sheets OAuth2 (used by 3 nodes):

Create credential via any Google Sheets node
Grant spreadsheet permissions
Apply same credential to all Google Sheets nodes

Validation API:

Get API key from https://verifi.email
Add credential to "Validate Email Address" node

Gmail OAuth2:

Add credential to "Send Weekly Report" node
Grant email sending permissions

3. Connect Google Sheet

In all three Google Sheets nodes:

Select your spreadsheet from Document dropdown
Select sheet tab
Verify "Column to Match On" = row_number (for Update nodes)

4. Set Email Recipient

In "Send Weekly Report" node:

Change "Send To" from placeholder to your email
Optional: Add CC/BCC for multiple recipients

5. Test

Add 3-5 test emails (mix of valid/invalid)
Click "Execute Workflow"
Verify sheet updates and email arrives

6. Activate

Toggle "Active" switch. Workflow runs automatically every Friday at 5 PM.

Customization

Change Schedule:
Edit "Weekly Schedule" node cron expression:

Daily 9 AM: 0 9 * * *
Monday 5 PM: 0 17 * * 1
First of month: 0 9 1 * *

Email Design:
Edit HTML in "Send Weekly Report" message field. Modify colors (search hex codes), text, or add branding.

Archive Invalid Emails:
Add Google Sheets Append node after "Update Invalid Status" → create "Invalid_Archive" tab → append email, name, reason, date

Slack Notifications:
Add Slack node after email report → configure channel → use summary text from statistics

Rate Limiting:
Add Wait node (1-2 seconds) after validation for large lists to prevent API throttling

Troubleshooting

"Column not found":
Verify exact column names in sheet: row_number, name, email, status, checked_at, notes (case-sensitive)

Only processes 1 email:
Check Google Sheets node Range field is empty or set to include all rows. Verify "Use Header Row" enabled.

Wrong statistics:
Enable "Execute Once" in Calculate Statistics node settings (gear icon)

Email not arriving:
Check spam/promotions folder, verify Gmail credential authorized, confirm recipient address correct

API errors:
Verify API key valid, check quota not exceeded, test with simple email like [email protected]

Merge node error:
Confirm both Update nodes connect to Merge inputs (top and bottom). Check both branches execute successfully.

Validation Checks

Each email undergoes:

RFC 5322 format compliance
MX record existence (domain has mail servers)
SMTP verification (mailbox exists)
Disposable email service detection
Catch-all domain detection

Categories:

Valid: All checks passed
Invalid: Critical checks failed
Risky: Disposable or catch-all domain

Support

Check execution logs in n8n Executions tab for errors. Use "Execute Node" on individual nodes to isolate issues. Visit n8n community forum for additional help.