Issue identified with Google Spreadsheet Connector

[Jamie_Gregory]

Hello Qlik Users!

An issue has been identified with the Google Spreadsheet Connector when using “GetWorksheet”. The issue affects:

• Qlik Web Connectors December 2020
• Qlik Sense Business
• Qlik Sense Enterprise SaaS

Two error messages can occur, depending on the platform.

Qlik Web Connectors on-premise error message

<Exception>APIConnectorInterfaces.Exceptions.APIConnectorException: Failed on attempt 1 toGET. (The remote server returned an error: (400) Bad Request.) ---&gt; System.Net.WebException: Theremote server returned an error: (400) Bad Request.

Qlik Cloud Services error message

HttpStatusCode: BadRequest, StatusCode: 400, StatusDescription: Bad Request, Message: The remote server returned an error: (400) Bad Request., ResponseBody: Error 400 (Bad Request)!!1

There is currently a workaround, which you can find in the article:

Qlik Web connectors, google spreadsheets connector Error "The remote server returned an error: (400)...

R&D is currently investigating the issue. Qlik Support will update the article with information as it comes in.

Kind regards,

Qlik Digital Support

Edited to add: There is also the Known Issues for Qlik Sense SaaS article. Please bookmark this page to use for future reference. 

Update 5/6/2021 at 9:45 am EDT: Qlik is developing an update to the Connector package to use an improved getWorksheetmethod. We expect the update to be available Monday. When the update is released, we will also post instructions on how to move to the new method, along with considerations for testing.

Update 5/11/2021 at 9:04 am EDT: A fix was pushed out to Qlik Sense Enterprise SaaS on Friday, May 7th. A patch is now available for Qlik Web Connectors. Please see Qlik Web Connectors Patch available for Google Spreadsheet Connector Issue for more information.

[NITIN_GUPTA]

Yes facing issues with fetching data while using get worksheet option,

list spreadsheets and list worksheets are working bu not get worksheet

[josemanzod]

Since yesterday at approximately 11:00 hours we have this problem, we changed some pages to public to solve it momentarily.

I hope it can be solved soon.

Thanks

[lashagoch]

We are also waiting for this issue to be resolved...

[RafaelBarrios]

hi all,

There is another 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.

[MichelLalancette]

While applying the suggested workaround for private sheets, I noticed the issue seems to be resolved?

Can we get an official status?

[josemanzod]

It works again in Qlik Sense Saas and Qlikview.

Thank you.