topic Re: How to Fetch a Sharepoint 2013 List using REST interface in QlikView

How to Fetch a Sharepoint 2013 List using REST interface

Wed, 13 Nov 2013 20:52:19 GMT

In earlier versions of SharePoint most of the Qlikview developers used Windows SharePoint Services Remote Procedure Call (RPC) methods to fetch data from a share point list.

http://Server_Name/[sites/][Site_Name/]_vti_bin/owssvr.dll?Cmd=Display&List=GUID&XMLDATA=TRUE

This method of extraction always required a GUID to fetch a list or a specific view. This posed difficulties in automating some of the extraction tasks.

With the launch of Sharepoint 2013 Microsoft provided REST(Representational State Transfer) interface which is a data service that allows you to use construct HTTP requests to query SharePoint list data. The main advantage here is that WCF Data Services uses the OData protocol for fetching the data. The data is returned using a well known XML format called ATOM. As a Qlikview developer you don't have to learn entire technical aspects of this new feature. All you have to do is to understand some of the very useful methods.

Some of the main advantages are given below.

1. GUID is no longer required to fetch a list. Now we can fetch a list by the Title

Use the following format

http://server/site/_api/web/lists/getbytitle(‘Title’)/Items

2. We can fetch meta data about the list.

http://server/site/_api/web/lists/getbytitle(‘Title’) will provide a XML with Metadata about list

For example

[content/properties/ItemCount] will provide you the number of records in the list

3. OData protocol have much more powerful methods and Going forward ,Microsoft is positioning it as a preferred method of interaction.

Following are some of the available options

Option	Description
$expand	Directs that related records should be retrieved in the record or collection being retrieved.
$filter	Specifies an expression or function that must evaluate to ‘true’ for a record to be returned in the collection.
$orderby	Determines what values are used to order a collection of records.
$select	Specifies a sub set of properties to return.
$skip	Sets the number of records to skip before it retrieves records in a collection.
$top	Determines the maximum number of records to return.

Sample Script for Fetching data from Sharepoint 2013 List using ODate protocol

//Get number of Records from the list

ListCount:

LOAD

Num([content/properties/ItemCount]) as ItemCount

