burnash / burnash/gspread

Support for Google query language

Open
#641 3 comments 0 reactions 0 assignees View on GitHub
Feature Request
Dominant language
Python
Stars
7.5k
Forks
979
PR merge metrics
No merged PRs in 30d

Description

We can use [Google Query Language](https://developers.google.com/chart/interactive/docs/querylanguage) 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](https://docs.google.com/spreadsheets/d/14xrSdZXaE0D83gjDXdyxjYttRSnhRGkz9a9qO_zip-E) test spreadsheet with age larger than 50:

```python
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.

Contributor guide

Open the contributing guide

Research direction

Start by reading the current `_finder` method and the existing Google Sheets request path. Compare how the Google Query Language endpoint returns table content and errors with the library's current behavior; done means users can issue such queries and receive the returned table or an appropriate query error.

Written by the indexing model from the issue text.

Assessment

Tech stack
python
Domain
api
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.