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

by
SharePoint Training Course Bundle (60% Discount) Lifetime Access

Check out Best Alternative to InfoPath -> Try Now

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

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.

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

    • 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.

      Reply
    • >