Google Spreadsheet JSON API: SQL Filtering

Using Google Spreadsheet as a JSON backend is not a new story, but do you know what's sexier? Filtering data from that spreadsheet with simple SQL syntax!

This technique is based on Query Language of Google Charts API. In this tutorial I will be guiding through the whole process staring from spreadsheet preparation ending with ready-to-use data.

1. Preparing a Spreadsheet

By default spreadsheets on Google Drive are private. Go to File -> Publish to the web, publish spreadsheet, grab the URL and extract the key from it.

You also have to share your spreadsheet to everyone, but only with read permission. Go to File -> Share and select Anyone with the link in link sharing options. The idea is to make spreadsheet accessible without requiring to sign in.

There is a small drawback here: whole data in your database will be accessible, so make sure you don't store keys, tokens and other important stuff in the spreadsheet.

2. Playing With the URL

Base URL of our API will be http://spreadsheets.google.com/a/google.com/tq with only key required parameter in the query string.

If you use Google Apps replace google.com in the base URL with your domain, so /a/google.com/ becomes /a/your-domain.com/.

Parameters for the base URL are:

• key (required) - Extracted key of the spreadsheet.
• tq - URL encoded SQL query to filter data.
• tqx - Callback name for JSONP, prefixed with responseHandler:. This parameter is undocumented, but works perfectly.

In the end URL with SQL query (for example select * limit 10) and callback name should look like this: http://spreadsheets.google.com/a/google.com/tq?key=YOUR_KEY_HERE&tq=select%20*%20limit%2010&tqx=responseHandler:my_callback;

Make sure JSON data is returned when you access URL from the incognito browser tab. If you get an authorization error make sure spreadsheet sharing settings are correct.

3. Loading Data

As you guessed we will be using JSONP to load data. To simplify things here's a JSONP helper function in Vanilla JS.

var jsonp = function(url)
{
    var script = window.document.createElement('script');
    script.async = true;
    script.src = url;
    script.onerror = function()
    {
        alert('Can not access JSONP file.')
    };
    var done = false;
    script.onload = script.onreadystatechange = function()
    {
        if (!done && (!this.readyState || this.readyState === 'loaded' || this.readyState === 'complete'))
        {
            done = true;
            script.onload = script.onreadystatechange = null;
            if (script.parentNode)
            {
                return script.parentNode.removeChild(script);
            }
        }
    };
    window.document.getElementsByTagName('head')[0].appendChild(script);
};

// Example

var my_callback = function(data)
{
    console.log(data);
}

jsonp('http://spreadsheets.google.com/a/google.com/tq?key=YOUR_KEY_HERE&tq=select%20*%20limit%2010&tqx=responseHandler:my_callback');

4. SQL Syntax

Query Language of Google Charts API is basically a simplified SQL syntax. Please refer to Query Language Reference for full documentation.

Several key points here:

• SQL syntax has no from, everything is expected to be selected from the current sheet. So structure your query without from: select a, b, c where a = 'hello' and b = 'world' limit 10.
• There are no column names, so use select a, b, c instead of select name, email.

5. Parsing Data

Returned JSON is far from the ideal. To make it usable we have to restructure data as an associative array and its' items as key = value structure.

Here's a simple function I use to parse the data, it is not a perfect parser, but works most of the time:

var parse = function(data)
{
    var column_length = data.table.cols.length;
    if (!column_length || !data.table.rows.length)
    {
        return false;
    }
    var columns = [],
        result = [],
        row_length,
        value;
    for (var column_idx in data.table.cols)
    {
        columns.push(data.table.cols[column_idx].label);
    }
    for (var rows_idx in data.table.rows)
    {
        row_length = data.table.rows[rows_idx]['c'].length;
        if (column_length != row_length)
        {
            // Houston, we have a problem!
            return false;
        }
        for (var row_idx in data.table.rows[rows_idx]['c'])
        {
            if (!result[rows_idx])
            {
                result[rows_idx] = {};
            }
            value = !!data.table.rows[rows_idx]['c'][row_idx].v ? data.table.rows[rows_idx]['c'][row_idx].v : null;
            result[rows_idx][columns[row_idx]] = value;
        }
    }
    return result;
};

Final Example

var query = function(sql, callback)
{
    var url = 'http://spreadsheets.google.com/a/google.com/tq?',
        params = {
            key: 'YOUR_KEY_HERE',
            tq: encodeURIComponent(sql),
            tqx: 'responseHandler:' + callback
        },
        qs = [];
    for (var key in params)
    {
        qs.push(key + '=' + params[key]);
    }
    url += qs.join('&');
    return jsonp(url); // Call JSONP helper function
}

var my_callback = function(data)
{
    data = parse(data); // Call data parser helper function

    // Do whatever you want with the data.

}

query('select * where a = "hello" and b = "world" limit 20', 'my_callback');

---

In this tutorial I tended to use plain Vanilla JS, I enjoy using it for simple tasks. Also check out this excellent project called SheetRock for getting most out of Google Spreadsheets.

I hope you enjoyed reading this, please don't forget to upvote.