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

Description

Email List Hygiene - Automated Weekly Validator

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.

Tags
email-validation marketing-automation data-cleaning google-sheets scheduled-workflow deliverability list-hygiene