FALLING CATS CONSULTING
  • Home
  • About
  • Blog List
  • Videos
  • Useful Stuff
    • Email Preheaders
    • Who's ignoring emails?
  • Digital Creatives
  • Random Stuff
    • The Music
    • Really Random Stuff
  • Contact
  • Home
  • About
  • Blog List
  • Videos
  • Useful Stuff
    • Email Preheaders
    • Who's ignoring emails?
  • Digital Creatives
  • Random Stuff
    • The Music
    • Really Random Stuff
  • Contact
FALLING CATS CONSULTING

who's ignoring emails?

Picture
 TLDR: I send a lot of e-intro emails to connect people. Decided to see who are ignoring these so I don't bother them in future.
But how? With some spare time over this weird silly season I decided to brush off my #coding skills with some #googleapps #script writing. Very rusty but thanks to good documentation and Stack Overflow we're cooking with gas!

The code below is for Google Sheets. Basically it looks for intro emails that I have sent and where the recipient never responded to that email. Cell A1 on the sheet is a title that says "Email Addresses". The script shoves the baddies under there, sorted so you can see the repeat offenders as well as the email date.
​
Menu > Tools > Script Editor, then paste in the code. First time you run it you'll get scry messages about it wanting access to your Sheets and Emails - well yes that's the point. Agree!

You need to change 2 things:
​1 - what is your email subject search? Mine is *intro* - yours might be different.
2 - your ME will not be me!

function getEmails() {
  // VARIABLES
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var unrespondedCount = 0;
  var threads = GmailApp.search('subject:"*intro*"');
  var msgCount = 0;
  var sentTo = "";
  var rowCount = 2;
  const ME = "brad.deveson@gmail.com";
  const STARTTAG = "<";
  const ENDTAG = ">";
  // CLEAR UP OLD RESULTS
  var oldStuff = spreadsheet.getRange("A2:B999");
  oldStuff.clear();
  // MAIN LOOP for each thread of emails that matches the search criteria
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    msgCount = 0;
    // how many messages are their including the original?
    for (m = 0; m < messages.length; m++) {
      sentTo = messages[m].getTo();
      var sentDate = messages[m].getDate();
      msgCount++;
    }
    if (msgCount == 1) {
      // if just one message then we got no reply - who are the culprits who ignored our plea?
      // NOTE: sentTo might be "Brad <b@x>" or "Brad <b@x>, Jim <j@z>" etc - we just want the stuff in <>
      var person = "";
      var pos = 0;
      var len = 0;
      if (sentTo.search(STARTTAG) > 0) {
        while (sentTo.length || 0) {
          person = stripOut(sentTo, STARTTAG, ENDTAG); // strip out the email
          // add to spreadsheet
          if (updateList(spreadsheet, rowCount, person, sentDate, ME)) {
            unrespondedCount++;
            rowCount++;
          }
          // remove this email from the list
          pos = sentTo.search(ENDTAG);
          len = sentTo.length;
          sentTo = sentTo.substring(pos + 1, len);
        }
      }
      else {
        //found email with no < in it at all
        if (updateList(spreadsheet, rowCount, sentTo, sentDate, ME)) {
          unrespondedCount++;
          rowCount++;
        }
      }
    }
  }
  //SORT RESULTS
  var results = spreadsheet.getRange("A2:B" + Math.floor(unrespondedCount + 1).toString());
  Logger.log("A2:A" + Math.floor(unrespondedCount + 1).toString());
  results.sort(1);
}
function updateList(spreadsheet, rowCount, person, sentDate, ME) {
  // update list unless it's me
  if (person != ME) {
    var emailRecipients = spreadsheet.getRange(rowCount, 1);
    emailRecipients.setValue(person);
    var emailDate = spreadsheet.getRange(rowCount, 2);
    emailDate.setValue(sentDate);
    Logger.log(person);
    return true;
  }
  else {
    Logger.log("Ignored " + person);
    return false;
  }
}
function stripOut(lookIn, startFrom, endWith) {
  //grab text between the delimiters passed in
  var fromHere = lookIn.search(startFrom);
  var toHere = lookIn.search(endWith);
  if (fromHere == "") { return "" } else { return lookIn.substring(fromHere + 1, toHere); }
}