Support for Google query language
- 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
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