Qlik Community

Knowledge

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.

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

Francisco_Fernandez

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

When using the google drive & spreadsheet the table "GetWorksheet"  it might fail with one of the following messages depending on the platform used 

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.

Cloud error message:

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

 

Environment

 

Current Status

 

This issue has been resolved in a fix that was pushed to Qlik Sense Enterprise SaaS and Qlik Sense Business on Friday, May 7th. A patch is available for Qlik Web Connectors, December 2020 Patch 2. The patch can be found on the Qlik Download site.

GetWorksheet is now deprecated. GetWorksheetV2 or GetSheetValues should be used moving forward.

Workarounds

 

If the Gsheet has to be Private:

Alternatively, to "GetWorksheet" the "GetSheetValues" and the parameters shown in the attached PDF, should return the same data as the table "GetWorksheet".  Please, make sure to double-check that the data matches before doing any changes to the apps.

If the Gsheet can be Public:

Making the sheet public also solves the problem and allows you to keep using the table "GetWorksheet", but please note that we only suggest this workaround for very specific cases where the data is already public or doesn't contain confidential information.

 

Cause

Qlik Web Connectors used a legacy method for getWorksheet. A change to this API by Google caused the defect. This has been logged with Qlik as product Defect ID: QCWP-3096.

Labels (1)
Attachments
Comments
Francisco_Fernandez

@p_verkooijen ,

I've edited the article, hopefully now is clearer.

@Croisfelt,

I don't think that reverting back to a previous version will solve the problem, the change happened on google's API so it's quite likely that all the versions containing that API call will fail.

@u_aliyev,
The fix will be likely provided with a new version/patch

mrsid001
Contributor
Contributor

@Francisco_Fernandez, both the work around works, but our company has more than 100 apps which requires this change if using getSheetValue and making all the sheets public is also not a viable solution.

Can you suggest, how long will it take for the R&D to provide a solution and then a possible new version or patch?

MenzeKnoop
Partner
Partner

Following this thread to get news.

btremain_s
Contributor
Contributor

This issue is also causing big problems in my org so following this thread with interest! Knowing an estimated timescale for the fix would be beneficial please @Francisco_Fernandez - that way we can better make a decision on whether to spend the time implementing the workaround or to wait it out.

tduarte
Partner
Partner

Hi @Francisco_Fernandez 
As mentioned via Qlik Support, we also need know the time estimate for Qlik to apply a fix to the Web Connectors so that we can then decide whether to wait or start working on the workaround.
This information is paramount so that we can make a decision on the next steps.

Thank

nacho_bonet
Partner
Partner

Is there any estimated time of having the patch or the new version?

AdamG
Contributor
Contributor

We have came across the same issue today. Lots of apps depending on Google Spreadsheets connector since we use it for Section Access management.  So effectively they no longer reload... Changes would be massive 😞 Any information on estimated fix greatly appreciated!

Francisco_Fernandez

@AdamG @nacho_bonet @tduarte  @btremain_s  @mrsid001 

Unfortunately, we cannot provide a time estimation yet, but Support is collaborating closely with R&D to provide a solution as soon as possible.

The article will be kept updated with the latest news.

Please feel free to ping me if you have any doubts or further questions.

Best regards,

tduarte
Partner
Partner

Hi @Francisco_Fernandez 

I understand this may not be straight forward but we have internal users and external clients expectations to manage, not to mention service agreements, so we cannot just ask users to wait indefinitely for an update.

Again, could you kindly please provide a rough estimation on how long a fix may take so that we can make an internal decision on the next steps? Having a estimate of a day vs a week will already be quite useful.

Regards

pauljohansson
Creator III
Creator III

This issue started to affect us today (2021-05-04). Its a pain since we make use of several spread sheets in our daily data load.

We are using 1.2.0.16945.

It would be great with a estimation on when this will be fixed,

Thanks in advance,

br

Paul

Version history
Revision #:
14 of 14
Last update:
‎2021-05-11 09:12 AM
Updated by: