Create a Custom Function to Identify Email Providers in Google Sheets

Have you ever needed to categorize email addresses by their provider? Whether you're analyzing your marketing campaign data or organizing your contact list, knowing the email provider can be invaluable. In this tutorial, I'll show you how to create a custom function in Google Sheets that automatically detects the email provider by analyzing the domain's MX records. This project should take about 15-20 minutes to complete.

Screenshot showing the custom function in action with a list of email addresses and their corresponding providers

Prerequisites

This tutorial assumes you're familiar with:

3 steps to create an email provider detection function

Step 1 — Create the Apps Script project
Step 2 — Add the custom function code
Step 3 — Use the function in your spreadsheet

Step 1 — Create the Apps Script project

Open Google Sheets
Click on Extensions → Apps Script
In the Apps Script editor, remove any existing code
Rename your project by clicking on "Untitled project" in the top-left corner

Step 2 — Add the custom function code

Copy and paste the following code into the Apps Script editor:

/**
* Custom array-compatible formula to identify email provider from MX records.
* Usage in Google Sheets:
* Single cell: =GETEMAILPROVIDER(A1)
* Range of cells: =GETEMAILPROVIDER(A1:A10)
*
* @param {string|string[][]} email The email address(es) to analyze
* @customfunction
*/
function GETEMAILPROVIDER(email) {
 // Handle array input through recursion
 if (Array.isArray(email)) {
   return email.map(row => {
     if (Array.isArray(row)) {
       return row.map(cell => GETEMAILPROVIDER(cell));
     }
     return GETEMAILPROVIDER(row);
   });
 }

 // Process single email
 try {
   // Input validation for single email
   if (!email || typeof email !== 'string') {
     return 'Invalid Input';
   }

   // Extract domain from email
   const domain = email.split('@')[1];
   if (!domain) {
     return 'Invalid Email';
   }

   // Look up MX records using Google's DNS API
   const url = `https://dns.google/resolve?name=${domain}&type=MX`;
   const response = UrlFetchApp.fetch(url);
   const result = JSON.parse(response.getContentText());

   if (!result.Answer) {
     return 'No MX Records';
   }

   // Get all MX records as a single lowercase string for easier matching
   const mxString = result.Answer
     .map(record => record.data.toLowerCase())
     .join(' ');

   // Identify provider based on MX records
   if (mxString.includes('google') || mxString.includes('googlemail')) {
     return domain === 'gmail.com' ? 'Gmail' : 'Google Workspace';
   }
   if (mxString.includes('outlook') ||
       mxString.includes('hotmail') ||
       mxString.includes('microsoft')) {
     return 'Microsoft 365/Outlook';
   }
   if (mxString.includes('yahoodns')) {
     return 'Yahoo';
   }
   if (mxString.includes('mail.icloud')) {
     return 'iCloud';
   }
   if (mxString.includes('aol.com')) {
     return 'AOL';
   }
   if (mxString.includes('zoho')) {
     return 'Zoho';
   }
   if (mxString.includes('proton')) {
     return 'ProtonMail';
   }
   if (mxString.includes('messagingengine')) {
     return 'FastMail';
   }
   if (mxString.includes('emailsrvr')) {
     return 'Rackspace';
   }
   if (mxString.includes('mx.yandex')) {
     return 'Yandex';
   }
   if (mxString.includes('secureserver.net')) {
     return 'GoDaddy';
   }
   if (mxString.includes('hostinger')) {
     return 'Hostinger';
   }
   if (mxString.includes('ovh.net')) {
     return 'OVH';
   }
   if (mxString.includes('ionos')) {
     return 'IONOS';
   }
   if (mxString.includes('privateemail')) {
     return 'Namecheap';
   }
   if (mxString.includes('amazonaws.com')) {
     return 'Amazon WorkMail';
   }
   if (mxString.includes('mailgun')) {
     return 'Mailgun';
   }
   if (mxString.includes('sendgrid')) {
     return 'SendGrid';
   }
   if (mxString.includes('your-server.de')) {
     return 'Hetzner';
   }
   if (mxString.includes('dreamhost')) {
     return 'DreamHost';
   }
   if (mxString.includes('mail.ru')) {
     return 'Mail.ru';
   }
   if (mxString.includes('gmx.net')) {
     return 'GMX';
   }
   if (mxString.includes('postmarkapp')) {
     return 'Postmark';
   }
   if (mxString.includes('mailjet')) {
     return 'Mailjet';
   }
   if (mxString.includes('hey.com')) {
     return 'Hey';
   }
   if (mxString.includes('qq.com')) {
     return 'Tencent';
   }
   if (mxString.includes('bluehost')) {
     return 'Bluehost';
   }
   if (mxString.includes('hostgator')) {
     return 'HostGator';
   }
   return 'Other';

 } catch (error) {
   return 'Lookup Failed';
 }
}

