CODEWITHSUNDEEP
pythongoogle-sheets-apigoogle-api-python-clientgspread
Ruchit Vithani
Ruchit Vithani

Reputation: 341

Query a google spread sheet with google query language in gspread

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.

Upvotes: 2

Views: 916

Answers (1)

Tanaike
Tanaike

Reputation: 201553

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:

Upvotes: 2

Related Questions