topic Re: Office 365 Sharepoint Connector in Connectivity & Data Prep

Office 365 Sharepoint Connector

Anonymous — Fri, 02 Apr 2021 07:53:41 GMT

Has anybody been able to successfully use the new Office 365 Sharepoint Connector?

The example on the qvsource wiki looks pretty straightforward:

http://wiki.qvsource.com/Office-365-Connector-For-QlikView-And-Qlik-Sense.ashx

However, the example uses Microsoft Azure which we don't have. In the example Azure is used to change settings and generate the Client Id.

In following this Microsoft article:

We found that a Client Id could be generated by going to:

http://<SharePointWebsite>/_layouts/15/AppRegNew.aspx

We plugged the newly generated id into the Connector but could not authenticate:

"There was an error running the table (No refresh_token found for Qlik Office 365 Sharepoint Connector)"

I feel that the Client Id is accurate. Perhaps the Base URL wrong? We tried the Sharepoint address for our company and also the link where the lists reside and neither would work. I'm wondering if we have the wrong URL or if by not going through Azure, there is an additional setting on the Sharepoint side that we are missing.

Any feedback would be greatly appreciated, thanks in advance.

Re: Office 365 Sharepoint Connector

chrisbrain — Wed, 23 Nov 2016 13:02:01 GMT

Hi - Are you trying to connect to hosted Office 365 or to a Sharepoint site hosted on premise or by another provider?

Re: Office 365 Sharepoint Connector

Anonymous — Wed, 23 Nov 2016 15:24:41 GMT

Hey Chris, it is a hosted Office 365.

Thanks

Re: Office 365 Sharepoint Connector

stevedark — Wed, 23 Nov 2016 18:21:22 GMT

Are you able to publicly share the sheet you are trying to connect to (with a impossible to guess key) or is the nature of the data such that you can't do this?

If it's the former you can get a link which will be something like this:

https://onedrive.live.com/view.aspx?resid=14E53B3A096CD7FF!658&ithint=file%2cxlsx&app=Excel&authkey=!AJuNySYjcmYH2NY

You then simply have to change view.aspx to download.aspx and you can connect to it in QlikView directly, without a connector:

Population:

LOAD Rank,

Country,

Population

FROM

