jQuery, Google Visualisation API, and JSON data

October 14, 2011    Google Charts API jQuery JSON

Recently I was working on a project which allows for some data analysis using the Google Visualisation API. The API is great, but there is (at least) one pitfall when using it together with jQuery.

Imagine the following set-up: an SQLite database, a PHP page spitting out JSON understandable by the Google Visualisation DataTable. The PHP page reads data from the SQLite database and transforms it to JSON. The JSON data is then fed to the Google Visualisation API to build a graph. Sounds simple, right?

According to the Google Visualisation API documentation, datetime-values are supported. I created the following piece of code in JavaScript which uses jQuery to get the data and build the DataTable in the users' browser:

function loadData() {
        $.ajax("data/get_data.php", {
                success: dataDownloaded,
                error: function() {
                        alert("Error loading data");
function dataDownloaded(jsonData) {
        var table = new google.visualization.DataTable(jsonData);

When the data has been downloaded, a DataTable is created. The columns of the DataTable, together with some example data is as follows:

    "cols": [
        {"id":"id",         "label":"ID",      "pattern":"", "type":"string"},
        {"id":"begin",      "label":"Begin",   "pattern":"", "type":"datetime"},
        {"id":"end",        "label":"Eind",    "pattern":"", "type":"datetime"},
        {"id":"trajectory", "label":"Traject", "pattern":"", "type":"string"},
        {"id":"type",       "label":"Type",    "pattern":"", "type":"string"}
    "rows": [
	    {"v": "1234"},
	    {"v": "Date(2011, 01, 01, 00, 00, 00)"},
	    {"v": "Date(2011, 01, 04, 00, 00, 00)"},
	    {"v": "text1"},
	    {"v": "text2"}

Looks ok, but it turns out the datetime-values are not parsed correctly. Chrome reports the following error, and when querying the table a string is returned (while a Date object is expected):

    Uncaught TypeError: Object Date(2011, 04, 05, 10, 51, 00) has no method 'getTimezoneOffset'
    > table.getValue(0, 1)
      "Date(2011, 01, 01, 00, 00, 00)"

Why is this happening? After some quality-debugging-time it turns out that jQuery already parses the JSON (see http://api.jquery.com/jQuery.ajax/, parameter dataType). The datetime-values are kept as strings. If the DataTable itself parses the JSON data (given as a string) it has the intelligence that it parses the Date(..) values as a Date object (http://code.google.com/apis/chart/interactive/docs/dev/implementing_data_source.html#jsondatatable).

The reason that jQuery parses the JSON string is because it finds the header “Content-type: application/json” returned by the PHP page. Then it knows the data is JSON and tries to help the user by parsing it. The fix is to set the parameter dataType: “text” in the jQuery.ajax()-call. Now jQuery no longer parses the JSON string, but the DataTable is given the data as a string and parses the Date(..) values as expected.