Working with SharePoint list data - OData, REST and JavaScript

Working with SharePoint list data using OData, REST and JavaScript

Recently I’ve been doing a fair amount of work with SharePoint list data using OData and the SharePoint 2013 REST APIs; so I thought I would share some of my experiences with you.

As you are aware, SharePoint lists are far from a new thing. However, they do offer a really flexible method to store a lot of data. Creating data is simple through the OOB user interface and you can find many different ways of filtering, grouping and ordering your data using views.

However, what if you have a completely customised user interface, and need to both create and surface list based data into it? Using the client object model, the new OData and REST functionality in SharePoint 2013 is definitely one way, and this is the focus of this blog post.

Future posts may include different CRUD operations and different entities.

Working with OData and REST in SharePoint 2013

I'm going to assume that you have some understanding of OData and REST. If you are really new to it as a concept then there are plenty of informative articles out there, so we are purely focusing on SharePoint 2013's implementation here.

My first impressions about the way that you construct your RESTful HTTP requests very closely mimics the client object model; so it doesn't take long for you to familiarise yourself with; lets take a look at a few examples:

Accessing a list with the client object model:

List.GetByTitle(Sales)

And with the corresponding REST endpoint:

https://contoso/sites/sales/_api/lists/getbytitle('Sales')

In the example above, you can see that we are requesting a list titled 'Sales', which is what will be returned to us. If we wanted to have the items within the list returned then we would create the following:

https://contoso/sites/sales/_api/lists/getbytitle('Sales')/items

We can go a lot further than this. I have included a few examples below:

Operation	REST Endpoint
Retrieve a single list	/lists/getbytitle('listname')
Retrieve all items in a list	/lists/getbytitle('listname')/items
Retrieve all items in a list and select a specific property	/lists/getbytitle('listname')/items?$select=Title
Filter items in a list, returning only entries that start with 'A'	/lists/getbytitle('listname')/items?$filter=startswith(Title, A) eq true

 

 

 

 

We have briefly covered querying data, so what does this look like in action? In the screenshot below, you can see that we are querying 'all suppliers' in a custom list:

Creating list items in SharePoint 2013 using JavaScript

We are going to move on to creating list items using a custom UI and the CSOM:

Our form is nothing too scary really, we are going to create a simple ‘Supplier Management’ form which will allow our users to enter some basic information about suppliers. Which is a great point really to insert a disclaimer! All of the code and examples provided here are exactly that.
There is no support provided, and if you want to use any of this content then I recommend that it is purely for learning purposes only. None of this has been tested on any of your environments.

Now we have that out of the way; lets take a look at the form:

Next, we will create the JavaScript that will create a new list item when our users click on the 'create' button:

 

