Search code examples
google-sheetsgoogle-apigoogle-visualization

Querying a Google Sheet API using GViz


I would like to query the Google Spreadsheet using GViz (SQL like query language) and Google API on behalf of users of my app, however, in the Sheets API (https://developers.google.com/sheets/api/guides/concepts) I could not find such an endpoint.

I know that I can do it with: https://spreadsheets.google.com/tq?tqx=out:csv&tq={Query}&key={SheetID} but I don't know how to authenticate it using user tokens. It's different than API endpoints authentication (https://sheets.googleapis.com/v4/spreadsheets)

Is there any way to do this?

I am looking for any way to using Google API filter data by Google Visualization Api Query Language (or other query language) get json/csv with filtered results. I only have users access tokens. Tokens are ofc with appropriate scope.


Solution

  • I thought that in this case, the endpoint might be https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq?tqx=out:csv&tq={Query} instead of https://spreadsheets.google.com/tq?tqx=out:csv&tq={Query}&key={SheetID}.

    And, when you want to request the endpoint using your access token, how about the following request?

    GET https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq?tqx=out:csv&tq={Query}&access_token={your access token}
    

    or

    GET https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq?tqx=out:csv&tq={Query}
    Request header --> Authorization: Bearer {your access token}
    

    When these are converted to the curl command, it becomes as follows.

    curl -L "https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq?tqx=out:csv&tq={Query}&access_token={your access token}"
    

    or

    curl -L -H "Authorization: Bearer {your access token}" "https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq?tqx=out:csv&tq={Query}"
    

    Note:

    • When you use the access token, please include one of the following scopes.
      • https://www.googleapis.com/auth/spreadsheets

      • https://www.googleapis.com/auth/drive.readonly

      • https://www.googleapis.com/auth/drive

      • https://www.googleapis.com/auth/drive.file

        • In this case, it is required to create a Spreadsheet using the access token with the scope of https://www.googleapis.com/auth/drive.file. When the existing Spreadsheet is accessed using the access token of this scope, an error like 404 occurs. Please be careful about this.
      • When https://www.googleapis.com/auth/spreadsheets.readonly is used as the scope, a 401 error occurs. Please be careful about this.

    References: