CRUD Operation to List Using SharePoint 2013 Rest API

• Download PowerShell script for creating list

Introduction

The goal of this document is to provide a complete idea about CRUD (create, read, update and delete) operation in a list of SharePoint 2013 using REST API. The target audiences must know OData (Open Data Protocol is an OASIS standard that defines the best practice for building and consuming RESTful APIs).

Initial Setup

For making things easy, please consider we have the following two lists (SpTutorial & SpTutorialParent) where we will perform CRUD operation.

SpTutorial

In this list, I have tried to cover all types of columns so that we can see how to get and set values to those columns.

SpTutorialParent

It is used in SpTutorial as a lookup column so that we can see how to get and set value to a lookup column.

Note: Please check the attachment to have PowerShell Script to create these lists.

Tools needed

All examples are given using jQuery.ajax but you can use any REST Client like Fiddler, Postman or Advanced REST Client and so on. These REST Clients have some problems. They do not work seamlessly with SharePoint. That's why I made my own SharePoint REST Client.

I believe you will be happy using this REST Client for testing/exploring REST API of SharePoint. Find the documentation of it over here.

REST in SharePoint 2013

REST service for list was first introduced in SharePoint 2010. It was under the end point /_vti_bin/listdata.svc, and it still works in SharePoint 2013. SharePoint 2013 introduces another endpoint /_api/web/lists, and which is much more powerful than in SharePoint 2010. The main advantage of REST in SharePoint 2013 is: we can access data by using any technology that supports REST web request and Open Data Protocol (OData) syntax. That means you can do everything just making HTTP requests to the dedicated endpoints. Available HTTP methods are GET, POST, PUT, MERGE, and PATCH. The supported data format is ATOM (XML based) or JSON. 

READ: HTTP GET method is used for any kinds of read operation.

CREATE: Any kind of create operation like list, list item, site and so on maps to the HTTP POST method. You have to specify the data in request body and that’s all. For non-required columns, if you do not specify the values, then they will be set to their default values. Another important thing is: you cannot set value to the read-only fields. If you do so, then you will get an exception.

UPDATE: For updating existing SharePoint 2013 objects, there are three HTTP methods like PUT, PATCH and MERGE available. The recommended methods are PATCH and MERGE. PUT requires the entire huge object to perform update operation. Let's say we have a list named EMPLOYEE and it has 100+ fields and we want to update EmployeeName field only. In this case, if we use PUT method, we must have to specify the value of others fields along with EmployeeName field. But PATCH and MERGE are very easy. We just have to specify the value of EmployeeName filed only.

DELETE: HTTP DELETE method is used to delete any objects in SharePoint 2013

For accessing SharePoint resources by using REST API, at first we have to find the appropriate endpoints. The following table demonstrates the endpoints associated with CRUD operation in a list.

URL endpoint	Description	Supported HTTP Method
/_api/Web/Lists	Retrieving all lists in a site and adding new lists	GET, POST
/_api/Web/Lists/GetByTitle('listname')	Getting a list details by its title and updating it as well. If anyone changes your list title, your code will break.	GET, POST
/_api/Web/Lists(guid'guid id of your list')	Same as above but changing list title will not affect the code.	GET, POST
/_api/Web/Lists/GetByTitle(' listname ')/Fields	Retrieving all fields associated with a list and add new fields	GET, POST
/_api/Web/Lists/GetByTitle('listname')/ Fields/GetByTitle('fieldname')	Getting details of a field, modifying and deleting it.	GET, PUT, PATCH, MERGE, DELETE
/_api/Web/Lists/GetByTitle('listname') /Items	Retrieving all items in a list and adding new items	GET, POST
/_api/web/lists/GetByTitle('listname') /GetItemById(itemId)	This endpoint can be used to get, update and delete a single item.	GET, PUT, PATCH, MERGE, DELETE

Hope you are done with creating the above lists as mentioned before. Now come to the real examples how we can use it in practical life. Accessing REST endpoints from client side, there are so many ways but the approach is almost same everywhere. In this article, jQuery($.ajax) will be used to access all endpoints.

GET Items From List

