1

Is it possible to execute queries on spreadsheets using Google Query Language in gspread API? I was wandering if we can use this API call to execute these queries.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
Ruchit Vithani
  • 331
  • 4
  • 12
  • `gspread` is a library for Sheets API, and the Visualization API you reference is a completely different API, so you cannot execute one with the other. If you provide more details about what you want to accomplish, though, maybe a solution can be found. – Iamblichus Apr 14 '21 at 09:12
  • I want to execute queries as described in google query language site. e.g. `select A where B>=5000` – Ruchit Vithani Apr 14 '21 at 09:52
  • I'm familier with filtering in sheets API, but this approach is much more efficient and easier to do, compared to first building a filter, then setting a filter, then fetching all rows, and then clearing the basic filter. – Ruchit Vithani Apr 14 '21 at 09:54
  • Is there any similar library for visualisation? E.g. as gspread is for sheets API, anything similar for visualization api? – Ruchit Vithani Apr 14 '21 at 09:58
  • Depends on what you mean, by `execute queries` you mean retrieving the results of a query in your script? Also, `gspread` is not the official Python library for Sheets API, [here](https://developers.google.com/sheets/api/quickstart/python) you can see an example of the official library in use as well as the library docs. – Iamblichus Apr 14 '21 at 10:12

1 Answers1

1

I believe your goal as follows.

  • You want to retrieve the values using Query language.
  • You have a script for using gspread, and you want to achieve this using the script.

Modification points:

  • Unfortunately, in the current stage, the Query language cannot be directly used with Sheets API. This has already been mentioned in the comments for your question.
  • But, when requests library is used, the Query language can be used.
    • "Sample script 3" of this thread is for Google Apps Script. Ref Using this method, I think that your goal can be achieved with python. And, the authorization script for using gspread can be also used for this method.

When above points are reflected to a script, it becomes as follows.

Sample script:

In this case, the access token is retrieved from credentials of client = gspread.authorize(credentials) for using gspread.

client = gspread.authorize(credentials) # Here, please use your authorization script for using gspread.

spreadsheetId = '###' # Please set the Spreadsheet ID.
sheetName = 'Sheet1' # Please set the sheet name.

query = 'select A where B>=5000' # This is from your sample query.
url = 'https://docs.google.com/spreadsheets/d/' + spreadsheetId + '/gviz/tq?sheet=' + sheetName + '&tqx=out:csv&tq=' + urllib.parse.quote(query)
res = requests.get(url, headers={'Authorization': 'Bearer ' + credentials.access_token})
print(res.text)
  • In this script, import urllib.parse and import requests are also used.
  • When you want to use the sheet ID instead of sheet name, please modify url = 'https://docs.google.com/spreadsheets/d/' + spreadsheetId + '/gviz/tq?sheet=' + sheetName + '&tqx=out:csv&tq=' + urllib.parse.quote(query) to url = 'https://docs.google.com/spreadsheets/d/' + spreadsheetId + '/gviz/tq?gid=' + sheetId + '&tqx=out:csv&tq=' + urllib.parse.quote(query)
  • In this case, it seems that the scopes for using Sheets API and Drive API can be used.

Note:

  • Above sample script returns the data as the CSV data.
  • For example, when the Spreadsheet is publicly shared, the access token is not required to be used. So, in that case, you can retrieve the data with res = requests.get(url).

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks for this. I was kind of aware of this approach, but this clears that right now gspread has nothing to do this task or use query language – Ruchit Vithani Apr 14 '21 at 12:25
  • @Ruchit Vithani Thank you for replying. Yes. In this case, the authorization script for gspread is used in order to retriev the access token. When the Spreadsheet is publicly shared, the access token is not required to be used. In that case, the authorization script is also not required to be used. – Tanaike Apr 14 '21 at 12:28