Save the script by clicking on the disk icon or pressing Ctrl + S (⌘ + S on Mac).

Note

To add support for additional email providers, insert a new if statement following the same pattern. For example, to add support for a provider that uses 'example.com' in its MX records, add: if (mxString.includes('example.com')) { return 'Example Provider'; }

Step 3 — Use the function in your spreadsheet

Return to your Google Sheets spreadsheet
In any cell, type =GETEMAILPROVIDER( and select a cell containing an email address (for example, =GETEMAILPROVIDER(A2)). The function works exactly like built-in Google Sheets functions such as UPPER or TRIM.

Autocomplete showing the GETEMAILPROVIDER function with its description

Note

You can analyze multiple email addresses at once by selecting a range of cells. For example, if your email addresses are in cells A2:A10, you can use =GETEMAILPROVIDER(A2:A10) to identify the provider for all emails in that range simultaneously

Rate Limits

Google's DNS API has rate limits. If you're analyzing many email addresses, spread out your requests over time or implement caching to avoid hitting these limits. To handle rate limits effectively, you can implement caching to store frequently accessed results

How the code works

Let me break down how this custom function works:

Function Declaration and Documentation

/**
* Custom array-compatible formula to identify email provider from MX records.
* Usage in Google Sheets:
* Single cell: =GETEMAILPROVIDER(A1)
* Range of cells: =GETEMAILPROVIDER(A1:A10)
*
* @param {string|string[][]} email The email address(es) to analyze
* @customfunction
*/

This section uses JSDoc to document the function and enable autocomplete in Google Sheets. The @customfunction tag tells Google Sheets this is a custom function.

Array Handling

if (Array.isArray(email)) {
  return email.map(row => {
    if (Array.isArray(row)) {
      return row.map(cell => GETEMAILPROVIDER(cell));
    }
    return GETEMAILPROVIDER(row);
  });
}

This section makes our function work with both single cells and ranges. It recursively processes each cell in the range.

Email Processing

const domain = email.split('@')[1];
if (!domain) {
  return 'Invalid Email';
}

We extract the domain part of the email address (everything after the @ symbol).

MX Record Lookup

const url = `https://dns.google/resolve?name=${domain}&type=MX`;
const response = UrlFetchApp.fetch(url);
const result = JSON.parse(response.getContentText());

We use Google's public DNS API to look up the MX (mail exchanger) records for the domain (not sure what an API is? Check out my beginner-friendly explanation). These records tell us which mail servers handle email for the domain.

Provider Detection

const mxString = result.Answer
  .map(record => record.data.toLowerCase())
  .join(' ');

if (mxString.includes('google') || mxString.includes('googlemail')) {
  return domain === 'gmail.com' ? 'Gmail' : 'Google Workspace';
}

We combine all MX records into a single string and look for specific keywords that identify different email providers.

Conclusion

In this tutorial, I showed you how to create a custom function that can automatically identify email providers by analyzing MX records. This can be incredibly useful for segmenting email lists, analyzing customer data, or just organizing your contacts.

Sign up to be notified when I publish new content

By signing up you agree to the Privacy Policy & Terms.