Validate Emails

Identify and list all invalid email addresses in email fields for data quality assurance

The script validates email addresses in your NocoDB database, ensuring they conform to standard email formatting rules. It scans through specified email fields and generates a report of all records with invalid email addresses, helping you maintain data quality and improve communication deliverability.

let settings = input.config({
    title: "Validate emails",
    description: "This script will list all invalid emails for a field you pick.",
    items: [
        input.config.table("table", { label: "Table" }),
        input.config.field("field", {
            parentTable: "table",
            label: "Email field",
        }),
    ],
});

let { table, field } = settings as {
    table: Table,
    field: Field
};

let emailRegex = /^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$/;

// Function to validate a single email
function validateEmail(email: string) {
    if (!email || typeof email !== 'string') {
        return { isValid: false, error: 'Empty or invalid data type' };
    }

    // Trim whitespace
    email = email.trim();

    if (email === '') {
        return { isValid: false, error: 'Empty after trimming whitespace' };
    }

    // Check length (email addresses shouldn't be longer than 254 characters)
    if (email.length > 254) {
        return { isValid: false, error: 'Email too long (>254 characters)' };
    }

    // Check for multiple @ symbols
    if ((email.match(/@/g) || []).length !== 1) {
        return { isValid: false, error: 'Invalid number of @ symbols' };
    }

    // Validate against regex
    if (!emailRegex.test(email)) {
        return { isValid: false, error: 'Invalid email format' };
    }

    // Check for consecutive dots
    if (email.includes('..')) {
        return { isValid: false, error: 'Consecutive dots not allowed' };
    }

    // Check if starts or ends with dot
    let [localPart, domain] = email.split('@');
    if (localPart.startsWith('.') || localPart.endsWith('.')) {
        return { isValid: false, error: 'Local part cannot start or end with dot' };
    }

    if (domain.startsWith('.') || domain.endsWith('.')) {
        return { isValid: false, error: 'Domain cannot start or end with dot' };
    }

    return { isValid: true, error: null };
}

// Function to handle multiple emails in one cell
function validateMultipleEmails(cellValue) {
    if (!cellValue || typeof cellValue !== 'string') {
        return [{ email: cellValue, isValid: false, error: 'Empty or invalid data type' }];
    }

    // Check if multiple emails are present (separated by common delimiters)
    let emails = [];
    let delimiters = /[,;|\n\r]/;

    if (delimiters.test(cellValue)) {
        // Multiple emails detected
        emails = cellValue.split(delimiters).map(email => email.trim()).filter(email => email !== '');
    } else {
        // Single email
        emails = [cellValue.trim()];
    }

    return emails.map(email => {
        let validation = validateEmail(email);
        return {
            email: email,
            isValid: validation.isValid,
            error: validation.error
        };
    });
}

// Fetch records from the selected table
let queryResult = await table.selectRecordsAsync({
    fields: [field],
});

// Handle pagination - load all records if there are more pages
while (queryResult.hasMoreRecords) {
    await queryResult.loadMoreRecords();
}

// Array to store validation results
let results = [];
let totalEmailsChecked = 0;
let totalInvalidEmails = 0;

// Process each record
for (let record of queryResult.records) {
    let recordName = record.name || record.id;
    let cellValue = record.getCellValue(field);

    // Validate emails in this cell
    let emailValidations = validateMultipleEmails(cellValue);

    for (let validation of emailValidations) {
        totalEmailsChecked++;

        if (!validation.isValid) {
            totalInvalidEmails++;
            results.push({
                Record: recordName,
                Email: validation.email || '(empty)',
                Error: validation.error,
                'Original Cell Value': cellValue || '(empty)'
            });
        }
    }
}

// Display results
if (results.length === 0) {
    output.text(
        `✅ All emails are valid! (${totalEmailsChecked} emails in ${queryResult.records.length} records validated)`
    );
} else {
    output.text(
        `❌ ${totalInvalidEmails} invalid emails found in ${results.length} entries. (${totalEmailsChecked} total emails in ${queryResult.records.length} records validated)`
    );

    // Group results by error type for better readability
    let errorGroups = {};
    for (let result of results) {
        if (!errorGroups[result.Error]) {
            errorGroups[result.Error] = [];
        }
        errorGroups[result.Error].push(result);
    }

    // Display grouped results
    for (let [errorType, items] of Object.entries(errorGroups)) {
        output.markdown(`\n**${errorType}** (${items.length} ${items.length === 1 ? 'email' : 'emails'}):`);
        output.table(items.map(item => ({
            Record: item.Record,
            Email: item.Email,
            'Original Cell Value': item['Original Cell Value']
        })));
    }
}

Use Cases

Data Quality Assurance: Ensure all email addresses in your database are properly formatted
Marketing Campaign Prep: Clean email lists before sending campaigns to improve deliverability
Contact Management: Identify and fix invalid email addresses in customer databases
Import Validation: Check imported data for email formatting issues
Compliance Auditing: Verify email data quality for regulatory compliance requirements

How it Works

Select Email Field: Choose the email field you want to validate
Run Validation: The script checks each email address against standard email format rules
Generate Report: View a list of all records with invalid email addresses
Review Results: Examine the invalid emails and their associated records

Requirements

An Email field or Text field containing email addresses
Records with email data to validate

Validation Rules

The script checks for common email format issues:

Missing @ symbol: userexample.com
Invalid domain format: user@domain
Multiple @ symbols: us@er@example.com
Invalid characters: user@exam<ple.com
Missing local part: @example.com
Missing domain: user@
Improper spacing: user @example.com

Example Output

Invalid Email Report:

Record ID | Email Address | Issue
----------|---------------|--------
123       | user.example.com | Missing @ symbol
145       | admin@domain | Invalid domain format
167       | test@@example.com | Multiple @ symbols
189       | contact@exam ple.com | Invalid spacing
201       | @example.com | Missing local part

Common Invalid Formats

Missing @ Symbol

❌ Invalid: johndoeexample.com
✅ Valid: johndoe@example.com

Incomplete Domain

❌ Invalid: admin@company
✅ Valid: admin@company.com

Extra Characters

❌ Invalid: user@exam<ple.com
✅ Valid: user@example.com

Spacing Issues

❌ Invalid: contact @example.com
✅ Valid: contact@example.com

Benefits

Data Quality: Maintain high-quality email data for better communication
Deliverability: Improve email campaign success rates by removing invalid addresses
Error Prevention: Catch email formatting issues before they cause problems
Compliance: Meet data quality standards for email marketing and communication
Efficiency: Quickly identify problematic emails across large datasets

Field Types

This script works with:

Email fields: Purpose-built email fields in NocoDB
Text fields: Any text field containing email addresses
Single Line Text: Standard text fields with email content