0

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.

PatrickdC
  • 1,385
  • 1
  • 6
  • 17
Chris Switalski
  • 68
  • 6

1 Answers1

2

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Hi Tanaike, thank you for your answer. The thread you gave in references concerns public spreadsheets. However, the problem applies to private ones. I have tried both with ?access_token and with Authorization: Bearer token and still get a 401 error – Chris Switalski Mar 21 '23 at 01:30
  • @Chris Switalski Thank you for replying. About `The thread you gave in references concerns public spreadsheets.`, yes. So, I proposed the method for using the access token. About the status code 401, in this case, it seems that the error of unauthorized occurs. So, please confirm the current scopes of the access token. Unfortunately, I cannot find your scopes in your question. I apologize for this.And also, in this case, when your access token cannot access another user's Spreadsheet, you cannot access it and an error like 401 occurs. Please be careful about this. – Tanaike Mar 21 '23 at 01:43
  • @Chris Switalski When I tested the request using my Spreadsheet and my access token (in this case, the scope of Drive API is used.), no error occurs. Even when the Spreadsheet is not publicly shared, the result value is returned by using the access token. I apologize for this situation. If you cannot understand my reply, can you provide the detailed flow for correctly replicating your current situation of `I have tried both with ?access_token and with Authorization: Bearer token and still get a 401 error`? By this, I would like to confirm it. – Tanaike Mar 21 '23 at 01:43
  • 1
    @Chris Switalski Now, I added the information related to the scopes using this endpoint. Please confirm it. – Tanaike Mar 21 '23 at 01:59
  • I have tried with the "spreadsheets.readonly" scope. It seemed to me that this was the right scope for the job. After that I also tried with "drive.readonly" and "drive" with no luck. This is the token I received after loging the user in using OAuth2. It is still active because requests through sheets.googleapis.com are working fine. – Chris Switalski Mar 21 '23 at 02:06
  • @Chris Switalski Thank you for replying. About `I have tried with the "spreadsheets.readonly" scope.`, I think that your current issue is due to this. And, about `After that I also tried with "drive.readonly" and "drive" with no luck.`, when I tested these scopes, the request worked fine. I apologize for this situation. For example, do you have permission for accessing the Spreadsheet? If you have no permission, an error occurs. I deeply apologize that I cannot know your actual situation. – Tanaike Mar 21 '23 at 02:10
  • @Chris Switalski About `It is still active because requests through sheets.googleapis.com are working fine.`, your issue has already been resolved? – Tanaike Mar 21 '23 at 02:10
  • Ohh.. Sorry I tried it with spreadsheets.readonly only, my mistake. With "spreadsheets" it works correct! It's still a bit strange to me that I can't use readonly for such task - after all, I'm not modifying any data this way. – Chris Switalski Mar 21 '23 at 02:10
  • @Chris Switalski Thank you for replying. About `It's still a bit strange to me that I can't use readonly for such task`, I think that this might be the current specification. For example, when you want higher security using the restricted scope, how about using `drive.file`? In this case, first, it is required to create a new Spreadsheet using the access token with `drive.file` and copy the values to the created Spreadsheet. By this, the above endpoint can be accessed with `drive.file`. And, in this case, only the file created by `drive.file` can be accessed. – Tanaike Mar 21 '23 at 02:20
  • @Chris Switalski Now, I added more information using the scope of `drive.file` in my answer. – Tanaike Mar 21 '23 at 02:23