Wednesday, January 11, 2012

Toying with Google Apps Script

Google offers an access to its services with Apps Scripts (JavaScript). That gives you a possibility to connect your spreadsheet to a fascinating variety of tools like geocoder, stock info, language translator, or email.

My java-scripting abilities are rather limited but just playing with tutorial examples I was quickly able to produce a script analyzing time distribution of received emails. It looks through your Gmail for the given contact and record the times of emails sent by it.

So this is email behaviour of my wife. You can see a peak at the noon when babies are having a nap and another local maximum at the end of the day when they are finally sleeping in beds.


The second example is my friend, very, very bad email responder (chance for a reply is ~50%). As you can read from the graph most essential emails are answered when the day start, then a few after a lunch... and finally evening responses (usually short and strict).



To try the script yourselves, open a new spreadsheet in GoogleDocs, select Tools -> Script Editor and copy the code below.

Run it, return to the spreadsheet and enter an email address of the contact of interest. The process takes a minute of two, be patient. You may want to add a command MailApp.sendEmail("your.name@yourmail.com", "Finished", "Google Apps Script"); before the last "}"  to be warned by mail when the job is finished.

After this, the first column is filled with time stamps information and we need a way to visualize it. Google App Script have a charting module but I found more convenient to use R/ggplot2 plotting services. A few lines of code are given below (before that the Google spreadsheet was published to web and the link was copied into, alternatively you could use RGoogleDocs).

Gmail blog: Gmail Snooze with Apps Script

2 comments:

  1. I am trying to run your script however i encounter the following error when execute the first line.

    datat <- read.csv("https://docs.google.com/spreadsheet/ccc?key=0AtV5LpX5WI3_dGQzeDZvdEJ2UGxPLVBoa0N2ajBSc3c", header = FALSE, skip = 1, as.is = TRUE)
    Error in file(file, "rt") : cannot open the connection
    In addition: Warning message:
    In file(file, "rt") : unsupported URL scheme

    ReplyDelete
    Replies
    1. You need to publish the document (File -> Publish to the web -> Publish).

      Next, I believe your first line should be the following

      data <- read.csv("http://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0AtV5LpX5WI3_dGQzeDZvdEJ2UGxPLVBoa0N2ajBSc3c&output=csv", header=FALSE, skip=1, as.is=TRUE)

      If still not working, just download the csv file to your disk (File -> Download as -> CSV) and work offline.

      Greetings to Amsterdam,

      Petr

      Delete