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"
}
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 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 like this see Google Apps Scripts Snippets
For help and more information join our forum, follow the blog or follow me on Twitter