Office 365 Sharepoint Connector

[Anonymous]

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:

Register SharePoint Add-ins 2013

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.

[chrisbrain]

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

[Anonymous]

Hey Chris, it is a hosted Office 365.

Thanks

[stevedark]

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

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&auth...]

(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

[Anonymous]

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:

Register SharePoint Add-ins 2013

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?

[stevedark]

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

[stevedark]

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=xxxxxxxxxxxx...]

(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

[Anonymous]

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. 

[Anonymous]

Where can I download this connector?

[stevedark]

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