How to Connect QlikSense to a 2013 SharePoint List

[wdchristensen]

** Prerequisite – Install Qlik Sense REST Connector **

1) Get guid from SharePoint list (Maybe your SP Administrator can provide this?)

•    Navigate to the aspx page that contains the list:

     Example page: http://sharepoint/company/home/folder/project/new/newer/_formats/001/default.aspx

• Click on the Site Contents link

• Click on Settings

• Doesn’t matter if you actually have permission to the object the Access Denied page has the information you need! Look at the URL and search the string from RIGHT to LEFT and find the first instance of the word “List” and copy everything from that point to the end of the URL. 

Example url: http://sharepoint/company/home/folder/project/new/newer/_formats/001/AccessDenied.aspx?Source=http%3...List%3DZY98XW76%252DVU54%252DTS32%252DRQ19%252DPO87NM65LK43

• The %3D in the url encoding just means the equal sign (=) so it can be ignored. Change all “%2D” to “-“ (I used notepad)

• Wrap the result in curly brackets and you have the List ID

Example GUID List ID = {ZY98XW76-VU54-TS32-RQ19-PO87NM65LK43}

2) How to view xml code of a list view

• Build URL: http://{0}/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={1}

          {0} – The URL to your site.  This could be the root web or a child site.

          {1} – The GUID for your list.

Example URL for viewing XML in browser:

http://sharepoint/company/home/folder/project/new/newer/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&Ro...{ZY98XW76%252DVU54%252DTS32%252DRQ19%252DPO87NM65LK43}

• Copy and paste the new url into your favorite browser and you should see a page of XLM. If you can't view the XML in a browser you won't be able to view it in QLIK so make sure you can see the information before continuing.

3) Open your QlikSense application and create a new connection using the "Qlik Rest Connector".

4) Create new rest connection.

• Enter the url you created in step 2.
• Set the authentication and create a meaningful name (cnSharePointExampe)
• Fill out the remaining options as follows and test the connection

5) You can now load data (similar to any other data load process)

[Anonymous]

Hi

I am trying to accomplish the above but it seems that your version uses an old version of Qlik Desktop since mine has the REST Connector included and the authentication section has more options

Can you please advise which authentication method I should use (NTLM , Basic , Digest , ...) . I am using a AD to connect to sharePoint.

BTW I tried all of them and they call return 403 Forbidden although I plugged in the SP administrator account

[wdchristensen]

I wrote the documentation above based on Qlik Sense 3.2. After I created the documentation we "upgraded" our development to Feb 2018 but unfortunately I can no longer create apps or make data connections so it is hard to update the documentation. We have a support case open for that issue but in the meantime I can make a guess as to the issue.

I suspect your primary issue is building out the URL. Are you able to view the XML in your brower (step 2)? If not, get that part working before you try to make a connection to the sharepoint data.

Do you get the 403 error from Qlik or from a browser when navigating to the derived XML url?

[wdchristensen]

I was able to create connection with the "Window NTLS" option but I had to supply a login which is not what you. Since I was not able to get Windows Authentication to work on Feb 2018, I edited the connection string in the QMC. This produced an error during the load that the "connection format is obsolete".

[Anonymous]

Did u get it to work using NTLS ? if yes please how did you supply the username and password (format I mean)

[wdchristensen]

[wdchristensen]

Can you at view the XML file in your browser?

[Anonymous]

I am still getting the 403 Error. I am plugging the admin username and password !. and Using the browser when I push the link I am getting download .DLL file I click yes and I open the DLL as XML it opens Up

Here is my URL : https://sharepoint.butec.com.lb/sites/HR/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={...

And a sample when I open the XML file

[wdchristensen]

Your XML looks good but when I enter my URL, my XML renders immediately. I don’t have to click “yes”. Can send a screenshot of that dialog box? Were you on your personal computer when you took the XML screenshot or were you remoted into the Qlik application server? Might also check and make sure your SharePoint site doesn’t have any IP deny rules blocking the Qlik Application Server? Are you using Qlik Sense Desktop or Enterprise? 

[Anonymous]

I am using Qlik Sense Desktop but I have Qlik Sense Server. I was on my personal computer. I am able to see the sharepoint from my browser and administer it normally .. So I suppose there is no permission access thing