Support for Google query language

[hkalexling]

We can use Google Query Language to query the content of Google Sheets. It's a subset of SQL and I think it's much more flexible than the current _finder method. All we need to do is just sending a GET request to https://docs.google.com/spreadsheets/d/[id]/gviz/tq/tq=[query].

Here's a simple example where we query all the records in this test spreadsheet with age larger than 50:

import json
import requests

def query(q):
    parts = ['https://docs.google.com/spreadsheets/d/14xrSdZXaE0D83gjDXdyxjYttRSnhRGkz9a9qO_zip-E', '/gviz/tq']
    url = '/'.join(p.strip('/') for p in parts)
    res = requests.get(url, params={'tq': q}).text
    res = res.split('{', 1)[-1]
    res = '{' + res[::-1].split('}', 1)[-1][::-1] + '}'
    j = json.loads(res)

    if j.get('status') != 'ok':
        if j.get('errors'):
            raise QueryException(j.get('errors')[0].get('detailed_message'))
        else:
            raise QueryException(j.get('status'))

    table = []
    for r in j.get('table').get('rows'):
        row = []
        for c in r.get('c'):
            row.append(c.get('f') or c.get('v'))
        table.append(row)

    return table

print(query('select * where D > 50'))

The only drawback that I can think of is that we can only get the table content, but not the row/column indices, but I think it's good to have this option there for users need it.

@burnash If you think it's a good idea, I would be happy to submit a PR.

[burnash]

I'm really sorry for the late reply. Yes, I think this is a good idea. Let me know if you are still interested in submitting a PR.

[ruchit2801]

@burnash Has anyone submitted a PR for this? If not, can I work on it and send a PR?

[burnash]

@ruchit2801 as far as I know, there's no PR for this. If you're interested, please go ahead.