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. We will 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.

Prerequisites

This tutorial assumes you're familiar with:

• Creating your first Apps Script

• Custom functions for Google Sheets

• Returning values from custom functions

• Using UrlFetchApp to fetch data from an API

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 function 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) {

 if (Array.isArray(email)) {
   return email.map(function(row) {
     return Array.isArray(row) ? row.map(getEmailProvider) : getEmailProvider(row);
   });
 }

 if (!email || typeof email !== 'string' || email.indexOf('@') === -1) {
   return 'Invalid Input';
 }

 var domain = email.split('@')[1].toLowerCase();

 var domainProviderMap = {
   'gmail.com': 'Gmail',
   'outlook.com': 'Microsoft 365/Outlook',
   'hotmail.com': 'Microsoft 365/Outlook',
   'live.com': 'Microsoft 365/Outlook',
   'yahoo.com': 'Yahoo',
   'aol.com': 'Yahoo',
   'icloud.com': 'iCloud',
   'zoho.com': 'Zoho',
   'protonmail.com': 'ProtonMail',
   'pm.me': 'ProtonMail',
   'mail.ru': 'Mail.ru',
   'gmx.net': 'GMX',
   'gmx.com': 'GMX',
   'hey.com': 'Hey',
   'qq.com': 'Tencent'
 };

 if (domainProviderMap[domain]) {
   return domainProviderMap[domain];
 }

 // MX record lookup
 try {
   var url = 'https://dns.google/resolve?name=' + domain + '&type=MX';
   var response = UrlFetchApp.fetch(url);
   var result = JSON.parse(response.getContentText());

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

   var mxRecords = result.Answer
     .map(function(record) {
       return record.data.toLowerCase();
     })
     .join(' ');

   var mxProviderMap = {
     'google': 'Google Workspace',
     'outlook': 'Microsoft 365/Outlook',
     'microsoft': 'Microsoft 365/Outlook',
     'yahoodns': 'Yahoo',
     'mail.icloud': 'iCloud',
     'zoho': 'Zoho',
     'proton': 'ProtonMail',
     'messagingengine': 'FastMail',
     'emailsrvr': 'Rackspace',
     'mx.yandex': 'Yandex',
     'secureserver.net': 'GoDaddy',
     'hostinger': 'Hostinger',
     'ovh.net': 'OVH',
     'ionos': 'IONOS',
     'privateemail': 'Namecheap',
     'amazonaws.com': 'Amazon WorkMail',
     'mailgun': 'Mailgun',
     'sendgrid': 'SendGrid',
     'your-server.de': 'Hetzner',
     'dreamhost': 'DreamHost',
     'postmarkapp': 'Postmark',
     'mailjet': 'Mailjet',
     'bluehost': 'Bluehost',
     'hostgator': 'HostGator'
   };

   for (var provider in mxProviderMap) {
     if (mxRecords.indexOf(provider) !== -1) {
       return mxProviderMap[provider];
     }
   }

   return "Other";

 } catch (error) {
   console.error('Error during MX lookup:', error);
   return 'Lookup Failed';
 }
}

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

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.

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.