How to get all SharePoint list items using Rest API + Display using DataTables Plug-in

This SharePoint rest api tutorial explains, how to get all SharePoint list items using Rest API and then how to display SharePoint list items in a data table using Rest API and jQuery in SharePoint Online or SharePoint 2013/2016.

We will see how to get all list items in sharepoint using rest api.

Here, we will use the datatables plug-in to display the SharePoint list items in a tabular format.

DataTables

DataTables is a plug-in build on top of jQuery to enhance any HTML table with its own advanced features. Without writing any code, you can enhance your HTML tables like you can have paging, search, formating, etc.

To work the DataTables JavaScript library or plug-in, you just require to refer the following .css and .js file.

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.css">

<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.js"></script>

If you do not want to provide the path from the CDN, you can also download and save it in your site and refer it from there.

Get all SharePoint list items using Rest API

Now, we will see how to get all list items in sharepoint using rest api and then display in a tabular format.

Here, I have a SharePoint Online list which has few columns like:

  • Title
  • Gender
  • ContactNumber
  • Department
  • Address

And the SharePoint list looks like below:

get all SharePoint list items using Rest API
Display SharePoint List items in a data table using Rest API

Now, we will see how to display these SharePoint list items in a data table using Rest API, HTML, jQuery, etc in a web part page in SharePoint.

Here I will use a content editor web part to add the code into the web part page in SharePoint. So here, I have added two separated file, one is the HTML file which contents the HTML code and the other one is a js file which contains our JavaScript and Rest API code.

Both the files I have uploaded to the SiteAssets library in the SharePoint Online site.

HTML Code:

<html>
<head>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.0.0/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://onlysharepoint2013.sharepoint.com/sites/SharePointSky/SiteAssets/RetriveListItemsRestAPI.js"></script>

<!–External js file to get data from SharePoint List –>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.12/css/dataTables.jqueryui.min.css">
</head>
<body>
<div>

<table id="Employee" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>EmpName</th>
<th>Gender</th>
<th>ContactNumber</th>
<th>Department</th>
<th>Address</th>
</tr>
</thead>
</table>
</div>
</body>
</html>

RetriveListItemsRestAPI.js Code

$(document).ready(function() {
GetItems();
});

function GetItems() {
var siteUrl = _spPageContextInfo.webAbsoluteUrl;
var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('MyCompanyEmployeeList')/items?$select=Title,Gender,ContactNumber,Department,Address";
$.ajax({
url: oDataUrl,
type: "GET",
dataType: "json",
headers: {
"accept": "application/json;odata=verbose"
},
success: OnSuccess,
error: OnFailure
});
}

function OnSuccess(data) {
try {
$('#Employee').DataTable({
"aaData": data.d.results,
"aoColumns": [
{
"mData": "Title"
},
{
"mData": "Gender"
},
{
"mData": "ContactNumber"
},
{
"mData": "Department"
},
{
"mData": "Address"
}
]
});
} catch (e) {
alert(e.message);
}
}

function OnFailure(data, errMessage) {
alert("Error: " + errMessage);
}

Then add a content editor web part to the web part page and give the HTML file reference like below:

display list items in datatable using rest api sharepoint
get all SharePoint list items using Rest API

Once you save the page, you can see the list data will appear in the datatable like below:

SharePoint online display list items in datatable using rest api
get all SharePoint list items using Rest API

Display SharePoint List Items using DataTables Plug-in (Script editor web part)

If you are familiar with the script editor web part, then you can add a script editor web part in the SharePoint web part page. Then add the below code in the script editor web part.

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.0.0/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<!–External js file to get data from SharePoint List –>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.12/css/dataTables.jqueryui.min.css">

<div>
<table id="Employee" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>EmpName</th>
<th>Gender</th>
<th>ContactNumber</th>
<th>Department</th>
<th>Address</th>
</tr>
</thead>
</table>
</div>

<script>
$(document).ready(function() {
GetItems();
});

