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

    References: