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

Guest User

SharePoint jQuery jQuery DatatableSharePoint Site PagesDisplay SharePoint 2013 List data

14 Comments

Join the conversation! Your thoughts help the community grow.

Abigail6y ago
Hi! thanks for the tutorial. I have an additional question, how can the first column be converted to a url to link to the item? also can column filters be added to this as well? Thank you in advanced
- bikram pal4y ago
  Any reply for this? can we add edit item button.??
Karthick N6y ago
It worked great for me. I can able to view the data. But It shows only 100 rows and link is not available. Kindly suggest
Sam Timothy6y ago
How to show a lookup field?
Sarmad Yousif7y ago
I only have the html loaded (column headers). Do I need to upload datatables.min and so on to site assets. I have the get data js file linked correctly.
- Karthick N6y ago
  You can use datatable link from https://datatables.net/
Ullas Anand7y ago
How to add these buttons : copy, excel, pdf. I am getting syntax error if i follow the syntax which is given on datatables.net website, please help
Anish Gupta7y ago
Can you please suggest how to format the date field for the same code if it is returning the deta field data as 2015-09-30T18:30:00Z. I want this in dd-mm-yyyy format
- Stephen SCHUSTER5y ago
  I didn't see an answer to this but use moment JS.
Eduardo Araujo7y ago
Awesome job! Is there some option for select multiple lines enabling check boxes as first column?
Siskoglaz Siskoglaz8y ago
Thanks Akshat. What would you do if items more than 5000? Regular user (not admin) will get standart threshold retrieving 5000 items...
Bryan Waldrop8y ago
Great solution! What would be the syntax for the use of "Use $top=5000 if items more than 100"? Where would I place that and how would it look?
- Bryan Waldrop8y ago
  Excellent thank you! And what form would an additional filter take that limited the table data loaded to the user login, assuming there was a matching data in one of the table columns?
- Guest User8y ago
  Thanks! By default SharePoint REST API fetches only top 100 items from the list.So to get all the items from the list where items are more than 100 use syntax $top=5000 - fetches top 5000 items from the list (i.e. As SharePoint List View Threshold Limit = 5000 so our query will return all list items) [Syntax : var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('EmployeeInfo')/items?$select=Title,Position,Office,Age,Joining_x0020_date&$top=5000"; //append $top=5000 to existing API Data URL ]

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

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,

<!DOCTYPE html>
<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="/SiteAssets/GetData.js"></script>
<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="table_id" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Joining Date</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Joining Date</th>
</tr>
</tfoot>
</table>
</div>
</body>
</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 ++)

function loadItems() {
var siteUrl = _spPageContextInfo.siteAbsoluteUrl;
var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('EmployeeInfo')/items?$select=Title,Position,Office,Age,Joining_x0020_date";
$.ajax({
url: oDataUrl,
type: "GET",
dataType: "json",
headers: {
"accept": "application/json;odata=verbose"
},
success: mySuccHandler,
error: myErrHandler
});
}

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,

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.

function mySuccHandler(data) {
try {
$('#table_id').DataTable({
"aaData": data.d.results,
"aoColumns": [
{
"mData": "Title"
},
{
"mData": "Position"
},
{
"mData": "Office"
},
{
"mData": "Age"
},
{
"mData": "Joining_x0020_date"
}
]
});
} catch (e) {
alert(e.message);
}
}

myErrHandler function

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

Our GetData.js file will look like,

$(document).ready(function() {
loadItems();
});
function loadItems() {
var siteUrl = _spPageContextInfo.siteAbsoluteUrl;
var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('EmployeeInfo')/items?$select=Title,Position,Office,Age,Joining_x0020_date";
$.ajax({
url: oDataUrl,
type: "GET",
dataType: "json",
headers: {
"accept": "application/json;odata=verbose"
},
success: mySuccHandler,
error: myErrHandler
});
}
function mySuccHandler(data) {
try {
$('#table_id').DataTable({
"aaData": data.d.results,
"aoColumns": [
{
"mData": "Title"
},
{
"mData": "Position"
},
{
"mData": "Office"
},
{
"mData": "Age"
},
{
"mData": "Joining_x0020_date"
}
]
});
} catch (e) {
alert(e.message);
}
}
function myErrHandler(data, errMessage) {
alert("Error: " + errMessage);
}

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.

Click OK and save the page

Output UI

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

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