Post
Ask Question

Using jQuery DataTable To Display SharePoint 2013 List Data On SharePoint Site Pages

Introduction

In this article, we will learn how to pass SharePoint List Data to a jQuery DataTable using REST API and display SharePoint list data on SitePages using DataTable.

DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based on the foundations of progressive enhancement, and will add advanced interaction controls to any HTML table.

[Official Site] : https://datatables.net/ - Please refer to the official site for documentation and the getting started guide
 
Before starting with this post, we must have some basic knowledge of the following.
  • HTML Tables
  • Rest API in SharePoint
  • JavaScript 
Implementation
 
Step 1

Create a Sharepoint List with data, this is the list from which we will retrieve the data and pass the data to jQuery DataTable. Skip this step if you already have the Data Source List on your SharePoint Site.
 
I have created a list of sample data for our project, Sample Data Source: jQuery DataTable Official Site, You can also use import spreadsheet SharePoint App to import bulk data from excel spreadsheet 
 
Sharepoint
 
Step 2

Now we will create the script file and the html Site page for displaying the list data.
  • HTML file to render the data 
  • JS file to get the data from SharePoint list.
Go to Data Table CDN to get the required JS and CSS files.

We need to follow CDN scripts, that are required to be used in our functionality

  • jquery.min.js
  • jquery.dataTables.min.js
  • jquery.dataTables.min.css
  • dataTables.jqueryui.min.css 
Create the HTML file in a text editor (I use notepad ++)
 
Note

jquery.min.js should be loaded before jquery.dataTables.min.js as shown in the below code block,
  1. <!DOCTYPE html>    
  2. <html>    
  3. <head>    
  4.     <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.0.0/jquery.min.js"></script>        
  5.     <script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>     
  6.       
  7.     <script type="text/javascript" src="/SiteAssets/GetData.js"></script>    
  8.     <!--External js file to get data from SharePoint List -->   
  9.       
  10.     <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">    
  11.     <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.12/css/dataTables.jqueryui.min.css">      
  12. </head>    
  13. <body>    
  14.    <div>    
  15.     <table id="table_id" class="display" cellspacing="0" width="100%">    
  16.         <thead>    
  17.             <tr>    
  18.                 <th>Name</th>    
  19.                 <th>Position</th>    
  20.                 <th>Office</th>    
  21.                 <th>Age</th>    
  22.                 <th>Joining Date</th>            
  23.             </tr>    
  24.         </thead>    
  25.         <tfoot>  
  26.             <tr>    
  27.                 <th>Name</th>    
  28.                 <th>Position</th>    
  29.                 <th>Office</th>    
  30.                 <th>Age</th>    
  31.                 <th>Joining Date</th>                   
  32.             </tr>   
  33.         </tfoot>    
  34.     </table>    
  35.     </div>    
  36. </body>    
  37. </html>    
Step 3

Now, we will get Data from the SharePoint list. For this, write a function to get the data, using REST API.
 
Create the GetData.js file in a text editor (I use notepad ++)
  1. function loadItems() {    
  2.     var siteUrl = _spPageContextInfo.siteAbsoluteUrl;    
  3.     var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('EmployeeInfo')/items?$select=Title,Position,Office,Age,Joining_x0020_date";    
  4.     $.ajax({    
  5.         url: oDataUrl,    
  6.         type: "GET",    
  7.         dataType: "json",    
  8.         headers: {    
  9.             "accept": "application/json;odata=verbose"    
  10.         },    
  11.         success: mySuccHandler,    
  12.         error: myErrHandler    
  13.     });    
  14. }    
Note

Use $top=5000 if items more than 100.
 
$select filter values should be the internal names of the columns, verify the values using a browser-based GET Request [Use Internet Explorer Only] Internet Explorer Screenshot - Put API Url in the browser,

Sharepoint

Step 4

Now we will bind the data obtained from the API Call with the jQuery DataTable [HTML View].
 
mySuccessHandler function binds the data to the DataTable. 
  1. function mySuccHandler(data) {    
  2.     try {    
  3.           
  4.         $('#table_id').DataTable({    
  5.               
  6.             "aaData": data.d.results,    
  7.             "aoColumns": [  
  8.             {    
  9.                 "mData": "Title"    
  10.             },   
  11.             {    
  12.                 "mData": "Position"    
  13.             },   
  14.             {    
  15.                 "mData": "Office"    
  16.             },   
  17.             {    
  18.                 "mData": "Age"    
  19.             },  
  20.             {    
  21.                 "mData": "Joining_x0020_date"    
  22.             }             
  23.             ]    
  24.         });    
  25.     } catch (e) {    
  26.         alert(e.message);    
  27.     }    
  28. }    
myErrHandler function 
  1. function myErrHandler(data, errMessage) {    
  2.     alert("Error: " + errMessage);    
  3. }   
Our GetData.js file will look like,
  1. $(document).ready(function() {    
  2.     loadItems();    
  3. });    
  4.     
  5.   
  6. function loadItems() {    
  7.     var siteUrl = _spPageContextInfo.siteAbsoluteUrl;    
  8.     var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('EmployeeInfo')/items?$select=Title,Position,Office,Age,Joining_x0020_date";    
  9.     $.ajax({    
  10.         url: oDataUrl,    
  11.         type: "GET",    
  12.         dataType: "json",    
  13.         headers: {    
  14.             "accept": "application/json;odata=verbose"    
  15.         },    
  16.         success: mySuccHandler,    
  17.         error: myErrHandler    
  18.     });    
  19. }    
  20.   
  21. function mySuccHandler(data) {    
  22.     try {    
  23.           
  24.         $('#table_id').DataTable({    
  25.               
  26.             "aaData": data.d.results,    
  27.             "aoColumns": [  
  28.             {    
  29.                 "mData": "Title"    
  30.             },   
  31.             {    
  32.                 "mData": "Position"    
  33.             },   
  34.             {    
  35.                 "mData": "Office"    
  36.             },   
  37.             {    
  38.                 "mData": "Age"    
  39.             },  
  40.             {    
  41.                 "mData": "Joining_x0020_date"    
  42.             }             
  43.             ]    
  44.         });    
  45.     } catch (e) {    
  46.         alert(e.message);    
  47.     }    
  48. }    
  49.     
  50. function myErrHandler(data, errMessage) {    
  51.     alert("Error: " + errMessage);    
  52. }   
Step 5

Copy both the files [html and JavaScript] to SharePoint SiteAssets Library.

Create a Webpart Page (Named DataTable) and store it in the pages or the site pages library.

Add a content editor to DataTable Page. Now, give HTML file reference to the content editor into Content Editor.

Sharepoint
Click OK and save the page
 
Output UI

Sharepoint
 
Real-Time Search, Paging, Sorting, View Functionality [In-built DataTable functionality - NO CODE]

Sharepoint

I hope this article was helpful in getting started with the jQuery DataTable plugin. Please share your valuable suggestions and feedback. 


Similar Articles