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
Q&A with Qlik! New Case Portal, Chat Bot and Qlik Community. Oct. 19th REGISTER TODAY

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
p_verkooijen
Partner
Partner

 

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

 

migle_purzelyte
Partner
Partner

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

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

Francisco_Fernandez

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,

AndreasE
Partner
Partner

Hope this get fixed asap.

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

Francisco_Fernandez

Hello @AndreasE ,

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

Best regards,

u_aliyev
Contributor
Contributor

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

p_verkooijen
Partner
Partner

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

AndreasE
Partner
Partner

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

p_verkooijen
Partner
Partner

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

Croisfelt
Contributor
Contributor

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.

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