Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

[teemus]

Hello Folks,

Greetings!

I am trying to fetch data from Google spreadsheets using Qlik Google Drive & Spreadsheets Connector.

As a part of the advanced installation process, following steps are concluded by the administration team -

1. Installation 

2. SSL certificate creation (self-signed) - Exported & shared with us for client-side import

3. User creation for authentication

4. deploy.config update to enable SSL i.e. HTTPS access over default port 5555

From the development-end, we have ensured that we have completed the following steps

1. Certificate import

2. Sign-in using the credentials provided by admin

3. Authorization for access to Google Drive using OAUTH2.0 

4. New data connection pointing towards the Web Connectors URL

5. These methods run seamlessly fine - canAuthenticate, ListSpreadsheets, ListWorksheets

6. The getWorksheet method fails with the said error:

Any inputs would be really helpful.

Thanks!

Best,

Sumeet

[teemus · Accepted solution]

I am able to retrieve data from a test worksheet (privately owned) using the GetSheetValues call, if that helps. 🙂

It has one tab viz. test tab and has data range A1 to B3 - specified as test tab!A1:B3 in the range param.

Regarding the 400 error for the GetWorksheet call, it still persists (we are now using the Dec-2020 version of QWC, and the account that is being used for this exercise has no added privileges from the API console apart from the ones QWC requests when it creates the API token).

You may try the resolution suggested here and see if it works.

Best,

Sumeet

[Jamie_Gregory]

This is a known issue that is happening. Please see the Support Updates Blog for more information. 

[RafaelBarrios]

hi all,

Some people have reported that the issue is resolved by making the sheets public, but it would be a security issue.

There is an alternative to not having to make the sheets public, and that is not to use the getWorkSheet statement, but to use the getSheetValues statement.
You would have to change the url and instead of sending the worksheet id together with the sheet id, place in the url the id of the file in the parameter spreadsheetKey and the name of the sheet with the parameter range.

something like this:

LOAD
.....

...

FROM

[http://hostname:5555/data?connectorID=GoogleDriveConnector&table=GetSheetValues&spreadsheetKey=$(FileId)&range=$(SheetName)&valueRenderOption=FORMATTED_VALUE&dateTimeRenderOption=FORMATTED_STRING&appID=&loadAccessToken=$(vL.GoogleToken)](qvx);

Hope it help.