|Gmail, Spreadsheets and Google Apps Script|
|Written by Ian Elliot|
|Friday, 25 January 2013|
Page 2 of 2
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:
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.
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:
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:
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:
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:
Now that you have the id you can open the spreadsheet.
and get the active sheet which will be the first sheet if the spreadsheet is empty:
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:
Finally we delete the email:
The deleteMessage function is very simple:
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
If you would like the source code of this project register with I Programmer and visit the CodeBin.
Dealing With Forum & Mailing List Spam
Getting Started With Google Apps Script
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.
|Last Updated ( Wednesday, 25 October 2017 )|