Processing the Email

In this particular case what should happen to the email was determined by the content of the subject line. To retrieve the subject line we simply use the getSubject method:

var subject=message.getSubject();

Now as the script is processing bounced emails what we are looking for are the standard headers that mail servers use to send back emails. The most common is:

Mail delivery failed: returning message to sender

and so we need an if statement to detect this and one for each of the variations. To keep things simple this is the only option this example will process.

if(subject=="Mail delivery failed:
                returning message to sender")
                             Process1(message);
The function Process1 is going to deal with this particular format of email.

The Spreadsheet

What is going to happen in Process1 is that the email address in the email is  extracted and then stored in the next free location in a Google spreadsheet.

The first task is to retrieve the body text of the email and this is just a matter of using the getBody method:

function Process1(message){
    var body=message.getBody();

From the body we are going to extract a fully formed email address. Although there will be a number of emails in the text of the message generally only the bounced email address will be correctly formed. The simplest way to do this job is to use a regular expression. If you don't know about regular expressions then see Master JavaScript Regular Expressions.

In this case we don't need to construct a regular expression for a well formed email address because there are a number you can search on the web and just use:

var regex= /(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+
 (?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"
 (?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b
    \x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e
  \x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*
 [a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*
 [a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|
 [01]?[0-9][0-9]?)\.){3}(?:25[0-5]|
 2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*
 [a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-
   \x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-
    \x7f])+)\])/;

In most cases, this one included, it is harder to understand someone else's regular expression than one you have constructed by yourself. This expression is so big and complicated it is better to just accept that it works!

Once we have the regular expression we can use it to extract the email address:

var address=regex.exec(body);

Now we have the address it is time to store it in the spreadsheet. For simplicity it is assumed that you have created an empty spreadsheet in the same Google Drive account. The only complication is that Apps Script doesn't work with file names but ids. You can write a function which searches the folder for a file with a given name and then extract its id number but in this case it is simpler to open the spreadsheet in a browser and look at the URL. You can find the id in the URL as the string that follows key= in the URL. This id string contains numbers, letters and symbols.

Simplest thing to do is copy and paste the id into the script:

var id= long id string pasted from the URL;

Now that you have the id you can open the spreadsheet.

var ss = SpreadsheetApp.openById(id);

and get the active sheet which will be the first sheet if the spreadsheet is empty:

var sheet = ss.getActiveSheet();

The problem of how to insert a new item of data into the sheet has many solutions. You can for example move the cursor to the top of the column you want to add to and move down until you reach an empty cell. A much easier solution, and it is so easy it almost qualifies as a cheat, is to use the appendRow sheet method. This will insert as many data items as you supply as parameters to the first free row in the sheet. Although it seems to be about appending a complete row, as long as you are happy with inserting data into the first few columns it works well enough.

A subtle point is that appendRow is an atomic operation and if multiple scripts are working on the same spreadsheet they will not insert data into the same row by accident. Consider the alternative of using getLastRow followed by a write to that row. Two scripts could easily get the same last row and start writing data into it.

To append the email address all we have to do is:

sheet.appendRow(address);

Finally we delete the email:

 deleteMessage(message);                  
}

The deleteMessage function is very simple:

function deleteMessage(message){  
  message.moveToTrash();
}

Notice that you don't delete an email but move it to the trash folder where it can either be retrieved or permanently deleted by the user or after the standard time has passed.

Using the Script

You can now run the script and view both the folder in Gmail and the spreadsheet. You should see emails disappear from the Gmail folder and lines appear in the spreadsheet. It doesn't work particularly quickly, about one or two emails per second and it work in bursts so if you don't see anything happen be patient.

If you decide to use it "for real" then you could go to your Google Drive account open the script and run it manually when you wanted to process the emails. Alternatively you could set up a timed trigger to run the script at regular intervals. To so this either click on the clock icon or use the Resources, Current script's triggers menu option. You can add a timed trigger for a range of time intervals and for a specific date and time.

The Complete Listing 

function getemails() { 
 var label = GmailApp.getUserLabelByName(
                                    "Bounce");
 var threads = label.getThreads();
     
 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];
   var subject=message.getSubject();
   if(subject=="Mail delivery failed:
           returning message to sender")
                            Process1(message);
  // additional if statements to process

  // other cases
  }
 }
}


function Process1(message){
 var body=message.getBody();
 var regex= /(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+
 (?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"
 (?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b
    \x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e
  \x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*
 [a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*
 [a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|
 [01]?[0-9][0-9]?)\.){3}(?:25[0-5]|
 2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*
 [a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-
   \x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-
    \x7f])+)\])/;
 var address=regex.exec(body);
 var id= long id string pasted from the URL;
 var ss = SpreadsheetApp.openById(id);
 var sheet = ss.getActiveSheet();
 sheet.appendRow(address);
 deleteMessage(message);          
} 

function deleteMessage(message){
 message.moveToTrash();
}

If you would like the source code of this project register with I Programmer and visit the CodeBin.

Related Articles:

Dealing With Forum & Mailing List Spam

Getting Started With Google Apps Script

Master JavaScript Regular Expressions

To be informed about new articles on I Programmer, subscribe to the RSS feed, follow us on Google+, Twitter, Linkedin or Facebook or sign up for our weekly newsletter.