Search code examples
pythongoogle-sheets-apigoogle-api-python-clientgspread

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.

Solution

  • 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: