Summarizing emails to a sheet

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. 

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"
}

 https://script.google.com/macros/s/AKfycbwZ2Hht93wTNzvRmYINYF7obaOHciBXWcP_wAiEtyGq70_x3cI/exec 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.

For help and more information join our forum, follow the blog, follow me on twitter

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 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
You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.


Comments