Google APIs Timeline: Behind the Scenes

As part of my recent series of talks on the landscape of the Google APIs, I started off with a timeline showing the history of our APIs, from just 10 APIs in 2005 to over 80 now, with many launches and a few deprecations along the way. That timeline, appropriately, is itself a quick mashup of our APIs, and I thought I'd spend a few minutes talking about how I made it here.

First, I needed the data on our APIs over time. Unfortunately, we don't offer an API for querying our API existence over time (as awesomely meta as that would be), so I had to go the painstakingly manual route. I used the Internet Wayback Machine, a website that lets you view cached versions of other websites at various times. We launched code.google.com in March 2005 (and by we, I mean Chris Dibona), and the machine cached many versions since that first version. By looking at a combination of the listings on the apis.html page and the launch announcements on the front page, I could figure out roughly which APIs we introduced when.

Next, I needed a place to store that data. Well, as some of you know, I'm a massive fan of using Google Spreadsheets as a lightweight database, so I created a worksheet with a few columns (date, APIs added) and filled that in as I browsed the code.google.com of yore (and got a bit nostalgic along the way).

Finally, I wanted to visualize the data in a cool way. I had used the Annotated Timeline from Google Chart Tools for the Wave Visualizer mashup last month, and I figured this was another good use of it. It's the same timeline that's used by Google Finance to show stock trends compared to news stories, and similar to the timeline used by Google Trends. Though the timeline itself is a Flash SWF, it is exposed via a Javascript API.

Now, to put it together, I just needed to pull in the spreadsheets data and feed that into the timeline.

I brought in the spreadsheets data using the JSONP technique, dynamically appending a script tag with the src attribute set to the JSON output of the spreadsheets API, and specifying a callback function to be passed the JSON data. Note that I used the "values" projection for the feed, as that treats the worksheet as a database and returns the named columns as values in the JSON.

function appendSpreadsheet() {
  var script = document.createElement('script');
  script.src = 'http://spreadsheets.google.com/feeds/list/0Ah0xU81penP1dE1TNnpscHdYYU5qSU5GZldLM1VMMVE/od6/public/values?alt=json-in-script&callback=onSpreadsheetLoad';
  document.body.appendChild(script);
}

In the callback function, I parse through the rows of the spreadsheets feed and add them as rows to a DataTable object. I then create the AnnotatedTimeline object and ask it to draw that data.

 function onSpreadsheetLoad(json) {
  var rows = json.feed.entry || [];
  var data = new google.visualization.DataTable();
  data.addColumn('date', 'Date');
  data.addColumn('number', 'Total APIs');
  data.addColumn('string', 'Changes');
  data.addRows(rows.length);
        
  for (var i = 0; i < rows.length; i++) {
    var row = rows[i];
    var year = parseInt(row['gsx$year']['$t']);
    var month = parseInt(row['gsx$month']['$t']);
    var day = parseInt(row['gsx$day']['$t']);
    var total = parseFloat(row['gsx$apitotal']['$t']);
    var info = row['gsx$info']['$t'];
    data.setValue(i, 0, new Date(year, month, day));
    data.setValue(i, 1, total);
    data.setValue(i, 2, info);
  }
        
  var annotatedtimeline = new google.visualization.AnnotatedTimeLine(
     document.getElementById('visualization'));
  annotatedtimeline.draw(data, {'displayAnnotations': true});
}

And with that (and some fiddling with the timeline options), I had an interactive timeline:

It's a pretty simple mashup that only took a few hours to put together (99% of which was data collection), and an example of how you can easily combine a couple of our APIs in interesting ways. Happy mashing! :)