In Inviting members to Google Plus from Groups I showed how to use htmlservice to send a templated email to multiple recipients. Let's say you want to create a spreadsheet of people you've sent a particular email to. Here's how.LibrariesRegular 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: {
info: {
info: {
Rate limitingThe 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 codeOnce 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 },[]);
Each message could have multiple recipients. Create an object for each one, and add to the list
// 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); Here's the 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); } For more snippets like this see Google Apps Scripts snippets
For help and more information join our forum, follow the blog, follow me on twitter
|
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Google Apps Scripts snippets >