Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
teemus
Partner - Contributor III
Partner - Contributor III

Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

Hello Folks,

Greetings!

I am trying to fetch data from Google spreadsheets using Qlik Google Drive & Spreadsheets Connector.

As a part of the advanced installation process, following steps are concluded by the administration team -

1. Installation 

2. SSL certificate creation (self-signed) - Exported & shared with us for client-side import

3. User creation for authentication

4. deploy.config update to enable SSL i.e. HTTPS access over default port 5555

From the development-end, we have ensured that we have completed the following steps

1. Certificate import

2. Sign-in using the credentials provided by admin

3. Authorization for access to Google Drive using OAUTH2.0 

4. New data connection pointing towards the Web Connectors URL

5. These methods run seamlessly fine - canAuthenticate, ListSpreadsheets, ListWorksheets

6. The getWorksheet method fails with the said error:

2020-10-19 - Copy_LI (3).jpg

Any inputs would be really helpful.

Thanks!

Best,

Sumeet

Labels (2)
22 Replies
chrisbrain
Partner - Specialist II
Partner - Specialist II

Hi - Not sure but that error doesn't look like it's related to QWC (it mentions QlikSenseEmailAdmin.exe).

Are you sure you located the correct matching log entry? Maybe you have several SSL issues from this machine?

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
chrisbrain
Partner - Specialist II
Partner - Specialist II

BTW - My current guess is this is not about the certificates you are generating but about the train of trust to the certificate for docs.google.com (or some other google certificate) unless you are using client certs to authenticate to ADFS/google?

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
teemus
Partner - Contributor III
Partner - Contributor III
Author

Hi @chrisbrain ,

Yes, there are 14 error events (on running the method) with task categories 'Verify Revocation' X 3, then 'Build Chain' X 1, followed by 'Verify Chain Policy' X 10. Could you please suggest a way I can share the event log file with you?

chrisbrain
Partner - Specialist II
Partner - Specialist II

Hi @teemus ,

Sorry  but I don't think I can help you debug windows certificate issues. This doesn't seem like a QWC issue at this point but hopefully I have given you something to go on.

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
teemus
Partner - Contributor III
Partner - Contributor III
Author

Hi @chrisbrain,

Most definitely, your support has been extremely valuable.

I will continue troubleshooting with the server team and keep you posted on the result.

Thanks once again! 🙂 

chrisbrain
Partner - Specialist II
Partner - Specialist II

Great - and yes if you think you find anything which will help others or have anything I can try to help with let me know!

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
Senor_Dai
Partner - Creator II
Partner - Creator II

Hi @teemus - did you resolve this issue as we are experiencing the same .. Many thanks

basilusp
Contributor
Contributor

Hi everyone,

It seems that the problem might be general because we are experiencing it also since today (everything was fine yesterday).

So everything seems to be OK until the GetWorksheet part (we can authenticate, list spreadsheet and list worksheet). For the GetWorksheet, we get an 400 - Bad request error.

We are using the last patch of the December 2020 version of QWC.

Any ideas on how to resolve this ? Thanks a lot for the help !

Here attached is the exception details we get:

sid-ri-001
Contributor
Contributor

Having the same issue in our environment also. 

Qlik Google Drive Web Connector GetWorksheet method failing with Bad Request Error in all the environment

Since yesterday, all the call to Qlik Google Drive Web Connector GetWorksheet method is failing. Below is the error log which is shown in the log.

Failed on attempt 1 to GET. (The remote server returned an error: (400) Bad Request.)

APIConnectorInterfaces.Exceptions.APIConnectorException: Failed on attempt 1 to GET. (The remote server returned an error: (400) Bad Request.) ---&gt; System.Net.WebException: The remote server returned an error: (400) Bad Request.<br /> at System.Net.HttpWebRequest.GetResponse()<br /> at APIConnectorEngine.Qwc.HttpHelper.MakeRequest(eHTTPMethod method, String url, Object postData, Object state, Int32 attempt)<br /> at APIConnectorEngine.Qwc.HttpHelper.MakeRequestWithRetries(eHTTPMethod method, String url, Object postData, Object state, HttpCallConfig callConfig)<br /> --- End of inner exception stack trace ---<br /> at APIConnectorEngine.Qwc.HttpHelper.MakeRequestWithRetries(eHTTPMethod method, String url, Object postData, Object state, HttpCallConfig callConfig)<br /> at APIConnectorEngine.OAuth.OAuth2.OAuth2APIHelper.RequestWithAccessToken(eHTTPMethod method, String url, String accessToken, Object enclosedEntity, Object state, HttpCallConfig callConfig, TrackingInfo trackingInfo)<br /> at APIConnectorEngine.OAuth.OAuth2.OAuth2APIHelperWithRefreshToken.GetWithRefreshToken(String url, String refresh_token, TrackingInfo trackingInfo)<br /> at GoogleDriveConnector.GoogleDriveConnector.&lt;&gt;c__DisplayClass53_0.&lt;GetWorksheet&gt;b__0()<br /> at APIConnectorInterfaces.HTTP_and_Authorisation.Retry.WebRequestRetrier`1.Run(Func`1 makeRequestFunction)<br /> at GoogleDriveConnector.GoogleDriveConnector.GetWorksheet(RequestPackage r, Inputs inputs)<br /> at GoogleDriveConnector.GoogleDriveConnector.WriteTable(RequestPackage r, String tableName, Inputs i)<br /> at GoogleDriveConnector.GoogleDriveConnector.onRequestTable(RequestPackage r)<br /> at APIConnectorInterfaces.APIConnectorBase.OnRequestTable(RequestPackage requestPackage)<br /> at APIConnectorEngine.Qwc.WebServer.QwcWebServer.ProcessHttpRequest(IInstalledConnectors installedConnectors, IWebRequestAndResponse reqAndResp, IAPIConnectorWrapper wrapper, AdditionalProcessHttpRequestCfg additionalProcessHttpRequestCfg)