At first, create our method to get items from a particular list. If we use $.ajax, it should look like the following:

function getItems(url) {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + url,
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        },
        success: function (data) {
            console.log(data.d.results);
        },
        error: function (error) {
            alert(JSON.stringify(error));
        }
    });
}

In the above code, _spPageContextInfo.webAbsoluteUrl may be new to you. Actually, it returns the current site url and it’s the preferred way rather typing it hard coded. Now it’s time to constructs some urls and call the above method.

Getting All Items from SpTutorial

If we go through the REST endpoints table again, the endpoint (constructed url) should look like the following:

var urlForAllItems = "/_api/Web/Lists/GetByTitle('SpTutorial')/Items";

Call the method getItems(urlForAllItems);

In the data.d.results, you will find fields internal names as object’s property. In the above example, we will get only the Id of Lookup and Person type column. But we need more information about these columns in our JSON results. To nail this, we have to learn some OData query string operators.

$select specifies which fields to return in JSON results.

$expand helps to retrieve information from Lookup columns.

Now if we re-write the urlForAllItems, it should look like the following:

var urlForAllItems = 
               "/_api/Web/Lists/GetByTitle('SpTutorial')/Items?"+
               "$select=ID,Title,SpMultiline,SpChoice,
               SpNumber,SpCurrency,SpDateTime,SpCheckBox,SpUrl,"+
                "SpPerson/Name,SpPerson/Title,SpLookup/Title, SpLookup/ID" +
                "&$expand=SpLookup,SpPerson";

To use $expand along with $select, you have to specify the column names in $select just what I did in the above like SpLookup/Title, SpLookup/ID.

$filter specifies which items to return. If I want to get the items where Title of SpTutorial equals to‘first tutorial’ and ID of SpTutorialParent equals to 1, the URL should look like the following:

var urlForFilteredItems = 
               "/_api/Web/Lists/GetByTitle('SpTutorial')/Items?"+
               "$select=ID,Title,SpMultiline,SpChoice,SpNumber,
               SpCurrency,SpDateTime,SpCheckBox,SpUrl,"+
               "SpPerson/Name,SpPerson/Title,SpLookup/Title,SpLookup/ID"+
               "&$expand=SpLookup,SpPerson&$filter=Title eq 
               'first tutorial' and SpLookup/ID eq 1";

You may notice that I have used a query operator like ‘eq’ in above URL. Now let’s see what are the other query operators available.

Numeric	String	Date Time functions
Lt (less than)	startsWith (if starts with some string value)	day()
Le (less than or equal)	substringof ( if contains any sub string)	month()
Gt (greater than)		year()
Ge (greater than or equal)		hour()
Eq (equal to)	Eq	minute()
Ne (not equal to)	Ne	second()

Note: Unfortunately, date time functions do not work with new style (URL) of SharePoint 2013. But there is a hope we can do it like SharePoint 2010 style.

var filterByMonth = "/_vti_bin/listdata.svc/SpTutorial?$filter=month(SpDateTime) eq 6";

$orderby is used to sort items. Multiples fields are allowed separate by comma. Ascending or descending order can be specified just by appending the asc or desc keyword to query.

var urlForOrderBy = "/_api/Web/Lists/GetByTitle('SpTutorial')/Items?" +
    "$select=ID,Title,SpMultiline,SpChoice,
    SpNumber,SpCurrency,SpDateTime,SpCheckBox,SpUrl," +
    "SpPerson/Name,SpPerson/Title,SpLookup/Title,SpLookup/ID" +
    "&$expand=SpLookup,SpPerson&$orderby=ID desc";

$top is used to apply paging in items.

var urlForPaging = "/_api/Web/Lists/GetByTitle
('SpTutorial')/Items?$top=2";

Adding New Item

In this case, our HTTP method will be POST. So write a method for it.

function addNewItem(url, data) {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + url,
        type: "POST",
        headers: {
            "accept": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "content-Type": "application/json;odata=verbose"
        },
        data: JSON.stringify(data),
        success: function (data) {
            console.log(data);
        },
        error: function (error) {
            alert(JSON.stringify(error));
        }
    });
}

