Gmail, Spreadsheets and Google Apps Script
Written by Ian Elliot   
Friday, 25 January 2013
Article Index
Gmail, Spreadsheets and Google Apps Script
Transferring date to a spreadsheet

If you have to administer an email list, creating a Google Apps Script to process email bounces and send the relevant data to a spreadsheet is not only useful but also a good example of using scripts.

 

Google Apps Script is basically a server-side implementation of JavaScript, complete with a set of classes that lets you access various Google services such as Gmail. If you use any email services then you will probably have encountered a problem that really needs automating.

For example, you need to collate the email addresses of people who have responded to an email you sent out to a bigger email list. Or you just want to deal with the bounced email from a mailshot. The obvious solution is to write a Google Apps Script that processes your email and this is exactly what this project is all about.

The script reads a particular "folder" in Gmail, processes all of the emails it contains, stores some information extracted from the emails in a spreadsheet and then deletes the email.

In the particular case of this example, the script processes bounced emails.

You can see that even if you you are not interested in the exact workings of this particular script, it still serves as a basis for similar operations.

Getting started

If you are a complete beginner with Google Apps Script then see Getting Started With Google Apps Script for a general discussion. In this article it is assumed that you have a Google account, including email and Drive.

The simplest way to create a new script is to go to the Google Drive page and click on Create, more then select Script:

 

create

 

This leads on to a dialog box which allows you to choose the type of script you want to create. In the main these choices simply add a few useful JavaScript functions relating to the service you are about to work with.

 

scriptype

You might think that as we want to work with GMail we should choose a GMail script, but in this case it is simpler to start with a Blank Project and build up only the code we actually need.

So select Blank Project. All you get in this case as a starter is:

 

function myFunction() { 
}

 

Before you can run your project you have to save it and this is also when you give it a name - gmailextract - say.

Once you have saved it you can run it. Make sure that myFunction is selected in the drop down list and click the run icon or the debug icon:

 

runicon

Now we are ready to start work on the application.

Getting the Emails

Our first job it to get the emails we want to process. For simplicity it is assumed that the emails are all in a single folder. In other words a Gmail filter has been set up which moves the emails of interest into a folder. Notice that in Gmail folders are an illusion. You can apply labels to emails and these are used to group the emails into what look like folders.

So to get all the emails in a particular "folder" we need to retrieve all the emails with a particular label.  To do this we use the getUserLabelByName method of the GmailApp object. Notice you don't have to create the GmailApp object it is automatically created for you.

function getemails() {
var label = GmailApp.getUserLabelByName(
                                "Bounce");

This creates a label object corresponding to the label you specify - "Bounce" in this case.

Once you have the label object you can retrieve all of the emails associated with it. Here we hit a slightly complication. In Gmail emails are organized into threads which represent a conversation - email received, email replied to and so on.

What this means is that we have to retrieve first a thread and then the emails within it. In this case it is very likely that each thread will consist of only one email but in general a thread could be any number of emails.

Our first job it to retrieve all of the threads associated with the label as a threads collection object:

var threads = label.getThreads();

Now we can setp though each of the threads using:

for (var i = 0; i < threads.length; i++) {
   threads[i] ...

threads[i] is the ith thread object and it has a getMessages method which returns a messages collection object consisting of all of the messages in the thread.

So now we can step though all of the messages belonging to threads[i]:

var messages=threads[i].getMessages();
  for (var j = 0; j < messages.length; j++) {
        messages[j];...

where messages[j] is the jth message associated with the thread i.

Putting all of this together gives:

for (var i = 0; i < threads.length; i++) { 
 var messages=threads[i].getMessages();
 
 for (var j = 0; j < messages.length; j++) {
   var message=messages[j];
    .. do something with the message 
 }
}

At this point we have a message object corresponding to the message and this has a range of properties and methods that allow us to gain access to its data.

 



Last Updated ( Wednesday, 25 October 2017 )