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:
http://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:
http://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 variable:
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; 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:
http://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: http://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(); (http://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: "http://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 (http://api.jquery.com/jQuery.ajax), but to save time, I have included the information here:
url: "http://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: http://msdn.microsoft.com/en-us/library/office/fp142380.aspx
For more information about jSON: http://json.org/
For more information about jQuery: http://jquery.com/
Cheers,
Steve.
