Can't access to Google Sheet2 tab (sheetName)

Bugs and Other Issues
,
1

Hi there!,

I'm trying the Google sheet extension but I ran into a problem, I can acces the information from Sheet1 but not from Sheet2.

GSheetTab

Here are my blocks and I'm also attaching the aia file.

GSheet

gsheet.aia (1.9 KB)

Google sheet has the public permission and I can do whatever I want when Sheet1 is used but can't access to Sheet2:

gsheetError
gsheetError720×1600 70.1 KB

Thanks for any hint!

2

Seems there is something wrong with your spreadsheet (not sure what or why)

Even in a browser the url fails:

https://docs.google.com/spreadsheets/d/1XaTFZ4b_qjjZtbbyvUH2_dxc7fZxanZudLio63VglEo/export?format=csv&range=Sheet2!2:2

Seems to not work on another spreadsheet I have with Sheet2.

This url works

https://docs.google.com/spreadsheets/d/1XaTFZ4b_qjjZtbbyvUH2_dxc7fZxanZudLio63VglEo/export?format=csv&gid=1520834199&range=2:2

[EDIT]
and this doesn't work

https://docs.google.com/spreadsheets/d/1XaTFZ4b_qjjZtbbyvUH2_dxc7fZxanZudLio63VglEo/export?format=csv&sheet=Sheet2&range=2:2

4

Hi,

That is a new spreadsheet I created to reproduce the issue in a simpler way, but it happens to me with another one where I discovered the problem, those are normals Google spreadsheets, I did not modify any configuration, just the public permission. I guess it will happen with any spreadsheet I will use.

Note that the third link that you sent brings information from Sheet1 (AA,BB when it should be CC,DD) even when you specify Sheet2 in the URL.

I might be wrong but I think that the sheetName value is not being taken into account, take a look at this:

https://docs.google.com/spreadsheets/d/1XaTFZ4b_qjjZtbbyvUH2_dxc7fZxanZudLio63VglEo/export?format=csv&sheetname=Sheet00000000000000000000000&range=2:2

(note that there are no Sheet00000000000000000000000 in the spreadsheet)

That brings information from Sheet1, so no mater what sheet you specify (Sheet2 | inexistent) you get Sheet1 information.

It seems to me that sheetname is not being taken into account as a parameter by Google.

Thanks!

5

This URL uses sheet parameter instead of sheetname and it works fine:

(https://docs.google.com/spreadsheets/d/1XaTFZ4b_qjjZtbbyvUH2_dxc7fZxanZudLio63VglEo/gviz/tq?tqx=out:csv&sheet=Sheet2)

When you replace sheet with sheetname it happens the same as with the APP, it brings the Sheet1 and not Sheet2 data, it seems to me sheetname is not a valid parameter.

Can you please be so kind to confirm this?

Thanks in advance!

6

Your .aia lacks a .json file for its spreadsheet component.

7

Hi ABG,

As far as I understand since I will only read the spreadsheet I do not need that part of the integration. Are you saying that error message is because the lack of the .json file?

Note that I'm not having issues accesing data due to permissions, it seems to be due to the sheetName paremeter, kindly check previous post with my tests.

Thanks!.

8

My previous commment was based on this:

"Note: If you are only going to read from the Google Sheets document, and not edit or write data to the document, you can skip the process of making a Service Account. The only requirement is that the Google Sheets document is shared such that anyone with the link can read the document."

Extract from: Google Sheets API Setup

9

I see your point on the .json file being required only for updates according to that doc.

From @TimAI2's tests, it looks like there is a requirement that ranges need gids instead of sheet names in range queries, and that might have slipped past testing of the Ai2 component.

Either that, or format=csv is incompatible with range requests.

I have used the spreadsheet component only with a ,json file, so this is new to me.

10

Another idea...

The Google Sheets API has evolved over time, as has AI2's attempts to catch up with it

Some version archeology might be needed.

11

I believe so.

12

Google Sheets will usually return data on the first sheet if no other correct parameters are provided. It seems to ignore incorrect parameters instead of throwing an error.

My confusion above was with gviz, which does return data with the parameter sheet=Sheet2

As I have shown, and @ABG surmised, the spreadsheet component needs to have it's internal url adjusted, and for it to introduce the gid (fetched from the sheetName) as a separate parameter to the url.

Just tested with an aia project using json credentials, and the block does work! - which might explain why not working without credentials got missed in testing....

13

(added to Open Issues section of FAQ)

14

I'm sorry, I got lost in the json file comment, if the poject has the json file will it work fine with different tabs?

Is there any workaround I can implement?

Otherwise I guess I will have to modify the spreadsheet an move all to Sheet1

Thanks!

15

I just saw an open issue with my name on it :slight_smile:

16

I have reviewed the code and can confirm this is an oversight in the code path related to unauthenticated requests.

1 Like
17

If you continue with the unauthenticated spreadsheet component, you can use the web component instead to get the row.

image
image786×557 59.2 KB

18

Thank you very much to all the people involved in my post, I've just confirmed that after implementing the .json file in my project I can access to Sheet2 using the same code, that made the difference and now it is working as expected.

TIMAI2: I wll analyze the web component suggestion too, thanks!.

19

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.