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