FROM [http://server/site/_api/web/lists/getbytitle(‘Title’) ] (XmlSimple, Table is [entry]);

//fetch the Row count

Let vListCount= Peek('ItemCount',0,'ListCount') ;

//Get List

//Please remember to use top method. If the top method with number of total rows is omitted the fetch might not return all the

//records in the share point list

List:

LOAD

[content/properties/ID] as ID,

[content/properties/Title] as Title,

[content/properties/Modified] as Modified,

[content/properties/Created] as Created

FROM [http://server/site/_api/web/lists/getbytitle(‘Title’)/Items?$Top=$(vListCount)] (XmlSimple, Table is [feed/entry]);

"I tried to create this as a blog ,but apparently couldn't publish it to a place where every one can access. Sorry for posting it in multiple places. "

Hope this will help you in your quest for the next Qlikview solution. This method enabled us to automate fetching of lists with the same name from different share-point sites.

Wishing you all the best of up coming holiday season from your friends in Michigan

Pauljin Kochupyloth | Sr. BI Consultant

WIT Inc :"HARNESS THE POWER OF BI" http://www.witinc.com/

Re: How to Fetch a Sharepoint 2013 List using REST interface

Qvmaster2019 — Thu, 20 Jun 2019 15:04:40 GMT

Re: How to Fetch a Sharepoint 2013 List using REST interface

Wed, 12 Feb 2014 19:11:33 GMT

I have tried your method above with no luck. Using sharepoint 2013 and QV 11 SR5

server is uf.hide.com

site = prod

list name = ProductionTanks

Is this the right code:

FROM [http://uf.hide.com/prod/_api/web/lists/getbytitle(‘ProductionTanks’) ] (XmlSimple, Table is [entry]);

I can get unknown MSXML errro

Re: How to Fetch a Sharepoint 2013 List using REST interface

Wed, 12 Feb 2014 20:12:20 GMT

I encountered the same error when I was deploying my application to server. So the following might fix the issue.

This error message is usually found where there is security access issues.

1.. Check whether the ID by which you are executing the qlikview publisher has proper sharepoint access.

Log into the server with the user id and password . Launch the sharepoint server . If you are asked to enter user name and password chances are that Qlikview is not getting the user authentication info.

2. If your id has access , you need to do one more configuration in Internet explorer in the server. Apparently microsoft is storing this setting as part of internet explorer security settings.

Go to internet explorer > internet options> Security .

Click on trusted sites and add the sharepoint site into list of custom sites .

Then click on the 'Custom level' button under security level area.

This will open up a list of options . Scroll all the way do and find option

'User authentication'

'Logon'

By default it will be selected to 'Automatic logon only in intranet zone' change it to

'Automatic logon with current usename and password'

This is what I have done to get things working. Ironically this falls in between admin and developer and sometimes none of us know what is going on.

We have to do it every server using the ID. For example if you move the app to prod , you have to do it there too. I guess you get the idea,

Try it out and let me know whether it helped you..

Re: How to Fetch a Sharepoint 2013 List using REST interface

Qvmaster2019 — Thu, 20 Jun 2019 15:21:32 GMT

Re: How to Fetch a Sharepoint 2013 List using REST interface

Thu, 13 Feb 2014 15:43:57 GMT

Our sharepoint is not hosted on the server. You might be right about the cloud hosting. There might be something which is prohibiting qlikview user from reaching the sharepoint.

Re: How to Fetch a Sharepoint 2013 List using REST interface

Fri, 14 Feb 2014 17:06:38 GMT

Hi pauljin

Thank you for the information. I found something similar to this and it also worked. Instead of trusted sites, we go to Local Intranet, then Advanced Settings, then add the SP site to trusted site and had to check the box for secure HTML.. and it worked!

Re: How to Fetch a Sharepoint 2013 List using REST interface

Qvmaster2019 — Thu, 20 Jun 2019 15:22:09 GMT

Re: How to Fetch a Sharepoint 2013 List using REST interface

Sun, 16 Feb 2014 18:37:25 GMT

QMC only. works fine from desktop. but all is working fine now

Re: How to Fetch a Sharepoint 2013 List using REST interface

Thu, 17 Apr 2014 07:42:09 GMT

Hi pauljin,

thank you for sharing this information. It works fine for me as long as I only use the fields ID, title, modified and created. As soon as I try to read details from fields like description with multiple lines I get no values. How can I read from fields with blank in the field description like 'Assignd to' and how can I extract date from the field 'Created by' or 'Modified by'? Thank you in advance.

Kind regards,

Harald Martini

Re: How to Fetch a Sharepoint 2013 List using REST interface

Fri, 18 Apr 2014 13:38:10 GMT

Hi Harald,

The internal names of the files are different from the description by which they are shown.

You can access the entire list of fields by following query.

http://server/site/_api/web/lists/getbytitle(‘Title’)/fields

Sometimes the sharepoint won't give you the fields that you want by default. For example if you need EncodedAbsUrl

you have to specifically ask for in the query as follows.

http://server/site/_api/web/lists/getbytitle(‘Title’)/Items?$select=EncodedAbsUrl,*

Read about rest service in Sharepoint 2013. It will provide a lot of info. Qlilkview is just making use of the rest by pulling a xml .

Re: How to Fetch a Sharepoint 2013 List using REST interface

Mon, 28 Apr 2014 06:48:22 GMT

Hi Pauljin,

I figured out how to do it and now it works fine for me. The only thing is, that there seems no possibility to select fields from the dialogue box. I have to delete the unwanted fields in the query manually. Thanks for your support.

Re: How to Fetch a Sharepoint 2013 List using REST interface

Thu, 12 Jun 2014 05:49:21 GMT

Hi Debbie , you succeed to load data from QVdesktop? How did you know the 'list name'? and where should I change in the standard link ,

FROM [http://server/site/_api/web/lists/getbytitle(‘Title’)/Items?$Top=$(vListCount)] (XmlSimple, Table is [feed/entry]);

Thank you very much.

Re: How to Fetch a Sharepoint 2013 List using REST interface

suniljain — Mon, 22 Sep 2014 11:51:42 GMT

please also enable active scripting option .

Re: How to Fetch a Sharepoint 2013 List using REST interface

Mon, 09 Mar 2015 11:37:44 GMT

I am using the REST service successfully but have run in to a bit of a snag. I can get all the data from the primary list without issue. But when I try to select a data type "nested" data, that data is not returned. I do not get an error, but the field is simply blank. Below is a sample dealing specifically with a field called AssignedTo which is a person reference field. The bolded field (AssignedTo/Title) is coming back blank.

CorrectiveActions:

LOAD

[content/properties/ID] as [%%Key_CorrectiveActionId],

[content/properties/Solution] as Solution,

[content/properties/Problem] as Problem,

[content/properties/Resolved] as Resolved,

[content/properties/LPA_QuestionId] as [%%Key_LPA_QuestionId],

[content/properties/AssignedTo/Title] as [CorrectiveActionAssignee]

FROM [http://[MYSHAREPOINTLOCATION]/_api/web/lists/getbytitle('Corrective%20Actions')/items?$top=10000&$select=Id,Solution,LPA_QuestionId,Problem,Resolved,AssignedTo/Title&$expand=AssignedTo] (XmlSimple, Table is [feed/entry]);

This query returns everything I want (Solution, Problem, Resolved, etc.) But the CorrectiveActionAssignee field is coming back blank. If I take the http query and paste it into my browser, all the data is coming back as expected including the person reference with Title (name). Do you have any idea why?

I've tried [content/properties/AssignedTo/properties/Title] and that doesn't work either.

Re: How to Fetch a Sharepoint 2013 List using REST interface

Thu, 30 Apr 2015 13:20:03 GMT

Hi Steve,

I'm facing exactly the same problem. I can access to all the data exept the one in expand. Returns blank as you explained.

Do you know how to solve this ?

Regards CHUNG Fabien

Re: How to Fetch a Sharepoint 2013 List using REST interface

Mon, 18 May 2015 19:24:09 GMT

How should I treat access denied error?

I'm trying to get data from o365/SP and this error appears:

<m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">

<m:code>-2147024891, System.UnauthorizedAccessException</m:code>

<m:message xml:lang="en-US">

Access denied. You do not have permission to perform this action or access this resource.

</m:message>

</m:error>

I'm trying to load from QlikView for Windows in my laptop, accessing SharePoint at Office 365 (which my user have access)

Re: How to Fetch a Sharepoint 2013 List using REST interface

roelvandeven — Thu, 04 Feb 2016 10:44:22 GMT

Hello Vagner,

Did you get this to work?

Re: How to Fetch a Sharepoint 2013 List using REST interface

Karthik3 — Wed, 30 Mar 2016 10:22:56 GMT

How the REST Qlikview Connector deal with recurring Calendar events in Sharepoint?

Re: How to Fetch a Sharepoint 2013 List using REST interface

vengadeshpalani — Thu, 27 Oct 2016 06:59:51 GMT

Please Share sample Screenshot