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
patilamay
Contributor III
Contributor III

Hi @Francisco_Fernandez 

Following this post since morning because all our data loads failed because of google sheet connector issue and  it's our month start (this is when we have lots of engagement with our dashboards).

For now we manually changed our most important dashboard feeds to use data from excel instead google sheets 😞 but we need to address the google sheets issue soon. 

Can you let us know if this will be resolved soon? 

Thanks,

Emmanuelle-Bustos

Hi, Since yesterday we started to having the same issue:
"There was an error running the table (Failed on attempt 1 to GET. (The remote server returned an error: (400) Bad Request.))"

For too many Apps.

gardenierbi
Creator
Creator

With the GetSheetValues option we get also errors :

&table=GetSheetValues&spreadsheetKey=xxxxx&range=per_product&valueRenderOption=FORMATTED_VALUE&dateTimeRenderOption=FORMATTED_STRING

Result :

{ "error": { "code": 400, "message": "Unable to parse range: per_product", "status": "INVALID_ARGUMENT" } }

&table=GetSheetValues&spreadsheetKey=xxxxx&range=per_product!A1%3aG978&valueRenderOption=FORMATTED_VALUE&dateTimeRenderOption=FORMATTED_STRING

Result :

{ "error": { "code": 400, "message": "Unable to parse range: per_product!A1:G978", "status": "INVALID_ARGUMENT" } }

&table=GetSheetValues&spreadsheetKey=xxxxx&range=per_product_v3!A1%3aG978&valueRenderOption=FORMATTED_VALUE&dateTimeRenderOption=FORMATTED_STRING

Francisco_Fernandez

Hello all,

The connector seems to be up and running again, could you please confirm?

Best regards,

 

mrsid001
Contributor
Contributor

The connector is still failing for few apps with the same error. However few are getting success.

What was the fix? Is there any configuration changes required from the Qlik Webconnector as not all reloads are success?

anirmalan
Partner
Partner

Yes, still failing for few apps 

Francisco_Fernandez

Hello,

In case the connector is still failing, could you please provide the logs in a support case?

We are trying to understand why some connections are still failing.

https://community.qlik.com/t5/Knowledge-Base/Qlik-Web-Connectors-Log-Files/ta-p/1714354

 

Best regards,

pauljohansson
Creator III
Creator III

For us GetWorkSheet works again, we are using  WebConnector 1.2.0.16945

 

br

Paul

Joan_Qlik
Contributor
Contributor

Hi @Francisco_Fernandez ,

I have problems with one connection:

Joan_Qlik_0-1620199662546.png

Joan_Qlik_1-1620199740403.png

 

Best regards,

 

Francisco_Fernandez

Hello @Joan_Qlik ,

Could you please open a support case and provide the logs as described in this article?

https://community.qlik.com/t5/Knowledge-Base/Qlik-Web-Connectors-Log-Files/ta-p/1714354

Best regards,

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