How to Fetch a Sharepoint 2013 List using REST interface

[#1]

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/

[#2]

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 .

[#3]

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.

[#4]

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.

[suniljain]

please also enable active scripting option .

[#6]

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&$se...] (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.

[#7]

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

[#8]

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)

[roelvandeven]

Hello Vagner,

Did you get this to work?

[Karthik3]

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

[vengadeshpalani]

Please Share sample Screenshot