Google Sheets for Email List Management: Formulas, Scripts and Workflows

Why Google Sheets for Email Lists

Google Sheets is free, collaborative and accessible from any device. For teams that do not need a full CRM or data warehouse, it serves as a practical email list management tool. Sheets handles lists of up to about 50,000 rows efficiently and can automate common tasks with built-in formulas and Apps Script:

Essential Formulas for Email Data

Cleaning and normalisation

Extraction and parsing

Validation checks

Deduplication

Domain analysis

Spreadsheet Structure

Recommended column layout

Multiple sheets for organisation

Apps Script Automation

Auto-clean new entries

function cleanEmailColumn() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange('A2:A' + sheet.getLastRow());
  var values = range.getValues();
  var cleanColumn = sheet.getRange('B2:B' + sheet.getLastRow());
  var cleanValues = [];

  for (var i = 0; i < values.length; i++) {
    var email = values[i][0];
    if (email && typeof email === 'string') {
      // Trim, lowercase, remove non-breaking spaces
      var cleaned = email.trim().toLowerCase()
        .replace(/ /g, '');
      cleanValues.push([cleaned]);
    } else {
      cleanValues.push(['']);
    }
  }

  cleanColumn.setValues(cleanValues);
}

Flag duplicates automatically

function flagDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var emailRange = sheet.getRange(
    'B2:B' + sheet.getLastRow()
  );
  var emails = emailRange.getValues();
  var dupColumn = sheet.getRange(
    'L2:L' + sheet.getLastRow()
  );
  var seen = {};
  var flags = [];

  for (var i = 0; i < emails.length; i++) {
    var email = emails[i][0];
    if (!email) {
      flags.push([false]);
      continue;
    }
    if (seen[email]) {
      flags.push([true]);
    } else {
      seen[email] = true;
      flags.push([false]);
    }
  }

  dupColumn.setValues(flags);
}

Check against suppression list

function checkSuppression() {
  var masterSheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Master List');
  var suppressionSheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Suppression');

  // Load suppression list into a set
  var suppRange = suppressionSheet.getRange(
    'A2:A' + suppressionSheet.getLastRow()
  );
  var suppEmails = suppRange.getValues();
  var suppSet = {};
  for (var i = 0; i < suppEmails.length; i++) {
    if (suppEmails[i][0]) {
      suppSet[suppEmails[i][0].toLowerCase().trim()] = true;
    }
  }

  // Check master list against suppression
  var emailRange = masterSheet.getRange(
    'B2:B' + masterSheet.getLastRow()
  );
  var emails = emailRange.getValues();
  var statusColumn = masterSheet.getRange(
    'N2:N' + masterSheet.getLastRow()
  );
  var statuses = statusColumn.getValues();

  for (var j = 0; j < emails.length; j++) {
    var email = emails[j][0];
    if (email && suppSet[email.toLowerCase().trim()]) {
      statuses[j][0] = 'suppressed';
    }
  }

  statusColumn.setValues(statuses);
}

Scheduled cleanup trigger

function createDailyTrigger() {
  ScriptApp.newTrigger('dailyCleanup')
    .timeBased()
    .everyDays(1)
    .atHour(6)
    .create();
}

function dailyCleanup() {
  cleanEmailColumn();
  flagDuplicates();
  checkSuppression();

  // Log the run
  var logSheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Audit Log');
  logSheet.appendRow([
    new Date(),
    'Daily cleanup',
    'Completed',
    Session.getActiveUser().getEmail()
  ]);
}

Google Forms Integration

Collecting emails via Google Forms

Form response processing

function onFormSubmit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getLastRow();

  // Get the email from the response
  var email = sheet.getRange(row, 2).getValue(); // Column B
  if (email) {
    // Clean and write to clean column
    var cleaned = email.trim().toLowerCase();
    sheet.getRange(row, 3).setValue(cleaned); // Column C

    // Extract domain
    var domain = cleaned.split('@')[1] || '';
    sheet.getRange(row, 4).setValue(domain); // Column D

    // Set date added
    sheet.getRange(row, 5).setValue(new Date()); // Column E

    // Set source
    sheet.getRange(row, 6).setValue('Google Form'); // Column F
  }
}

Integrating with Email Tools

Export formats

Export workflow

Collaborative Workflows

Sharing and permissions

Data validation rules

Importing Data from Email Extractor

After extracting emails from files using Email Extractor, download the results as CSV. Import the CSV into the "New Imports" sheet in your Google Sheets workbook (File > Import > Upload), then run the cleanup scripts to normalise, deduplicate and check against your suppression list before merging into the Master List.

Related Guides

• Spreadsheet Data Cleaning Techniques
• Excel Power Query for Email Data
• Removing Duplicates at Scale
• Normalizing Email Formats
• How to Clean Email List