Display SharePoint list data in jQuery data table using Rest API

Populate list items using jQuery datatable and REST API in SharePoint

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.

Populate list items using jQuery datatable and REST API in SharePoint
Populate list items using jQuery datatable and REST API in SharePoint

Step 2: I have added few items in this list and populate the same in jQuery data table using REST API.

bind list data to jquery datatable
bind list data to jquery datatable

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.

Dowload all js files

<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.

display sharepoint list data in jquery data table
display sharepoint list data in jquery data table

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.

jquery datatables sharepoint rest api
jquery datatables sharepoint rest api

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.

display sharepoint list data in html table using javascript
display sharepoint list data in html table using javascript

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.

jquery datatable in sharepoint online
jquery datatable in sharepoint online

You may like following SharePoint Rest API tutorials:

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.

Check out Best Alternative to InfoPath -> Try Now

EnjoySharePoint YouTube Channel

About Rajkiran Swain

Rajkiran is currently working as a SharePoint Consultant in QATAR . Rajkiran having 6 + years of experience in Microsoft Technologies such as SharePoint 2019/2016/2013/2010, MOSS 2007,WSS 3.0, Migration, Asp.Net, C#.Net, Sql Server, Ajax, jQuery etc.He is C#Corner MVP (2 Times).

View all posts by Rajkiran Swain →