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

In this article 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 lot of advanced interaction controls like pagination, sorting, searching, etc. 

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

Articles on the Jquery DataTable and SharePoint REST API 

• Load the Data in JQuery DataTable from SharePoint List using REST API
• Custom DropDown Column Filter in JQuery Datatable
• Free Text Search on Column in JQuery Datatable
• Get Data in JQuery DataTable from SharePoint List using $skip, $top, $inlinecount, $orderby parameters

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

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.

I hope this article was useful! Happy SharePointing!