function newSupplier(siteUrl) {
    var sName = $('#supplierName').val();
    var sRegion = $('#supplierRegion').val();
    var sProduct = $('#supplierProduct').val();
    var sAcctMgr = $('#supplierAcctMgr').val();

    var clientContext = new SP.ClientContext(siteUrl);

    var oList = clientContext.get_web().get_lists().getByTitle('Suppliers');   
    var itemCreateInfo = new SP.ListItemCreationInformation();

    this.oListItem = oList.addItem(itemCreateInfo);   
    oListItem.set_item('Title', sName);   
    oListItem.set_item('Region', sRegion);
    oListItem.set_item('Product', sProduct);
    oListItem.set_item('AccountManager', sAcctMgr);
    oListItem.update();

    clientContext.load(oListItem);

    clientContext.executeQueryAsync( 
        Function.createDelegate(this, this.onQuerySucceeded),
        Function.createDelegate(this, this.onQueryFailed)
    );  

Walkthrough of the code:

If we take a quick walk through the code above, you can see that we are creating several variables:

  var sName = $('#supplierName').val();
   var sRegion = $('#supplierRegion').val();
   var sProduct = $('#supplierProduct').val();
   var sAcctMgr = $('#supplierAcctMgr').val();

These simply contain the data that our users enter into the <input> elements in the submission form; you can see that we are using jQuery to reference the various input boxes, and the .val(); function to capture the data.

Moving on, we now load our current site into context, and then get the list that we want to create a list item in:

var clientContext = new SP.ClientContext(siteUrl);
  var oList = clientContext.get_web().get_lists().getByTitle('Suppliers');

We next need to use the ListItemCreationInformation() function and state which columns we want to populate:

var itemCreateInfo = new SP.ListItemCreationInformation();
    this.oListItem = oList.addItem(itemCreateInfo);
    oListItem.set_item('Title', sName);
    oListItem.set_item('Region', sRegion);
    oListItem.set_item('Product', sProduct);
    oListItem.set_item('AccountManager', sAcctMgr);

We are passing the data in our variable into the various columns in our supplier list.

Next we need to commit the changes; by using the oListItem.Update(); function.

Now that we have created the list item, we have to consider what happens when the operation succeeds, or fails:

function onQuerySucceeded() {
    alert('Item created: ' + oListItem.get_id());
}

function onQueryFailed(sender, args) {
    alert('Request failed. ' + args.get_message() +
        '\n' + args.get_stackTrace());
}

If you want to learn more about CRUD operations in SharePoint 2013, using the JavaScript OM, then here is an excellent place to start:

https://msdn.microsoft.com/en-us/library/jj163201.aspx

Lets take a look at our form working with a couple of new entries:

Retrieving and rendering SharePoint 2013 list items

Earlier on in this article, we briefly covered querying list data with OData; we saw how to retrieve list items, select specific properties and filter results. In this section we are going to render our Supplier list into a custom UI. We will use jSON and jQuery to do this, along with our RESTful HTTP request.

The first step for us is to ensure that our HTTP request is returning the data that we are expecting:

Our URL: https://contoso/_api/web/lists/GetByTitle('Suppliers')/items

This returns all of the list items in ATOM/XML format:

 

This is great, but we want to work with OData/jSON and not XML. If we start fiddler, we can take a look at the same data, but returned in jSON format instead. In fiddler, you will need to set a filter:

Now when you refresh the page, you will be seeing the jSON returned.

In order to render our content, we will be using jQuery, specifically jQuery.ajax(). If you check out the details for jQuery.ajax(); (https://api.jquery.com/jQuery.ajax/) then you can see that it 'Performs an asynchronous HTTP (ajax) request', which is exactly what we want to do :)

So, in order for us to retrieve our supplier information, we need to create the following code:

<script>
    $.ajax({

        url: "https://contoso/_api/web/lists/GetByTitle('Suppliers')/items",
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        },
        success: function(data){
            $.each(data.d.results, function(index, item){
                $('#oDataSuppliers').append("<li>" + "<h1>" + item.Title + "</h1>" + "<h2>" + item.Region + "</h2>" + "</li>");
            });
        },
        error: function(error){
            alert(JSON.stringify(error));
        }

});

</script>

Lets take a walk through the code above.

There is a really good description of the settings for $.ajax() on the jQuery site (https://api.jquery.com/jQuery.ajax), but to save time, I have included the information here:

url: "https://contoso/_api/web/lists/GetByTitle('Supplier')/items", this is the URL to where we want the send the request; note this is our OData/REST HTTP endpoint.

type: "GET", I am using a HTTP GET request to retrieve the data from the list.

headers: {
"accept": "application/json;odata=verbose",
},

Here we are saying in the request header that we want the server to return jSON data. Remember that by default, SharePoint is going to return XML data..

success: function(data){
            $.each(data.d.results, function(index, item){
                $('#oDataSuppliers').append("<li>" + "<h1>" + item.Title + "</h1>" + "<h2>" + item.Region + "</h2>" + "</li>");
            });
        },

In this block of code, we are stating what we want to do with the data that is being returned. I want to render this content into some HTML that I can then style using CSS. Lets take a closer look at this:

$.each(data.d.results

Here we are using a foreach loop; so for each object that is returned, we want to append the specific properties into HTML; into an existing element with the ID of 'oDataSuppliers'. The HTML that we want to render will basically look like this:

<ul>
    <li>
        <h1> item.Title </h1>
        <h2> item.Region </h2>
    </li>
</ul>

The end result will retrieve the Supplier name and their region, and will append it to an unordered list:

 

Hopefully, through this article you have seen how we can use the client object model to create list items; and by using OData and RESTful HTTP requests, along with jQuery we were able to render our list data into a custom UI.

For more details on REST and OData in SharePoint 2013: https://msdn.microsoft.com/en-us/library/office/fp142380.aspx
For more information about jSON: https://json.org/
For more information about jQuery: https://jquery.com/

Cheers,
Steve.

Comments

• Anonymous
  January 01, 2003
  Will this work with SharePoint Online?

  • Anonymous
    July 21, 2016
    This will work in Sharepoint Online, but the URL needs to be tweaked. You can find the proper URL construction on the MSDN Website: https://msdn.microsoft.com/en-us/library/office/fp142385.aspx . The column name used for filtering/sorting/expanding in the URL is NOT the column label but rather the field ID. To find the field id, navigate to the content editor for the list you want to run a REST query on. Click on the column you want to use. In the URL in your browser, the string after "field=" will be the field name you need to use in your REST query. For example, I have a column named "Legacy Client ID," and the field name is "Client_x0020_ID". If you use single word column labels and you never change them, then the field ID and label will be the same.

• Anonymous
  January 01, 2003
  Interesting!!!

• Anonymous
  October 18, 2013
  You know, it would be nice if microsoft also fully supported taxonomy fields into this equation. As it stands now, all filtering, sorting doesn't seem to work. Intrestingly, neither does een $inlinecount=allpages work on SP. An item count is not being returned from my experience. If anyone might have any ideas on that, that would be great :)

• Anonymous
  December 08, 2013
  Brecht, I found a way to filter on taxonomy fields... www.cleverworkarounds.com/.../how-to-filter-on-a-managed-metadata-column-via-rest-in-sharepoint-2013

• Anonymous
  February 03, 2014
  Hi there, I wrote the script to make REST call to retrieve the data and display in the content editor div tag. The problem I am facing is whenever the page refreshes the REST is keep adding the the content to the div. How can I avoid the REST call on page refresh? Thanks,

• Anonymous
  April 11, 2014
  Hi khushi did you try adding your rest in a html page and upload it to document lib and refer it in content editor webpart

• Anonymous
  April 15, 2014
  you can clear the div before appending into it.. so the next time it refresh..it will first clear the div and append it..
  

• Anonymous
  September 09, 2014
  Hello Steve, I need to copy list items from one list to another. Have you discussed the solution anywhere else? Please let me know. Appreciate your time.
  
  Awesome Post.

• Anonymous
  September 15, 2014
  Hi Steve. Is there a way to add items to a list using the client object model?
  I can get the list name using the GetByTitle() call, but can't figure out how to actually add records.

• Anonymous
  October 01, 2014
  Hello Steve, Im trying to get data from listdata, but i dont realy understand how to get the json data and not the xml data ...I dont use fiddler maybe there is another way ?

• Anonymous
  December 12, 2014
  Can anyone tell me how can i set my web client and hrrp listening port

• Anonymous
  January 30, 2015
  One of the most widely used features of SharePoint Online is the ability to create and easily store data

• Anonymous
  February 24, 2015
  function getProductID

• Anonymous
  May 10, 2015
  '$,ajax({' is not correct. It should be $.ajax({

• Anonymous
  May 20, 2015
  Someone try this code?

• Anonymous
  August 05, 2015
  i try it, it works fine for me!!

• Anonymous
  August 05, 2015
  i try it, it works fine for me!!

• Anonymous
  November 02, 2015
  I am having problems using the SP List Adapters because of the number of fields.
  
  I found this on Google, but the document is not on the site.
  
  Field limit for SharePoint Source and Destinations for SSIS
  www.sharepointsteve.com/.../field-limit-for-sharepoint-source-and-destinati...
  Jun 9, 2010 - Field limit for SharePoint Source and Destinations for SSIS ... I was importing about 15 lists into SQL Server to do someone reporting on the data, and 14 of ... Not much the adapter can do about this, as only you know the data.

• Anonymous
  May 04, 2016
  Thank you! Thank you! THANK YOU!

• Anonymous
  June 02, 2016
  hiii,I want to achieve the same but with the share point hosted list,means i want to create list inside share point hosted app and wish to perform insertion/editing and deletion operation on same list.the way you have explained a applied that one but unfortunately that is not working for me with share point hosted list,it is fine with un hosted list,so how can i achieve my goal??can any one help me here??

• Anonymous
  November 15, 2016
  Nice article, I keep getting 'unable to get property results' or undefined or null reference', however, I can access the API and see the data in XML format when I access it via the browser. What would cause this error?

• Anonymous
  March 01, 2017
  Great post.JUst wondering how to add and update to list items without contribute access ( user will have read or restricted read access ) , list being sensitive and we don't want to give all users write access.Many thanksThiru

• Anonymous
  May 11, 2017
  When I use this technique for web pages (from Site Pages or Pages libraries) with the getfilebyserverrelativeurl endpoint, I end up getting the ASP.NET markup of the aspx pages. Is there an endpoint where I can get the actual rendered html out of those pages using the REST API?

• Anonymous
  June 10, 2017
  Thanks ! thats interesting