Gmail – How to Find the First Mail of Every Contact in Gmail

gmailgmail-search

I would like to find the first mail by every Gmail contact I got mails from.

So the pseudo filter would be:

  • get all mails
  • group by from-address
  • filter each group to show the first mail
  • sort the query by time-stamp (DESC)

Is this somehow possible with the Gmail search function or otherwise?

Best Answer

With the following piece of code it is possible.

Code

function getFirstFrom() { 
  // get active sheet and clear contents
  var sh = SpreadsheetApp.getActiveSheet();
  sh.clear();

  //http://stackoverflow.com/a/12029701/1536038
  // get all messages and retrieve 'date' and 'from' 
  var eMails = GmailApp.getMessagesForThreads(
    GmailApp.search("label: all"))
      .reduce(function(a, b) {return a.concat(b);})
      .map(function(eMails) {
        return {date: new Date(eMails.getDate()), from: eMails.getFrom() } 
    });

  // get unique names
  var aNames = [];
  for(var i in eMails) {
    var fromName = eMails[i].from
    if(aNames.indexOf(fromName) === -1) {
      aNames.push(fromName);
    }
  }

  // retrieve first date
  var aInfo = [];  
  for(var k = 0, kLen = aNames.length; k < kLen; k++) {
    var checkDate = new Date();
    for(var j in eMails) {
      var fromDate = eMails[j].date, fromName = eMails[j].from;
      if(fromDate < checkDate && fromName == aNames[k]) {
        checkDate = fromDate;
      }
    }
    aInfo.push([aNames[k], checkDate]);
  } 

  // display result in sheet  
  sh.getRange(1, 1, aInfo.length, 2).setValues(aInfo.sort());
}

Note

Add this script into a Google Spreadsheet by selecting from the menu Tools>Script editor. Press the bug button and go through the authentication process. Press the play button to execute the script again.

Related Topic