🥇 Google Spreadsheet JSON API: SQL Filtering

Usar o Google Spreadsheet como back-end JSON não é uma história nova , mas você sabe o que é mais sexy? Filtrando dados dessa planilha com sintaxe SQL simples!

Esta técnica é baseada na linguagem de consulta da API de gráficos do Google. Neste tutorial, irei orientar todo o processo, desde a preparação da planilha até os dados prontos para uso.

1. Preparando uma planilha

Por padrão, as planilhas do Google Drive são privadas. Vá para File -> Publish to the web, publique a planilha, pegue a URL e extraia a chave dela.

Você também deve compartilhar sua planilha com todos, mas apenas com permissão de leitura. Acesse File -> Sharee selecione as Anyone with the linkopções de compartilhamento de link. A ideia é tornar a planilha acessível sem a necessidade de fazer login.

Há uma pequena desvantagem aqui: dados inteiros em seu banco de dados estarão acessíveis, portanto, certifique-se de não armazenar chaves, tokens e outras coisas importantes na planilha.

2. Brincando com o URL

O URL base de nossa API terá http://spreadsheets.google.com/a/google.com/tqapenas o keyparâmetro obrigatório na string de consulta.

Se você usar o Google Apps, substitua google.como URL base por seu domínio, o que /a/google.com/será feito /a/your-domain.com/.

Os parâmetros do URL base são:

chave (obrigatório) – chave extraída da planilha.
tq – consulta SQL codificada em URL para filtrar dados.
tqx – nome de retorno de chamada para JSONP, prefixado com . Este parâmetro não está documentado, mas funciona perfeitamente.responseHandler:

No final, URL com consulta SQL (por exemplo ) e nome de retorno de chamada deve ser semelhante a este :;select * limit 10http://spreadsheets.google.com/a/google.com/tq?key=YOUR_KEY_HERE&tq=select%20*%20limit%2010&tqx=responseHandler:my_callback

Certifique-se de que os dados JSON sejam retornados ao acessar o URL na guia anônima do navegador. Se você receber um erro de autorização, verifique se as configurações de compartilhamento da planilha estão corretas.

3. Carregando dados

Como você adivinhou, usaremos JSONP para carregar dados. Para simplificar as coisas, aqui está uma função auxiliar JSONP no 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. Sintaxe SQL

A linguagem de consulta da API de gráficos do Google é basicamente uma sintaxe SQL simplificada. Consulte a Referência da linguagem de consulta para obter a documentação completa.

Vários pontos-chave aqui:

A sintaxe SQL não tem from, tudo deve ser selecionado na planilha atual. Então estruturar sua consulta sem from: .select a, b, c where a = 'hello' and b = 'world' limit 10
Não há nomes de coluna, então use em vez de .select a, b, cselect name, email

5. Análise de dados

O JSON devolvido está longe de ser o ideal. Para torná-lo utilizável, temos que reestruturar os dados como um array associativo e seus itens como estrutura chave = valor.

Aqui está uma função simples que uso para analisar os dados, não é um analisador perfeito, mas funciona na maioria das vezes:

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;
};

Exemplo Final

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');

Neste tutorial, tendem a usar Vanilla JS simples, gosto de usá-lo para tarefas simples. Confira também este excelente projeto chamado SheetRock para obter o máximo do Google Spreadsheets.

Espero que você tenha gostado de ler isso, por favor, não se esqueça de votar positivamente .