Libraries
Regular visitors to this site will know that I’m a great fan of reusing code via libraries – no point in wasting time repeating things. We’re going to need these libraries.
info: { name: "cDbAbstraction", version: "2.0.3", key: "MHfCjPQlweartW45xYs6hFai_d-phDA33" } info: { name: "cUseful", version: "2.02", key: "Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j" } info: { name: "cDriverSheet", version: "2.01", key: "Mrckbr9_w7PCphJtOzhzA_Cz3TLx7pV4j" }Access my library to get all keys and dependencies from the majority of my public libraries
Rate limiting
The GMailApp is very strict about quotas, so I’m using exponential backoff throughout. It’s a fine balance between waiting too long between requests (waiting counts towards the 6 minute quota) and blasting too many requests for email messages.
The code
Once you have the libraries installed, there’s not much to it.
First we get all the email threads matching a query that will identify the message I want to pull out
// get all matching emails var threads = GmailApp.search("The Excel Liberation forum has moved to a Google+ community"); Next we have to extract the individual messages - I'll do that inside a reduce operation whose framework is like this - the objective is to get an array with one item for each item/mail recipient combination. var emails = threads.reduce ( function (p,c) { // get messages within each thread },[]);
Get the messages associated with each thread. This is the troublesome one that could blow your rate limits, so I wrap it in a backoff function.
var emails = threads.reduce ( function (p,c) { // get messages within each thread var messages = cUseful.rateLimitExpBackoff(function () { return c.getMessages(); },800); },[]);
Each message could have multiple recipients. Create an object for each one, and add to the list
var emails = threads.reduce ( function (p,c) { // get messages within each thread var messages = cUseful.rateLimitExpBackoff(function () { return c.getMessages(); },800); // add each message messages.forEach (function (d) { cUseful.arrayAppend(p,d.getTo().split(",").map(function(e){ return {to:e,subject:d.getSubject(),dateSent:d.getDate().toString(),from:d.getFrom()}; })); }); return p; },[]);
Using Database abstraction with google apps script, write the results to a database – in this case it’s a spreadsheet.
// open a spreadsheet as a database 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(); if (handler.handleCode < 0) throw JSON.stringify(result); // 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) ; })); if (handler.handleCode < 0) throw JSON.stringify(result);
Here’s the whole thing
function myFunction() { // get all matching emails var threads = GmailApp.search("The Excel Liberation forum has moved to a Google+ community"); var emails = threads.reduce ( function (p,c) { // get messages within each thread var messages = cUseful.rateLimitExpBackoff(function () { return c.getMessages(); },800); // add each message messages.forEach (function (d) { 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 spreadsheet as a database 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(); if (handler.handleCode < 0) throw JSON.stringify(result); // 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) ; })); if (handler.handleCode < 0) throw JSON.stringify(result); }