In header, you have to specify the value of X-RequestDigest. It’s a hidden field inside the page, you can get its value by the above mentioned way ($("#__REQUESTDIGEST").val()). But sometimes, it does not work. So the appropriate approach is to get it from /_api/contextinfo. For this, you have to send a HTTP POST request to this URL (_api/contextinfo) and it will return X-RequestDigest value (in the JSON result, its name should be FormDigestValue).

URL and request body will be like the following for adding new item in list.

var addNewItemUrl = "/_api/Web/Lists/GetByTitle('SpTutorial')/Items"; 

var data = {
    __metadata: { 'type': 'SP.Data.SpTutorialListItem' },
    Title: 'Some title',
    SpMultiline: 'Put here some multiline text. 
    You can add here some rich text also',
    SpChoice: 'Choice 3',
    SpNumber: 5,
    SpCurrency: 34,
    SpDateTime: new Date().toISOString(),
    SpCheckBox: true,
    SpUrl: {
        __metadata: { "type": "SP.FieldUrlValue" },
        Url: "http://test.com",
        Description: "Url Description"
    },
    SpPersonId: 3,
    SpLookupId: 2
};

Note: Properties of data are the internal names of the fields. We can get it from following URL by making a HTTP GET request.

var urlForFieldsInternalName = "/_api/Web/Lists/GetByTitle('SpTutorial')/
Fields?$select=Title,InternalName&$filter=ReadOnlyField eq false";

Type	Value
Single line of text	String
Multiple lines of text	Multiple lines can be added here also rich text
Choice	String but it must come from choices available in the list.
Number	Integer or double
Currency	Like number
Date and Time	String but it must be in ISOString format
Lookup	Integer and must be the ID of Lookup item
Yes/No	true or false
Person or Group	Integer and must be the ID of Person or Group
Hyperlink or Picture	Object that has three properties only like __metadata, Url, Description

For inserting multiple values to person or group column, we have to specify the ids of people or group.

var data = {
    __metadata: { "type": "SP.Data.TestListItem" },
    Title: "Some title",
    MultiplePersonId: { 'results': [11,22] } 
}

N.B.: This is an update based on user comment.

How to specify the value of __metadata for new list item? Actually, it looks like the following.

__metadata: {'type': 'SP.Data.' + 'Internal Name of the list' + 'ListItem'}

Or we can get it it from following URL by sending a GET request.

/_api/Web/Lists/getbytitle('List Name')/ListItemEntityTypeFullName

Updating Item

We can use the following method for updating an item.

function updateItem(url, oldItem, newItem) {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + url,
        type: "PATCH",
        headers: {
            "accept": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "content-Type": "application/json;odata=verbose",
            "X-Http-Method": "PATCH",
            "If-Match": oldItem.__metadata.etag
        },
        data: JSON.stringify(newItem),
        success: function (data) {
            console.log(data);
        },
        error: function (error) {
            alert(JSON.stringify(error));
        }
    });
}

Then you can get your old item, modify it and construct the URL.

var updateItemUrl = "/_api/Web/Lists/GetByTitle('SpTutorial')/
getItemById('Id of old item')";

Something has been changed as compared to add new item. Let me explain one by one. Now HTTP method is PATCH and it is also specified in header ("X-Http-Method": "PATCH") and which is recommended I mentioned earlier.

etag means Entity Tag which is always returned during HTTP GET items. You have to specify etag value while making any update or delete request so that SharePoint can identify if the item has changed since it was requested. Following are the ways to specify etag.

1. "If-Match": oldItem.__metadata.etag (If etag value does not match, service will return an exception)
2. "If-Match": "*" (It is considered when force update or delete is needed)

Deleting Item

It’s very simple. Just the following method can do everything.

function deleteItem(url, oldItem) {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + url,
        type: "DELETE",
        headers: {
            "accept": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "If-Match": oldItem.__metadata.etag
        },
        success: function (data) {
           
        },
        error: function (error) {
            alert(JSON.stringify(error));
        }
    });
}

URL is the same as updating item. It will not return anything when operation is successful. But if any error happens, then it will return an exception.