狠狠撸

do something useful with
Apps Script in 5 minutes
7. Log who you sent emails to in a sheet
Bruce McPherson
www.mcpher.com

Snippet objectives
● Use the lessons learned in ‘using a spreadsheet as a
database’
● Search email for a particular topic and write recipient
details to a sheet
● Learn about exponential backoff and Array functions
Libraries used
● database abstraction
● driver sheet
● useful snippets

Add libraries to script
● create a spreadsheet
● get its id
● create a script
● Open resources
● Add references to libraries
MHfCjPQlweartW45xYs6hFai_d-phDA33
Mrckbr9_w7PCphJtOzhzA_Cz3TLx7pV4j
Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j

layout what you are going to do
function myFunction() {
// get all email threads matching search query
// extract the messages and recipients for each thread
// open a sheet
// clear sheet
// write results
}

get all matching email threads
For this example, we’ll keep it simple - if you expected many results you’d need to handle continuation as by default this
only returns a chunk of the matching queries.
What you’ll get back is an array of GMailThread
// get all email threads matching search query
var threads = GmailApp.search("The Excel Liberation forum has moved to a Google+ community");

set up exponential backoff
Getting messages within threads is rate limited in Google Apps Script. If we ask for too many too quickly, it will fail.
Exponential backoff is a technique that recovers from errors like this and tries again. You’ll only be able to process
about 300 messages within the 6 minute GAS execution quota
var emails = threads.reduce ( function (p,c) {
// get messages within each thread and append each recipient
cUseful.rateLimitExpBackoff(function () {
});
return p;
},[]);

get the message threads
Each thread can contain multiple messages, go through them using Array.forEach() to extract info from each method
c.getMessages().forEach(function(d) {
});
});
return p;
},[]);

Map the recipients
Each message can contain multiple recipients in the To: property. These are comma separated so we can use String.
split(“,”) to create an array, then Array.Map() to transform each item and extract the summary information we need into
a new object. Finally these are appended to the growing array being built up by .reduce()
cUseful.arrayAppend(p, d.getTo().split(",").map(function(e) {
return {to:e,subject:d.getSubject(),dateSent:d.getDate().toString(),from:d.getFrom()};
}));
});
});
return p;
},[]);

Open sheet
Using database abstraction, open a sheet as a database.
// open a new sheet
var handler = new cDbAbstraction.DbAbstraction(cDriverSheet, {
"siloid": "emails",
"dbid": "1yTQFdN_O2nFb9obm7AHCTmPKpf5cwAd78uNQJiCcjPk"
});
and remove any data already there
// remove anything already there
var result = handler.remove();
if (handler.handleCode < 0) throw JSON.stringify(result);

Log the results
Using Array.sort() , sort by recipient email address then write the results to a sheet
// sort and log results
var result = handler.save(emails.sort(function (a,b) {
return a.to > b.to ? 1 : (a.to < b.to ? -1 : 0) ;
}));

The whole thing
function myFunction() {
// get all matching emails
var threads = GmailApp.search(
"The Excel Liberation forum has moved to a Google+ community");
cUseful.arrayAppend(p, d.getTo().split(",").map(function(e) {
return {
to:e,
subject:d.getSubject(),
dateSent:d.getDate().toString(),
from:d.getFrom() };
}));
});
});
return p;
},[]);
// open a new sheet
var handler = new cDbAbstraction.DbAbstraction (
cDriverSheet, {
"siloid": "emails",
"dbid": "1yTQFdN_O2nFb9obm7AHCTmPKpf5cwAd78uNQJiCcjPk"
});
if (!handler.isHappy()) throw 'could not open sheet';
// remove anything already there
var result = handler.remove();
// sort and log results
var result = handler.save(emails.sort(function (a,b) {
return a.to > b.to ? 1 : (a.to < b.to ? -1 : 0) ;
}));
}

Homework
Apps Script quota prevents you dealing with
more than about 300 threads within the
execution time quota.
Figure out how to deal with more than that
using search(query, start, max) and running the
task several times

Follow up materials
Take a copy of this script
Take a copy of these slides
Join me on G+, or the G+ community
More on desktop liberation
More on database abstraction
More 5 minute things

狠狠撸

Do something in 5 with gas 7-email log

More Related Content

What's hot (20)

Similar to Do something in 5 with gas 7-email log (11)

Recently uploaded (20)

Do something in 5 with gas 7-email log