How to find the first mail of every contact within Gmail

Question

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?

Answer 1

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.

Answer 2

I extended the correct answer by getting the subject too.

Since we have the whole message object now, one can easily extend that to get all infos out of a mail.

This version has the subject with a link to the mail included.

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(), id: eMails.getId()} 
    });

  // 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();
    var id;
    for(var j in eMails) {
      var fromDate = eMails[j].date, fromName = eMails[j].from;
      if(fromDate < checkDate && fromName == aNames[k]) {
        checkDate = fromDate;
        id = eMails[j].id;
      }
    }
    var message = GmailApp.getMessageById(id);
    var subject = "" + message.getSubject();
    subject = replaceAll("\"", "'", subject);
    var mailLink = '=HYPERLINK("https://mail.google.com/mail/#all/' + id + '","' + subject + '")';
    aInfo.push([aNames[k], checkDate, mailLink]);
  } 

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

function replaceAll(find, replace, str) {
  return str.replace(new RegExp(find, 'g'), replace);
}