Load the Data in JQuery DataTable from SharePoint 2013 List using REST API

InfoPath alternatives for form designing SharePoint

In this SharePoint 2013 Rest API tutorial, I will show how you can retrieve the data from the SharePoint List using the REST API and bind it to the JQuery DataTable.

JQuery DataTable is an excellent plugin tool built on JQuery JavaScript library to build an HTML table with a lot of advanced interaction controls like pagination, sorting, searching, etc.

You can download the js file for the data table from here.

For the purpose of the demo, I have created a customer list with the below columns and loaded it with some dummy data.

JQuery DataTable SharePoint 2013 List using REST API
JQuery DataTable SharePoint 2013 List using REST API

First of all, we will create 2 files viz CustomerJqueryDatatable.js and CustomerJqueryDatatable.txt files and place it under the Site Assets Library. Also, make sure you add the jquery js file in your SiteAssets/js folder.



Add the content editor web part on your page and give the path of the CustomerJqueryDatatable.txt file from the Site Assets Library and Save/Publish the Page.

Open the CustomerJqueryDatatable.txt file in SharePoint Designer and add the reference to the “jquery-1.11.0.min.js”, “jquery.dataTables.min.js” ,”jquery.dataTables.min.cs” and “CustomerJqueryDatatable.js” file in the CustomerJqueryDatatable.txt file.

<script type=”text/javascript” src=”../SiteAssets/js/jquery-1.11.0.min.js”></script>
<script type=”text/javascript” src=”https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js”></script>
<link href=”https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css” rel=”Stylesheet” type=”text/css” />
<script type=”text/javascript” src=”../SiteAssets/CustomerJqueryDatatable.js”></script>

Add the below HTML content in the CustomerJqueryDatatable.txt file. This will act as a placeholder for your jquery datatable
<div id=”CustomerPanel”>
<table style=”width: 100%;”>
<tr>
<td>
<div id=”CustomerGrid” style=”width: 100%”></div>
</td>
</tr>
</table>
</div>

Open the CustomerJqueryDatatable.js file and add the below code

$(document).ready(function() {
var RestUrl = “../_vti_bin/listdata.svc/Customers”;
$.ajax({
url: RestUrl,
method: “GET”,
headers: {
“accept”: “application/json;odata=verbose”,
},
success: function(data) {
if (data.d.results.length > 0) {
//construct HTML Table from the JSON Data
$(‘#CustomerGrid’).append(GenerateTableFromJson(data.d.results));
//Bind the HTML data with Jquery DataTable
var oTable = $(‘#CustomerRecordsTable’).dataTable({
“iDisplayLength”: 5,
“aLengthMenu”: [
[5, 10, 30, 50],
[5, 10, 30, 50]
],
“sPaginationType”: “full_numbers”
});
} else {
$(‘#CustomerGrid’).append(“<span>No Customer Records Found.</span>”);
}
},
error: function(data) {
$(‘#CustomerGrid’).append(“<span>Error Retreiving Customer Records. Error : ” + JSON.stringify(data) + “</span>”);
}
});
function GenerateTableFromJson(objArray) {
var tableContent = ‘<table id=”CustomerRecordsTable” style=”width:100%”><thead><tr><td> ?</td>’ + ‘<td>Customer Name</td>’ + ‘<td>Address</td>’ + ‘<td>Home Phone</td>’ + ‘<td>Mobile Number</td>’ + ‘<td>Email</td>’ + ‘</tr></thead><tbody>’;
for (var i = 0; i < objArray.length; i++) {
tableContent += ‘<tr>’;
tableContent += ‘<td>’ + objArray[i].Id + ‘</td>’;
tableContent += ‘<td>’ + objArray[i].CustomerName + ‘</td>’;
tableContent += ‘<td>’ + objArray[i].Address + ‘</td>’;
tableContent += ‘<td>’ + objArray[i].HomePhone + ‘</td>’;
tableContent += ‘<td>’ + objArray[i].MobileNumber + ‘</td>’;
tableContent += ‘<td>’ + objArray[i].Email + ‘</td>’;
tableContent += ‘</tr>’;
}
return tableContent;
}
});

Once you save this file and refresh the page where you have added the content editor webpart you will be able to see the data loaded in the JQuery Datatable. As you can see it provides the inbuilt functionality for the pagination, search, filtering and sorting without any additional code.

 Load the Data in JQuery DataTable from SharePoint 2013 List using REST API
Load the Data in JQuery DataTable from SharePoint 2013 List using REST API

Read some SharePoint 2013 Rest API examples:

Hope this SharePoint rest API tutorial will be helpful.


You May Also like the Following SharePoint Online Tutorials:

Leave a Reply