4

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?

4

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.

  • This is astonishing, I didn't know that it's possible to code macros like this in Google Docs! – Mathias Gorf Jul 29 '14 at 15:53
  • Would it be possible to extend the code to insert subject and body too? – Mathias Gorf Jul 29 '14 at 15:55
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);
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.