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!
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); }
}
// 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); }
}