7 Replies Latest reply: Nov 23, 2016 3:05 PM by Jackson Whitfield RSS

    Office 365 Sharepoint Connector

    Jackson Whitfield

      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.

        • Re: Office 365 Sharepoint Connector
          Chris Brain

          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
              Jackson Whitfield

              Hey Chris, it is a hosted Office 365.

               

              Thanks

                • Re: Office 365 Sharepoint Connector
                  Steve Dark

                  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
                      Jackson Whitfield

                      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?

                       



                        • Re: Office 365 Sharepoint Connector
                          Steve Dark

                          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:

                           

                          2016-11-23_14-39-41.jpg

                           

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

                           

                          2016-11-23_19-37-57.jpg

                           

                          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!

                           

                          2016-11-23_19-39-24.jpg

                          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:

                           

                          2016-11-23_19-42-09.jpg

                           

                          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