Export “To:” email addresses from Gmail

Posted on

Export “To:” email addresses from Gmail – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like Export “To:” email addresses from Gmail, and how to fix it.

I found a similar question here: Get e-mail addresses from Gmail messages received but the difference is that I want “To:” addresses, not “From:”.

I have a catch-all email address at my domain. Except I’m getting a ton of spam mail in there, so my host is telling me I need to setup individual email accounts for all the ones I’ve used. I have 100s of unique emails at my domain name that I’ve used, but no easy way to put them all in a list.

I’m trying to figure out how to do this with GmailApp API. I tried the below code, but it didn’t quite get me what I was looking for. I’d like to get all unique/distinct email addresses in my Gmail account to which I’ve received emails like *@mydomain.com.

function getEmails() { 
  // http://stackoverflow.com/a/12029701/1536038  
  // get all messages      
  var eMails = GmailApp.getMessagesForThreads(
    GmailApp.search('after:2015/1/14 before:2016/3/12'))
      .reduce(function(a, b) {return a.concat(b);})
      .map(function(eMails) {
    return eMails.getTo() 
  });

  // sort and filter for unique entries  
  var aEmails = eMails.sort().filter(function(el,j,a)
    {if(j==a.indexOf(el))return 1;return 0});  

  // create 2D-array
  var aUnique = new Array();  
  for(var k in aEmails) {
    aUnique.push([aEmails[k]]);
  }

  // add data to sheet
  SpreadsheetApp.getActiveSheet().getRange(1, 1, aUnique.length, 1)
    .setValues(aUnique);
} 

//////////////////
After getting the answer below, I’ve modified this code with paging. But it can still be improved by somehow incorporating the paging into the loop and being able to get all unique emails in one pass.

function GetAddresses ()
{
  // Get the active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();  

  // Create / empty the target sheet
  var sheetName = "Pixeltrics Emails";
  var sheet = ss.getSheetByName (sheetName) || ss.insertSheet (sheetName, ss.getSheets().length);
  sheet.clear();

  // Get all messages in a nested array (threads -> messages)
  var addressesOnly = [];
  var messageData = [];

  var pageSize = 500;
  var startIndex = 500;
   // Get all messages for the current batch of threads
     var eMails = GmailApp.getMessagesForThreads(
       GmailApp.search('to:(@mydomain.com)',startIndex,pageSize))
       .reduce(function(a, b) {return a.concat(b);})
      .map(function(eMails) {
        var matched = eMails.getTo().match(/<[^@]*@mydoman.com>/ig);
        return (matched ? matched : []);
      })
     .reduce(function(a, b) {return a.concat(b);});

 // lowercase, filter for unique entries, sort
  var aEmails = eMails.map(function(text) {return text.toLowerCase().slice(1, -1);})
                      .filter(function(el,j,a) {return j == a.indexOf(el)})
                      .sort();

  // create 2D-array
  aUnique = aEmails.map(function(el) {return [el];}); 

   // add data to sheet
  SpreadsheetApp.getActiveSheet().getRange(1, 1, aUnique.length, 1)
    .setValues(aUnique);
}

Answer :

The method getTo returns a string with names of recipients along with email addresses. You need to extract the addresses from it, specifically those matching *@mydomain.com. This is done with a regular expression below:

var matched = eMails.getTo().match(/<[^@]*@mydomain.com>/ig);

The result is an array or null; I replace null by empty array and then flatten the results again (reduce by concat).

Additionally, all addresses are made lowercase to correctly locate duplicates. The command slice(1,-1) gets rid of angle brackers around email. Then uniques are filtered and the result is sorted. (I think it’s better to sort after filtering out duplicates: a much smaller array to sort.)

And since we’re all about .map and .reduce in this function, I also used .map when creating a 2D array.

function getEmails() { 
  // modified from http://stackoverflow.com/a/12029701  
  // get all messages, extract matching addresses from To fields 
  var eMails = GmailApp.getMessagesForThreads(GmailApp.search('after:2015/1/14 before:2016/3/12'))
      .reduce(function(a, b) {return a.concat(b);})
      .map(function(eMails) {
        var matched = eMails.getTo().match(/<[^@]*@mydomain.com>/ig);
        return (matched ? matched : []);
      })
      .reduce(function(a, b) {return a.concat(b);});

  // lowercase, filter for unique entries, sort
  var aEmails = eMails.map(function(text) {return text.toLowerCase().slice(1, -1);})
                      .filter(function(el,j,a) {return j == a.indexOf(el)})
                      .sort();

  // create 2D-array
  aUnique = aEmails.map(function(el) {return [el];}); 

  // add data to sheet
  SpreadsheetApp.getActiveSheet().getRange(1, 1, aUnique.length, 1)
    .setValues(aUnique);
} 

Leave a Reply

Your email address will not be published.