Please help me improve this Google Apps Script for reminding the user to contact people they haven't contacted in a while:
Purpose:
- Creates tasks for you to contact people you haven't talked to for a while.
- Adds a 'Touch Frequency (Days)' field to contacts - make a schedule per Contact
- New emails to/from a contact with make Task go away automatically
- "Complete" the task manually to signify a touch over a different channel (Phone, Chat, In-person)
Known issues:
- 'Touch Frequency (Days)' field not appearing in Google Contacts interface
- Sporadic execution errors "Data Storage Error", "Backend Error" - not sure why. But try it again and it works.
- Tasks are duplicated with each run - major issue - should we also search by task title as a backup if the task is missing (in case the DB was wiped out)?
- Slower and more reads than necessary, perhaps?
// http://code.google.com/p/touchminder
//
// Run TouchMinder() on a scheduled trigger as a Google Apps Script.
// You'll need to add an API Key for your Tasks Service integration (Easy: https://developers.google.com/apps-script/service_tasks, https://developers.google.com/console/help/)
//
// Creates tasks for you to contact people you haven't talked to for a while.
// Adds a 'Touch Frequency (Days)' field to contacts - make a schedule per Contact
// New emails to/from a contact with make Task go away automatically
// "Complete" the task manually to signify a touch over a different channel (Phone, Chat, In-person)
//
// Known issues:
// 'Touch Frequency (Days)' field not appearing
// Currently, this doesn't fully work. Sporadic execution errors "Data Storage Error", "Backend Error" - not sure why. But try it again and it works.
// Tasks are duplicated with each run - major issue
// Slower and more reads than necessary, perhaps?
function TouchMinder() {
Logger.log('Starting TouchMinder');
Logger.log('');
var db = ScriptDb.getMyDb(); // Get the db
// Cursor to [] of contactTouches ({ table: 'ContactTouches', contactId: 0, lastTouch: 0, taskId: 0 })
var contactTouches = [];
var queryResults = db.query({ table: 'ContactTouches' });
while(queryResults.hasNext()) { contactTouches.push(queryResults.next()); }
// Who am I?
var currentUserEmailAddress = Session.getActiveUser().getEmail();
var myId = ContactsApp.getContact(currentUserEmailAddress).getId();
var allCurrentUserEmailAddresses = ContactsApp
.getContact(currentUserEmailAddress)
.getEmails()
.map(function (ea) { return ea.getAddress().trim().toLowerCase(); });
// Cursor to [] of contactTouches ({ table: 'ContactTouches', ownerContactId: myId, contactId: contactId, lastTouch: number, taskId: taskId }) // Cannot store Date, so store milliseconds instead
// ownerContactId because I believe the scriptDb is shared by the domain in Google Apps accounts. If not, no real harm, right?.
var contactTouches = [];
var queryResults = db.query({ table: 'ContactTouches', ownerContactId: myId });
while(queryResults.hasNext()) { contactTouches.push(queryResults.next()); }
Logger.log('currentUserEmailAddresses: ' + allCurrentUserEmailAddresses.join(', '));
// load all contacts
var trackedContacts = loadContacts(contactTouches, myId);
var maxDays = Math.max.apply( Math, trackedContacts.map(function (c) { return c.frequency; }) ) || 0; // What's the furthest back we should search?
var earliestDate = new Date(); earliestDate.setDate(earliestDate.getDate() - maxDays);
Logger.log('Found ' + trackedContacts.length + ' trackedContacts');
Logger.log('');
var tasklistId = getTouchMinderTaskList();
var processedTasks = processCompletedTasksGetRemaining(tasklistId, db); // { completed: number, remaining: taskId[] }
Logger.log('Touched ' + processedTasks.completed + ' contacts from completed tasks.');
Logger.log('');
Logger.log('Searching back as far as ' + earliestDate.toLocaleString());
Logger.log('');
var messages = new IterateMessages('in:anywhere -in:drafts'); // Iterate through ALL emails, sent & received.
while (messages.moveNext()) {
try {
var message = messages.current.message;
var thread = messages.current.thread;
if (thread.getLastMessageDate() < earliestDate) break;
var sender = extractRawEmailAddresses(message.getFrom())[0];
var recipients = extractRawEmailAddresses(message.getTo() + ',' + message.getCc());
var date = message.getDate().getTime(); // number!
if (allCurrentUserEmailAddresses.filter(function (myEmail) { return sender === myEmail; }).length !== 0) { // Sent email
for (var r in recipients) {
var recipient = recipients[r];
var matchingContacts = trackedContacts.filter(function (tc) { // find matching contacts where the lastTouch is older than this date AND there's an email match
return tc.contactTouch.lastTouch < date &&
0 < tc.allEmails.filter(function (ea) { return ea === recipient }).length; });
for (var mc in matchingContacts) // update the lastTouch
{
matchingContacts[mc].contactTouch.lastTouch = date;
}
}
} else { // Received email
var matchingContacts = trackedContacts.filter(function (tc) { // find matching contacts where the lastTouch is older than this date AND there's an email match
return tc.contactTouch.lastTouch < date &&
0 < tc.allEmails.filter(function (ea) { return ea === sender }).length; });
for (var mc in matchingContacts) // update the lastTouch
{
matchingContacts[mc].contactTouch.lastTouch = date;
}
}
} catch (ex) {
Logger.log('Error ' + ex + ' ' + message.getSubject());
throw ex;
}
}
Logger.log('Finished scanning');
// For which tasks to remove
var allRecentlyEmailed = trackedContacts.filter(function (tc) {
return tc.cutoffDate < tc.contactTouch.lastTouch && // lastTouch is after the cutoff for this contact
tc.contactTouch.taskId != null; // and the taskId is not null
});
// For which tasks to add
var allNewlyExpired = trackedContacts.filter(function (tc) {
return tc.contactTouch.lastTouch <= tc.cutoffDate && // lastTouch is before the cutoff for this contact
(tc.contactTouch.taskId == null || // and there is no taskId
processedTasks.remaining.filter(function (r) { return r === tc.contactTouch.taskId }).length === 0); // or the task is missing
});
for (var re in allRecentlyEmailed)
{
// delete the task
var recentlyEmailed = allRecentlyEmailed[re];
var task = processedTasks.remaining.filter(function (r) { return r.getId() === recentlyEmailed.contactTouch.taskId; })[0];
if (task)
{
task.setDeleted(true);
Tasks.Tasks.update(task, tasklistId, recentlyEmailed.contactTouch.taskId);
}
recentlyEmailed.contactTouch.taskId = null;
}
Logger.log('Deleted ' + allRecentlyEmailed.length + ' tasks');
for (var ne in allNewlyExpired)
{
// add a task
var newlyExpired = allNewlyExpired[ne];
var title = 'Check in on ' + newlyExpired.contact.getFullName();
Logger.log('Adding task "' + title + '"');
var newTask = Tasks.newTask().setTitle(title);
newlyExpired.contactTouch.taskId = Tasks.Tasks.insert(newTask, tasklistId).getId();
}
Logger.log('Added ' + allNewlyExpired.length + ' tasks');
db.saveBatch(contactTouches, false);
};
// From and To lines in email are each presented as a raw string - we need to extract just the email address for matching to Contact email addresses.
// This takes a string of comma or semi-colon delimited email addresses (including optionally quotes full-names preceeding brackets addresses, or just naked email addresses)
// and returns an array of normalized addressed.
function extractRawEmailAddresses(addressLine)
{
var reEmail = /\s*(?:([^"<,;]+)|(?:(?:(?:(?:"(?:""|[^"<,;])*")|[^"<,;]*)?\s*<([^"<>,;]*)>)))(?:$|,|;)/gi;
var extracted = [];
var captures;
while (captures = reEmail.exec(addressLine))
{
extracted.push((captures[2] || captures[1]).trim().toLowerCase());
}
return extracted;
}
// Ensure that the TaskList exists and return the ID.
function getTouchMinderTaskList()
{
var title = "TouchMinders";
var allLists = Tasks.Tasklists.list().getItems();
var id;
for (var i in allLists) {
if (title == allLists[i].getTitle()) {
return allLists[i].getId();
}
}
var newTaskList = Tasks.newTaskList();
newTaskList.setTitle(title);
return Tasks.Tasklists.insert(newTaskList).getId();
}
// Look for manually Completed Tasks and interpret that to mean that the Contact was touched by the user through another channel (Phone, Chat, in-person)
function processCompletedTasksGetRemaining(tasklistId, contactTouches, myId)
{
var tasks = Tasks.Tasks.list(tasklistId).getItems();
var completed = 0;
var remaining = [];
for (var t in tasks) {
var task = tasks[t];
var taskId = task.getId();
var completion = task.getCompleted();
if (completion)
{
var found = contactTouches.filter(function (ct) { return ct.taskId == taskId })[0];
if (found) {
var lastTouch = new Date(completion.replace(/\-/g,'\/').replace(/[T|Z]/g,' ')).getTime();
found.lastTouch = found.lastTouch < lastTouch ? lastTouch : found.lastTouch;
found.taskId = null;
task.setDeleted(true);
Tasks.Tasks.update(task, tasklistId, taskId);
completed++;
}
else
{
remaining.push(taskId);
}
}
}
return { completed: completed, remaining: remaining };
}
// Get all contacts from ContactsApp Google Service and pair them with cached data found in contactTouches ScriptDB
function loadContacts(contactTouches, myId)
{
// My Contacts only - perhaps later this is configurable?
return ContactsApp.getContactGroup("System Group: My Contacts").getContacts()
.map(function (c) {
var touchFrequency = c.getCustomFields('Touch Frequency (Days)'); // Get the Contact's preferred Touch Frequency in Days as configured in the Google Contacts App custom field
if (!touchFrequency) { // if there is no such setting, create one and set it to a default of 30 days
touchFrequency = '30';
c.addCustomField('Touch Frequency (Days)', touchFrequency);
}
var contactId = c.getId();
var contactTouch = contactTouches.filter(function (ct) { return ct.contactId === contactId; })[0]; // Find the matching contactTouch
if (!contactTouch) // if it doesn't exist, add it to the array (it will be saved to the ScriptDB at the end of the entire script execution)
{
contactTouch = { table: 'ContactTouches', ownerContactId: myId, contactId: contactId, lastTouch: 0, taskId: null };
contactTouches.push(contactTouch);
}
var frequency = parseInt(touchFrequency, 10) || 30; // Attempt to parse the touchFrequency string as an int or default to 30 days
var cutoffDate = new Date(); cutoffDate.setDate(cutoffDate.getDate() - frequency); // Calculate the earliest date to search for touches with this contact
return {
frequency: frequency,
cutoffDate: cutoffDate.getTime(),
contact: c,
contactId: contactId,
primaryEmail: c.getPrimaryEmail,
allEmails: c.getEmails().map(function (ea) { return ea.getAddress().trim().toLowerCase(); }),
contactTouch: contactTouch
};
});
}
/*
// IterateMessages created with the help of the Tracuer project from this source:
function* IterateMessages(searchCriteria, backwardFromDate) {
var oldestYetObserved = (backwardFromDate || new Date()).getTime()/1000 | 0;
while(true)
{
// Older seems non-inclusive, which is desirable for this purpose, otherwise we'd loop infinitely on any single result
var batchSearchCriteria = searchCriteria + ' older:' + oldestYetObserved.toString();
var threads = GmailApp.search(batchSearchCriteria, 0, 10);
if (threads.length === 0) break;
for (var i = 0; i < threads.length; i++) {
var messages = threads[i].getMessages();
for (var j = 0; j < messages.length; j++) {
yield messages[j];
}
}
oldestYetObserved = threads[threads.length - 1].getLastMessageDate().getTime()/1000 | 0;
}
};
for (var message in IterateMessages('in:anywhere'))
{
console.log(message);
}
*/
// Lazily iterates through email messages.
// Optionally pass any search criteria other than 'older:' for searchCriteria ex: 'in:inbox from:([email protected]) subject:(Lunch?) has:attachment'.
// Optionally pass a Date from which to iterate backwards in time (by each Thread's most recent email date)
// Refer to the commented ECMAScript Harmony version above for basic details
function IterateMessages(searchCriteria, backwardFromDate) {
var $that = this;
var $arguments = arguments;
var $state = 20;
var $storedException;
var $finallyFallThrough;
var batchSearchCriteria;
var i;
var j;
var messages;
var threadMessages;
var oldestYetObserved;
var threads;
var $result = {moveNext: function($yieldSent) {
while (true) try {
switch ($state) {
case 20:
oldestYetObserved = (backwardFromDate || new Date()).getTime() / 1000 | 0;
$state = 21;
break;
case 21:
if (true) {
$state = 10;
break;
} else {
$state = 19;
break;
}
case 10:
batchSearchCriteria = (searchCriteria || '') + ' older:' + oldestYetObserved.toString();
$state = 11;
break;
case 11:
threads = GmailApp.search(batchSearchCriteria, 0, 50);
messages = GmailApp.getMessagesForThreads(threads);
$state = 13;
break;
case 13:
if (messages.length === 0) {
$state = 14;
break;
} else {
$state = 15;
break;
}
case 14:
$state = 19;
break;
case 15:
i = 0;
$state = 7;
break;
case 7:
if (i < messages.length) {
$state = 5;
break;
} else {
$state = 9;
break;
}
case 4:
i++;
$state = 7;
break;
case 5:
threadMessages = messages[i];
$state = 6;
break;
case 6:
j = 0;
$state = 2;
break;
case 2:
if (j < threadMessages.length) {
$state = 0;
break;
} else {
$state = 4;
break;
}
case 1:
j++;
$state = 2;
break;
case 0:
$result.current = { thread: threads[i], message: threadMessages[j] };
$state = 1;
return true;
case 9:
oldestYetObserved = threads[threads.length - 1].getLastMessageDate().getTime() / 1000 | 0;
$state = 21;
break;
case 19:
$state = 23;
case 23:
return false;
case 22:
throw $storedException;
default:
throw "invalid state in state machine " + $state;
}
} catch ($caughtException) {
$storedException = $caughtException;
switch ($state) {
default:
throw $storedException;
}
}
}};
return $result;
}
// Quickly reset the taskId for all ContactTouches
function reset() {
var currentUserEmailAddress = Session.getActiveUser().getEmail();
var myId = ContactsApp.getContact(currentUserEmailAddress).getId();
var contactTouches = [];
var db = ScriptDb.getMyDb();
var queryResults = db.query({ table: 'ContactTouches', ownerContactId: myId });
while(queryResults.hasNext()) { var next = queryResults.next(); next.taskId = null; contactTouches.push(next); }
db.saveBatch(contactTouches, false);
}
// Slowly delete the ScriptDB items one at a time (might affect all domain users!)
function deleteAll() {
var db = ScriptDb.getMyDb();
while (true) {
var result = db.query({});
if (result.getSize() == 0) {
break;
}
while (result.hasNext()) {
db.remove(result.next());
}
}
}