[https://onedrive.live.com/download.aspx?resid=14E53B3A096CD7FF!658&ithint=file%2cxlsx&app=Excel&authkey=!AJuNySYjcmYH2NY]

(ooxml, embedded labels, table is Sheet1);

The Qlik Web Connector requires that you set up a ClientID for authentication, and I've not found whether you can do that with the standard Office365, or whether you need something hosted on Azure.

Steve

Re: Office 365 Sharepoint Connector

Anonymous — Wed, 23 Nov 2016 19:15:42 GMT

Thanks for the suggestion Steve, that could be a valid workaround for us.

Would still like to get the connector figured out for future reference.

"The Qlik Web Connector requires that you set up a ClientID for authentication, and I've not found whether you can do that with the standard Office365, or whether you need something hosted on Azure."

That's where we are stuck as well. We were able to generate a Client Id using the method mentioned in the following Microsoft article:

http://<SharePointWebsite>/_layouts/15/AppRegNew.aspx

With the Client Id successfully generated thought we would be able to connect but no such luck. I guess I don't understand the need for Azure, shouldn't the connector be able to authenticate directly to Sharepoint with a valid client id?

Re: Office 365 Sharepoint Connector

stevedark — Wed, 23 Nov 2016 19:49:12 GMT

I'm getting quite far with doing this for Office 365, but not all of the way.

Following the instructions for Registering a OneDrive app here:

https://dev.onedrive.com/app-registration.htm

I go to the link and enter an App Name and then Add An App, and I get this information back:

From here I plumb the clientId and base URL into Qlik Web Connectors and click Authenticate:

I'm not sure if that is the right Base URL - but it is where my spreadsheets appear.

I then get the standard Microsoft Login pages, and finally arrive at a page with an authorisation code - all looking good!

When clicking Authenticate, after copying the key back into Qlik Web Connectors, I get the following error:

19:34:41 An error occurred while trying to authenticate. (status code = 500, The remote server returned an error: (401) Unauthorized., HttpStatusCode: Unauthorized, StatusCode: 401, StatusDescription: Unauthorized, Message: The remote server returned an error: (401) Unauthorized., ResponseBody: {"error":"invalid_client","error_description":"AADSTS70002: The request body must contain the following parameter: 'client_secret or client_assertion'.\r\nTrace ID: 00221610-d5f3-4735-9362-fca35e989ffb\r\nCorrelation ID: 7cbf8d71-be2e-489b-aeec-d125cdb8242e\r\nTimestamp: 2016-11-23 19:34:43Z","error_codes":[70002],"timestamp":"2016-11-23 19:34:43Z","trace_id":"00221610-d5f3-4735-9362-fca35e989ffb","correlation_id":"7cbf8d71-be2e-489b-aeec-d125cdb8242e"})

It looks like the API that is doing the authorisation is requiring a bit more information.

I've been back to the Application Registration Portal with Microsoft and generated passwords and private keys, but there is nowhere to plumb these in in Qlik Web Connectors that I can see:

Obviously the connector is designed to work with Sharepoint online, and whilst it looks tantilisingly close to working with OneDrive it may be that the two things won't play nicely.

There is however a OneDrive connector, which I will give a go to now. It could just be a case of using the right connector for the right data!

chrisbrain‌ and darrenball‌ I am happy to try things out to help with testing these Beta connectors.

Cheers,
Steve

Re: Office 365 Sharepoint Connector

stevedark — Wed, 23 Nov 2016 19:55:35 GMT

Well, that was much easier! Just authenticated with OneDrive and created a binary stream of an Excel file and then this query loaded from the Excel file I posted above with no problems:

LOAD Rank,

Country,

Population

FROM

[http://qi-steve:5555/data?connectorID=OneDriveConnector&table=GetRawFileAsBinary&fileId=xxxxxxxxxxxxxxxxx&appID=]

(ooxml, embedded labels, table is Sheet1);

Not sure if data in your SharePoint can be exposed through a users OneDrive - that could make things much much easier.

Steve

Re: Office 365 Sharepoint Connector

Anonymous — Wed, 23 Nov 2016 20:05:30 GMT

Cool - we are tying to pull Sharepoint lists into Qlik. Not sure on the the how but if we could generate a similar link that points directly to the Sharepoint data then I could see that working.

Re: Office 365 Sharepoint Connector

Anonymous — Fri, 03 Aug 2018 12:25:03 GMT

Where can I download this connector?

Re: Office 365 Sharepoint Connector

stevedark — Sat, 04 Aug 2018 10:22:07 GMT

If your Qlik ID is associated with a customer account then you can go to the download site here:

https://demo.qlik.com/download/

If you are a customer and you get the option to only download Sense Desktop then you need to get your Community login associated with your customer account. Speak to your Qlik partner about this and they will pass the request onto Qoncierge.

If you are not an existing customer you can download from here:

https://da3hntz84uekx.cloudfront.net/WebConnectors/2.44.5/1/_MSI/QlikWebConnectorsJune2018Patch1.zip

As at now the Office 365 Sharepoint connector is in beta, which means you can use it free of charge, but it may not become production, and if it does a price tag will be attached for QlikView.

If you have Sense Professional and Analyser licences (as opposed to the old tokens) then I believe QWC is included with your Sense licences - but check this with your Qlik Partner.

Hope that helps.

Steve

Re: Office 365 Sharepoint Connector

Razej — Mon, 20 May 2019 06:41:16 GMT

Hi everyone,

I have problem with the QlikView-Sharepoint connector on Azure server.

I am missing something but I don't know what. Every time when I try to generate Authentication Code, I get this message:

Re: Office 365 Sharepoint Connector

gracehu2013 — Tue, 29 Sep 2020 15:13:44 GMT

Hello, Chrisbrain:

Will you please let me know if qlikview web connector could support Share point 365 online through Azure?

Thanks,

Grace Hu

Re: Office 365 Sharepoint Connector

chrisbrain — Tue, 29 Sep 2020 19:44:27 GMT

Hi Grace - Can you clarify what you mean by 'through Azure'?

Re: Office 365 Sharepoint Connector

gracehu2013 — Mon, 05 Oct 2020 17:49:20 GMT

Hi, Chris:

The sharepoint 365 online which we're using is hosted in the cloud by Microsoft and Microsoft cloud platform is Azure.

Thanks,

Grace Hu

Re: Office 365 Sharepoint Connector

chrisbrain — Tue, 06 Oct 2020 08:01:53 GMT

It's designed to work with Office 365 Online so yes it should work.

Re: Office 365 Sharepoint Connector

stevedark — Tue, 06 Oct 2020 08:11:54 GMT

Just to clarify though, @chrisbrain , I believe it is still the case that the connector can read from lists in SharePoint, but the SharePoint filesystem connector is not there to read from a QVD or Excel file stored in SharePoint in the same way as if it was on a file share on premise?

Thanks,
Steve

Re: Office 365 Sharepoint Connector

chrisbrain — Tue, 06 Oct 2020 08:27:13 GMT

Yes - Currently the Office 365 Connector integrated into the Qlik Hosted/Saas version of Sense does not allow you to load e.g. Excel, QVD files etc. stored in Office 365 but the version of the connector in the 'Qlik Web Connectors Standalone' version (for on premise Sense and QlikView) does.