This SharePoint tutorial, we will discuss how to display SharePoint list data in the jQuery data table using Rest API. We can display list data using jQuery data table using Rest API in SharePoint Online or SharePoint 2013/2016.
Here I have a SharePoint Online list and we need to display the list data using jQuery datatable using Rest API code.
Display SharePoint list data in jQuery data table using Rest API
Step 1: Here I have created a SharePoint list called “Ticket info” with specific columns.
Step 2: I have added few items in this list and populate the same in jQuery data table using REST API.
Step 3: I have created a table using simple HTML code and I have bootstrap for responsive.
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="table-responsive">
<table id="pDashboard" class="table" cellspacing="0" style="width:100% !important;">
<thead>
<tr>
<th></th>
<th>ID</th>
<th>Ticket No </th>
<th>Ticket Name</th>
<th>Status</th>
<th>Manager Name</th>
<th>Assign To</th>
</tr>
</thead>
<tfoot> </tfoot>
</table>
</div>
</div>
</div>
</div>
</body>Step 4: Next we have to use jQuery references before we start the coding. The below references we need to add in this code.
<script type="text/javascript" src="https://pikasha12.sharepoint.com/sites/DLH/Documents/Task%20Management/jquery-3.3.1.js"></script>
<script type="text/javascript" src="https://pikasha12.sharepoint.com/sites/DLH/Documents/Task%20Management/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://pikasha12.sharepoint.com/sites/DLH/Documents/Task%20Management/dataTables.bootstrap4.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/select/1.2.5/js/dataTables.select.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js"></script>
<link rel="stylesheet" href="https://pikasha12.sharepoint.com/sites/DLH/Documents/Task%20Management/bootstrap.css">
<link rel="stylesheet" href="https://pikasha12.sharepoint.com/sites/DLH/Documents/Task%20Management/dataTables.bootstrap4.min.css">
<link rel="stylesheet" href="https://pikasha12.sharepoint.com/sites/DLH/Documents/Task%20Management/dashboard.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">Step 5: Next we have to call the REST API to get the list data and bind the same in jquery data table in SharePoint.
<script>
$(document).ready(function() {
loadListItems(); //to load list items
});
function loadListItems() {
var oDataUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('TicketInfo')/items?$select=ID,Title,Ticket_x0020_Name,Start_x0020_Date,End_x0020_Date,Status,Project_x0020_Details,TeamMember/Title,Manager_x0020_Name/Title&$expand=TeamMember,Manager_x0020_Name";
console.log(_spPageContextInfo);
$.ajax({
url: oDataUrl,
type: "GET",
dataType: "json",
headers: {
"accept": "application/json;odata=verbose"
},
success: successFunction,
error: errorFunction
});
}
function successFunction(data) {
try {
var dataTableExample = $('#pDashboard').DataTable();
if (dataTableExample != 'undefined') {
dataTableExample.destroy();
}
dataTableExample = $('#pDashboard').DataTable({
//scrollY: "350px",
autoWidth: true,
dom: 'Blfrtip',
"pageLength": 5,
buttons: [
'copyHtml5',
'excelHtml5',
'csvHtml5',
'pdfHtml5'
],
/* order: [
[0, 'desc'],
],*/
columnDefs: [{
"width": "3%",
"targets": [0]
}, {
"width": "3%",
"targets": [1]
}, {
"width": "8%",
"targets": [2]
}, {
"width": "6%",
"targets": [3]
}, {
"width": "10%",
"targets": [4]
}, {
"width": "10%",
"targets": [5]
},
{
"width": "10%",
"targets": [6]
}
],
"aaData": data.d.results,
"aoColumns": [{
"className": 'details-control',
"orderable": false,
"data": null,
"defaultContent": ''
}, {
"mData": "ID",
"render": function(mData, type, row, meta) {
var returnText = "";
var url = _spPageContextInfo.webAbsoluteUrl + "/Lists/TicketInfo/DispForm.aspx?ID=" + mData;
returnText = "<a target='_blank' href=" + url + ">" + mData + "</a>";
return returnText;
}
},
{
"mData": "Title"
}, {
"mData": "Ticket_x0020_Name"
}, {
"mData": "Status",
"render": function(data) {
if (data === null) return "";
else
return "<label class='badge1 badge-" + data + "'>" + data + "</label>"
}
}, {
"mData": "Manager_x0020_Name",
"render": function(mData, type, full, meta) {
var returnText = "";
if (mData.Title == undefined) return "";
else return mData.Title;
console.log(mData.Title);
}
}, {
"mData": "TeamMember",
"render": function(mData, type, full, meta) {
var returnText = "";
if (mData.Title == undefined) return "";
else return mData.Title;
console.log(mData.Title);
}
}]
});
$('#pDashboard tbody').on('click', 'td.details-control', function() {
//alert('H');
var tr = $(this).closest('tr');
var row = dataTableExample.row(tr);
if (row.child.isShown()) {
// This row is already open - close it
row.child.hide();
tr.removeClass('shown');
} else {
// Open this row
row.child(format(row.data())).show();
tr.addClass('shown');
}
});
function format(d) {
// `d` is the original data object for the row
return '<table cellpadding="5" cellspacing="0" style="margin-left:-12px;width:100%;" border="0">' +
'<tr>' +
'<td><b>Description:</b></td>' +
'<td>' + d.Project_x0020_Details + '</td>' +
'</tr>' +
'<tr>' +
'<td colspan="2"><table style="width: 101%;">' +
'<td><b>Start Date:</b></td>' +
'<td>' + getDates(d.Start_x0020_Date) + '</td>' +
'<td><b>End Date:</b></td>' +
'<td>' + getDates(d.End_x0020_Date) + '</td>' +
'</td></table>'
'</tr>' +
'</table>';
}Step 6: Next copy the same code and paste it inside a script editor web part and net click on OK button to get the O/P of this code. The below is the screenshot of this O/p.
Step 7: Here I have added a plus and minus icon to see the more details of a specific item. Please look into the screenshot below.
Step 8: I have added a few advanced features to save the table data as a PDF or Excel format. If you click on the button.
Step 9: We can also easily search the Item in the search text box and show full item instead of paging. The below image is my full O/P of this code.
You may like following SharePoint Rest API tutorials:
- Create Highcharts in SharePoint using Rest API
- SharePoint Rest API Crud Operations
- Create SharePoint Quote of the day web part using Rest API and Bootstrap
- Create custom announcement web part using Rest API in SharePoint Online/2013/2016
- Update People Picker Group field using Rest API and jQuery in SharePoint 2013
- Display Task List data in a table using SharePoint REST API and filter by status column
- Send Email with jQuery and REST API in SharePoint
- SharePoint 2013 Add Top Navigation Link to a Site Using REST API
- KnockoutJS SharePoint 2013 CRUD Operations using REST API
- How to Trigger Sharepoint 2013 Designer workflow on a list item by calling REST API using jQuery?
- Upload attachment to a new item on the list using REST API JQuery in SharePoint 2013
- Display SharePoint list item level attachments using REST API and JQuery in SharePoint 2013
Hope this SharePoint tutorial explains how to display SharePoint list data in jQuery data table using Rest API in SharePoint Online or SharePoint 2013/2016.
SharePoint Online FREE Training
JOIN a FREE SharePoint Video Course (3 Part Video Series)
Hi, I have tried but seems it is not working, I can see the headers but nothing else
Hey Ryan,
Can you check in the console, if any script error is there? You can check in Google Chrome -> more tools -> Developer tools and then in the console tab.
Yes. Check the console .. This is a tested code and It was working fine. Please add all reference file properly and try .
Hi Rajkiran, I have same issue, I see only the banner but nothing else work, I have tried everything, even recreate list with column ID but nothing, any suggestion? Thank you
Hi ron,
Could you please share me the screenshot of your console so that I can able to recognize the exact issue.I couldn’t suggest anything without knowing the exact issue of your code.
Hi Rajkiran, thank you for your reply, how I should send the screenshot? have no option here. Thank you
Please copy and paste the error over here.
Hi Rajkiran, I have tried but it does not allow to paste inside this box. Thank you
I think it will allow in text format.
Okay, so only thing I see after implementing the code is banner above the list which more like
ID Ticket No Ticket Name Status and so forth. I don’t see any error nor message, it seems something in the code is not working. Thank you
Then put the break point and see where it break.
sure, I will try and let you know, thank you for your help and time!
Best
It worked this time, thank you.
Apologize for submitting the comment to myself, it worked. Thank you
Hi Raj, I have followed all the steps but I don’t see its working, I see the titles but that is it. I have tried “Retrieve and Display TASK status using REST API in SharePoint” which using the same js/css files and it worked perfectly with same list but when I tried to work on the above; it does not work. I am not JavaScript expert and don’t know what am I doing wrong? Thank you
Hi Raj, just want to Thank you because it worked because I was missing column “TeamMember” as soon as I added that column; it worked. Only question I have that how you added the plus and minus in the list? Again, thank you so much!
Imran
Download the plus and minus icons from internet or add js library to find the icons. NEXT refer the same in the code. Follow the same code to get better idea.
Thank you and will do.
I tried following the same code, still icons seems to be missing from the DataTable
can u give me steps please to make this list ?
You can follow the screenshot to create the list, you can start with creating a custom list.
Where i should add a TeamMember column?
-1, Microsoft.SharePoint.SPException
The “TeamMember” field or property does not exist.
please can u help me for video about this article
Excellent post, but only issue is with font awesome icons, they don’t show up, should I be using different reference files? Please help.
No need to add reference here. Yiu can also download both icons like plus and minus …and keep it in your library and refer the same link in your code .
Which part of the code should I link the reference? please bear with me here, only this is where I’m stuck at.
Please follow my below comment.
Hello Guys,
If anyone is facing issue to showing Plus and Minus icon,Please download all references file and go to the Dashboard.css and modify the URL .
Download both plus and Minus icon from internet and keep it in you SharePoint library , next open the dashboard.css and modify the URL in below section.
td.details-control {
background: url(‘/sites/DLH/Documents/Task%20Management/details_open.png’) no-repeat center center;
cursor: pointer;
}
tr.shown td.details-control {
background: url(‘/sites/DLH/Documents/Task%20Management/details_close.png’) no-repeat center center;
}