Sorting emails and spreadsheets with Google Apps Script

I get far too may emails that I can deal with on a daily basis so I use Gmail filters to help me keep track of what is where. However sometimes you want to extract information from these emails without having to read every single one - for instance if you wanted to keep a database of all the emails you received from a mailing list and their content. Google spreadsheets provides several nice tools to deal with this - most notably their Google Apps Script through which you can access much of the Google toolkit's functionality (i.e. sending emails, generating charts etc). Their scripting language is based on Javascript - so even if you're not familiar with it it's not too hard to pick up with some specific examples.

So diving right in - open a new google spreadsheet and from the file menu at the top select Tools -> Script Editor which will open a new window for you to enter your scripts in. The first thing to note is that this script is currently locked to the spreadsheet you opened it from so if you run the commands:


the script will load the spreadsheet you started from and then set the variable 'lastRow' to the row number of the last row with information in it.

If you want to know what values that variables has you can print it to the console log using the command:
(If you're coming to this from Python this is akin to the print command).
Finally, the commands you want to read from or write to a spreadsheet cell are:

To check everything is working, enter some numbers into the first column of your spreadsheet, then copy and paste the following script into your script editor and then select 'Run' from the file menu. You will have to give the app permission to run - and to see the log output choose Logs from the View menu (The execution transcript from the same menu can also be useful for debugging).

All our script is doing is reading through the spreadsheet, printing out the value it finds and then replacing it with the square of whatever it finds. Simple!....unless you accidentally put text in in which case the script will throw an error. I'll leave you to sort out error handling on your own with the Google Apps Script documentation.

So how do we use this to keep a log of information from emails? Like this:

Just remember - for this to work the emails must have the label 'data_emails' (unless you change the variable in the code) and must be marked as Unread.
Now you have that data in spreadsheet format - you can process/sort it with other scripts, send emails that contain it, or automatically graph it each day. I'll cover some of these in future tutorials

Scraping websites with Google spreadsheets

Frequently when working with data collection or visualizations it's useful to get data from an external website that doesn't have an API. There are plenty of tools to help you with this but one of the easiest is certainly google spreadsheets, or you could of course write your own web scraping bot with Python and the Beautiful Soup library (which I will outline in a future tutorial). One thing to note - web scraping is not always legal so make sure to check the website's data use policy before you go scraping it and sharing it with everyone!

To get started go grab a google account and/or login to google documents and create a new google spreadsheet. then go find the URL of the data you want to scrape. To keep things simple I'm going to demonstrate with the highest-grossing films data from Wikipedia. Here's the URL:
 
http://en.wikipedia.org/wiki/List_of_highest-grossing_films#Highest-grossing_films

If you're not familiar with URLs, the # character directs your requests to a specific subset of the page - in this case to the 'highest grossing films' section. If you go to the site you will see a table which is the data we're interested in:



In the top left hand cell of your spreadsheet, use the following command:
 =ImportHtml("http://en.wikipedia.org/wiki/List_of_highest-grossing_films#Highest-grossing_films","Table", 1)

Hit enter and the spreadsheet will slowly populate itself with all the data from the table we're looking at and you can graph it or save it as a .csv or text file for later analysis with another program or script.

If it didn't work there's a few things to check:

1. Make sure you have enough of the right kind of quotation marks. There should be double quotes around both the URL and the word Table in the above command
2. Check the URL is correct.

So what precisely is happening? The =ImportHtml() command directs google spreadsheets to the particular page and the next command "Table" tells it too look for an element in the format of a table. You can also use alternative Html elements here like lists ("List"). Finally the value 1 tells it which element to look at. Start with 0 and keep increasing the number by 1 until you find the data you're looking for. 

Have a go changing the number in the above command and see if you can scrape the table from further down the page with films adjusted for inflation.


There's a functional example of the spreadsheet here if you're interested.

It's also worth noting that Google spreadsheet come with several other import tools for importing different types of data including .xml with:

• ImportHtml()
• ImportXML()
• ImportData()
• ImportFeed()

Finally you can also use the other google spreadsheet commands to sort through the data you get. One of the most useful is the =SPLIT() command that separates the incoming data by a certain character (so the command =SPLIT(A1, " ") would split whatever text is in the cell A1 wherever there was a white space.)  Another useful command is =REGEXEXTRACT(), which searches a sting for a particular pattern of letters and numbers known as a regular expression, and then returns it. Regex commands are a little complicated to get used to but really useful - here's one that searches for the pattern of a year(i.e. 2014) in a string:  
=REGEXEXTRACT(A1,"[0-9]{4}"
)