function GetItems() {
var siteUrl = _spPageContextInfo.webAbsoluteUrl;
var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('EmployeesList')/items?$select=Title,Gender,ContactNumber,Department,Address";
$.ajax({
url: oDataUrl,
type: "GET",
dataType: "json",
headers: {
"accept": "application/json;odata=verbose"
},
success: OnSuccess,
error: OnFailure
});
}

function OnSuccess(data) {
try {
$('#Employee').DataTable({
"aaData": data.d.results,
"aoColumns": [
{
"mData": "Title"
},
{
"mData": "Gender"
},
{
"mData": "ContactNumber"
},
{
"mData": "Department"
},
{
"mData": "Address"
}
]
});
} catch (e) {
alert(e.message);
}
}

function OnFailure(data, errMessage) {
alert("Error: " + errMessage);
}
</script>

The result will come like below:

get all SharePoint list items using Rest API
get all list items sharepoint rest api

Retrieve all list items using Rest API in SharePoint Online/2013/2016

Here I am retrieving list items from the default Announcements list using Rest API. The Announcements list has 3 item which we will retrieve using REST API.

Put the below code inside a Script editor web part and save the page.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>

<script>
$(function(){
$("#btnClick").click(function(){
var requestUri = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle('Announcements')/items";
$.ajax({
url: requestUri,
type: "GET",
headers: {
"accept":"application/json; odata=verbose"
},

success: onSuccess,
error: onError
});

function onSuccess(data) {
var items = data.d.results;
for (var i = 0; i < items.length; i++) {
alert(items[i].Id+ " : " + items[i].Title);
}
}
function onError(error) {

alert(JSON.stringify(error));
}
});
});
</script>

<input type="button" id="btnClick" value="Get all List Items"/>

When you will click on the button, it will display all the items like below:

Retrieve list items using Rest API in SharePoint Online
Retrieve list items using Rest API in SharePoint Online

Retrieve list item by id using REST API in SharePoint Online/2013

Now we will see how we can retrieve a particular item from a SharePoint 2013 list using REST API. Here we will use to getitembyid(id) to retrieve the particular item from the list.

In this example, I have a list where I have 3 columns name as Title, FirstName and LastName.

You can write the below code in a Script Editor web part or content editor web part and Save the web part page.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>

<script>
$(function(){
$("#btnClick").click(function(){
var requestUri = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('bijayTestList')/getitembyid(1)";
var requestHeaders = { "accept": "application/json;odata=verbose" };
$.ajax({
url: requestUri,
contentType: "application/json;odata=verbose",
headers: requestHeaders,
success: onSuccess,
error: onError
});

function onSuccess(data, request) {
var listiteminfo = data.d;
alert('Title: '+listiteminfo.Title + ' First Name:'+listiteminfo.FirstName +' Last Name: '+listiteminfo.LastName);
}

function onError(error) {
alert("error");
}

});
});

</script>
<input type="button" id="btnClick" value="Click Here"/>

When you click on the button, the result will come like below:

Retrieve list item by id using REST API in SharePoint Online/2013
Retrieve list item by id using REST API in SharePoint Online/2013

You may like following SharePoint Rest API tutorials:

I hope this SharePoint tutorial helps to get all SharePoint list items using Rest API and display SharePoint list items in a datatables plug-in using Rest API and jQuery in SharePoint Online or SharePoint 2013/2016. Also, we saw how to get all list items sharepoint rest api.

  • Hello, I’m so confused.

    How do the code know which list we want to display. I can’t see the link to the list.

    Thank you.

    • In the below line you can see the list name:
      var oDataUrl = siteUrl + “/_api/web/lists/getbytitle(‘MyCompanyEmployeeList’)/items?$select=Title,Gender,ContactNumber,Department,Address”;

      Here MyCompanyEmployeeList: Is my list name, you can change according to your list name.

  • Items is not filtering after 5000, Can you please assist me if I have more than 5000 items in SharePoint List.

  • >