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

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

Francisco_Fernandez — Tue, 10 May 2022 19:15:54 GMT

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.) ---> 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

December 2020

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.

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

p_verkooijen — Tue, 04 May 2021 08:21:48 GMT

@Francisco_Fernandez any idea if this gets fixed asap?
Cannot share my data with "the world"

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

migle_purzelyte — Tue, 04 May 2021 08:25:14 GMT

GetSheetValues is still working but it requires to change lots of script.

Can we expect another workaround than make the private data public?

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

Francisco_Fernandez — Tue, 04 May 2021 08:47:57 GMT

Hello @p_verkooijen @migle_purzelyte ,

I understand your concerns but currently, that's the only workaround that we have found to keep the connector working without major changes in the script, as @migle_purzelyte mentioned.

We only suggest that workaround for very specific cases where the data is already public or doesn't contain private information.

R&D is currently working to solve the problem as soon as possible, once R&D confirms the resolution we will update the article.

Best regards,

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

AndreasE — Tue, 04 May 2021 10:04:10 GMT

Hope this get fixed asap.

We can't use the workaround and are in need of it in Sense.

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

Francisco_Fernandez — Tue, 04 May 2021 11:27:01 GMT

Hello @AndreasE ,

We are constantly updating this article, could you please check now?

Best regards,

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

u_aliyev — Tue, 04 May 2021 12:04:16 GMT

@Francisco_Fernandez To have it fixed, should we install a new version/fix or should the fix be applied on existing installations? If it is the second, then it seems that the issue is still present.

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

p_verkooijen — Tue, 04 May 2021 12:13:58 GMT

@Francisco_Fernandeztested the GetSheetValues workaround and this works.

However got more than a dozen clients using a range of spreadsheets so a lot of workarounds todo..

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

AndreasE — Tue, 04 May 2021 12:22:16 GMT

@Francisco_Fernandez I ment we have many clients with private sheets so we can't use the workaround and hoping for this to get patched or a better workaround.

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

p_verkooijen — Tue, 04 May 2021 12:34:24 GMT

@Francisco_Fernandez the workaround GetSheetValues works with private spreadsheets.
Spreadsheet set to Public is needed as a workaround using the GetWorksheet

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

Croisfelt — Tue, 04 May 2021 12:46:02 GMT

Same issue here for all data extraction from google worksheets in our company.

It seems only 'GetWorkSheet' started yesterday to return "There was an error running the table (Failed on attempt 1 to GET. (The remote server returned an error: (400) Bad Request.))".

We tried cleaning cache but the connector keeps given the error message. Now we are trying to install a previous version of the webconnector to see if it works.

Following this thread to get news.

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

Francisco_Fernandez — Tue, 04 May 2021 12:52:45 GMT

@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

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

sid-ri-001 — Tue, 04 May 2021 13:46:53 GMT

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

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

MenzeKnoop — Tue, 04 May 2021 13:54:26 GMT

Following this thread to get news.

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

btremain_s — Tue, 04 May 2021 14:21:04 GMT

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.

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

tduarte — Tue, 04 May 2021 14:45:44 GMT

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

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

nacho_bonet — Tue, 04 May 2021 14:46:57 GMT

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

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

AdamG — Tue, 04 May 2021 14:58:53 GMT

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!

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

Francisco_Fernandez — Tue, 04 May 2021 15:16:57 GMT

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

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

tduarte — Tue, 04 May 2021 15:45:01 GMT

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