Jul 14, 2010

Accessing Google's RESTful APIs from Google Apps Script

Let’s talk about how we can access Google’s RESTful APIs from Google Apps Script. As a concrete example, we’ll access our Google Analytics data and bring it into a spreadsheet.

The first step is to authenticate using ClientLogin. So open up a new spreadsheet, and open up the script editor (Tools > Script > Script Editor). We need to authenticate with our Google username and password and get a token. This token will be used later to request data.

The script to authenticate looks like this:

function getAuthToken() {
  var advancedArgs = {
    method: "post",
    payload: "AccountType=HOSTED_OR_GOOGLE" +
      "&Email=<email>" +
      "&Passwd=<password>" +
      "&service=analytics" +
      "&source=vh_googapp_script",
  };
  var response = UrlFetchApp.fetch(
    "https://www.google.com/accounts/ClientLogin", advancedArgs);
  var authToken = response.getContentText().split("\n")[2];
  return authToken;
}

Remember to substitute your own username and password in the above script. Also, don’t share the spreadsheet because it has your username and password :-#.

Now you can use that like a function in a spreadsheet cell, by typing “=getAuthToken()” in a cell.

Say we want to get a list of the sites for which we’re collecting analytics data, your “feed list.” The script for that is:

function getFeedList(token) {
  var advancedArgs = {
    headers: {"Authorization": "GoogleLogin" + token}};
  var response = UrlFetchApp.fetch(
    "https://www.google.com/analytics/feeds/accounts/default",
    advancedArgs);
  return response.getContentText();
}

The “token” parameter is the auth token returned by getAuthToken(). This will give you back an XML description of your analytics feeds.

Now say you pick a single feed for which you want to get hits and bounces for a specific date. The script to get that data looks like:

function getVisitsForDay(token, tableId,day) {
  var advancedArgs = {
    headers: {"Authorization": "GoogleLogin" + token}};
  var response = UrlFetchApp.fetch(
    "https://www.google.com/analytics/feeds/data?ids=" +
    tableId + "&metrics=ga:visits&start-date=" + day +
    "&end-date=" + day,
    advancedArgs);
  var parsedXml = Xml.parse(response.getContentText());
  var visits = parsedXml.getElement().getElement("entry")
      .getElement("http://schemas.google.com/analytics/2009", "metric")
      .getAttribute("value").getValue();
  return visits;
}
function getBouncesForDay(token, tableId, day) {
var advancedArgs = {
headers: {“Authorization”: “GoogleLogin” + token}};
var response = UrlFetchApp.fetch(
“https://www.google.com/analytics/feeds/data?ids=" +
tableId + “&metrics=ga:bounces&start-date=” + day +
“&end-date=” + day, advancedArgs);
var parsedXml = Xml.parse(response.getContentText());
var visits = parsedXml.getElement().getElement(“entry”)
.getElement(“http://schemas.google.com/analytics/2009", “metric”)
.getAttribute(“value”).getValue();
return visits;
}

“tableId” is of the form “ga:nnnnnnn” and will be listed in the feeds list. And the “day” parameter is the date for which you want the data, in the format “YYYY-MM-DD”. As with all script functions, you can now use these in your spreadsheet cells. For example, you could put into a cell the formula “=getVisitsForDay(A2, B5, "2010-07-11”)“ to get the number of visits to your site on July 11 2010, assuming cell A2 contained your auth token, and cell B5 contained your table or profile ID.

As you can probably see by now, these functions have a common structure: put the auth token in the HTTP request header, construct the HTTP URL for the request, and parse the returned XML to get at the exact piece of data you want. Hope this sets you on your way.