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

[Francisco_Fernandez]

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

• Qlik Web Connectors  December 2020
• Qlik Sense Business 
• Qlik Cloud 

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.

[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

[sid-ri-001]

@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]

Following this thread to get news.

[btremain_s]

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]

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]

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

[AdamG]

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  @sid-ri-001 

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]

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]

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