Show SharePoint List Data using jQuery Datatable

This SharePoint tutorial, we will discuss how to show SharePoint list data using jQuery datatable with some advanced feature.

jQuery DataTable is an excellent plugin tool built on jQuery JavaScript library to build an HTML table with a lot of advanced interaction controls, like Pagination, Multi-column Filtering, Multi-column Ordering, Export to Excel, Hyperlinking, and Searching all data table content, etc.

Users can’t filter a list item of particular column value or not showing the particular item details. A lot of data in the list is showing in the pagination format for fast rendering the page with data.

We overcome the limitation of the SharePoint list view using jQuery table along with advanced features like Hyperlink, pagination, Multi-column Filtering, Multi-column ordering and Search.

We meet the user’s expectation to follow the below procedure. Now, we move to the functionality part that suggests we need to follow CDN to get the required JS and CSS files that are required to be used in our functionality.

 We use the following JS for Multi-Column Filtering functionality.

For this, we need two files – one is an HTML file to render the data and another one is a JS file to get the data from the SharePoint list and map the data table plug-in methods.

Show SharePoint List Data using jQuery Datatable

Create the HTML file

<html xmlns="http://www.w3.org/1999/xhtml">  
	<head runat="server">  
	    <title></title>  
	    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" />  
	    <script src="https://code.jquery.com/jquery-3.3.1.js" type="text/javascript"></script>  
	    <script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>  
	    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>  
	    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css" />  
	    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.min.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://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.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://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">  
	    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css">  
	    <script src="https://pointerone.sharepoint.com/sites/SPFXDemo/SiteAssets/filterDropDown.js"></script>	  
	</head>  
	<body>  
	    <table id="table_id" class="display" role="grid" width="100%" cellspacing="0" style="width: 175%;">  
	        <thead>  
	            <tr>  
	                <th>Employee Name</th>  
	                <th>Location</th>  
	                <th>Designation</th>  
	            </tr>  
	        </thead>  
	    </table>  
	</body>  
	</html>  

Create a document library (CustomLibrary) and Customfile.txt file.

display SharePoint List Data using jQuery Datatable

In the above example, the filters are loaded before the data is bound to HTML. As a result, the filters appear empty or do not appear.

For avoiding this issue, we are writing data (list items in JSON format) from the list on Page load to a file (Customfile.txt) in the document library (CustomLibrary) and pass this same list to the datatables on page load so that the data is available to bind for filters.

For SharePoint List Items More than 5000

Create rest service url if item count is above 5000 then we split calling with 1000 item per call top=1000 for better performance. Otherwise we call as usual 5000 items top=5000.

how to display SharePoint List Data using jQuery Datatable

From this page, select Site Actions -> Edit Page. Edit the page, go to the “Insert” tab in the ribbon and click the “Web Part” option. In the Web Parts picker area, go to the “Media and Content” category, select the Script Editor Web Part, and press the “Add” button.

Once the Web Part is inserted into the page, you will see an “EDIT SNIPPET” link; click it. You can insert HTML and/or JavaScript, as shown below.

	<script type="text/javascript">  
	    var items = "";  
	    var itemcollection = "";  
	    var TotalItemCount = 0;  
	    SP.SOD.executeOrDelayUntilScriptLoaded(updateFile, 'SP.js');  
	    //Step 1. get total item count of threshold exceed list.  
	    function GetItemCount(siteurl, ListName) {  
	        var ItemCount = '';  
	        $.ajax({  
	            url: siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/ItemCount",  
	            method: "GET",  
	            async: false,  
	            headers: { "Accept": "application/json; odata=verbose" },  
	            success: function (data) {  
	                ItemCount = data.d.ItemCount;  
	            },  
	            error: function (data) {  
	                console.log(data);  
	            }  
	        });  
	        return ItemCount;  
	    }  
	    //Step 2. create rest service url if item count is above 5000 then we split calling with 1000 item per call top=1000 for better performance. Otherwise we call as usual 5000 items top=5000  
	    function createRestUrl(siteurl, ItemCount, ListName) {  	  
	        if (ItemCount <= 5000) {  
	            //Item count less than 5000 so we limit it as usual 5000  
	            var listServiceUrl = siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Id,EmployeeName,Location,Designation&$top=5000";  
	        } else {  
	            //Item count more than 5000 so we split it in 1000 item per call  
	            var listServiceUrl = siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Id,EmployeeName,Location,Designation&$top=1000";  
	        }  	  
	        processList(listServiceUrl, ItemCount);  
	    }  	  
	    //Step 3: Rest call to procerss each items of list  
	    function processList(nextUrl, ItemCount) {  	  
	        var dfd = new $.Deferred();  	  
	        if (nextUrl == undefined) {  
	            dfd.resolve();  
	            return;  
	        }  
	  
	        //Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object.  
	        getJSONDataFromUrl(nextUrl).done(function (listItems) {  	  
	            TotalItemCount = TotalItemCount + listItems.d.results.length;  	  
	            items = listItems.d.results;  
	            var next = listItems.d.__next;  	  
	            $.when(processList(next, ItemCount)).done(function () {  	  
	                dfd.resolve();  	  
	            });  
	            for (var i = 0; i <= items.length; i++) {  
	                if (i == 0) {  
	                    itemcollection = itemcollection + '{"data": [[' + '"' + items[i].EmployeeName + '",' + '"' + items[i].Location + '",' + '"' + items[i].Designation + '",' + '"' + items[i].Id + '"],';  
	                }  
	                else if ((items.length > i) && (i != 0)) {  
	                    if ((items.length > i) && (i != 0))  
	                        itemcollection = itemcollection + "[" + '"' + items[i].EmployeeName + '",' + '"' + items[i].Location + '",' + '"' + items[i].Designation + '",' + '"' + items[i].Id + '"],'  
	                }  
	                else if ((items.length == i) && (i != 0)) {  
	                    itemcollection = itemcollection.slice(0, -1) + "]}";  
	                }  
	            }  
	  
	        });  
	    }  	  
	    //Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object.  
	    function getJSONDataFromUrl(endpoint) {  
	        return jQuery.ajax({  
	            url: endpoint,  
	            method: "GET",  
	            async: false,  
	            headers: {  
	                "Accept": "application/json; odata=verbose",  
	                "Content-Type": "application/json; odata=verbose"  
	            }  
	        });  
	    }  	  
	    function updateFile() {  
	        var siteurl = _spPageContextInfo.webAbsoluteUrl;  
	        var ItemCount = GetItemCount(siteurl, 'EmployeeInformation');  
	        var clientContext;  
	        var oWebsite;  
	        var oList;  
	        var fileCreateInfo;  
	        var fileContent;  
	        clientContext = new SP.ClientContext.get_current();  
	        oWebsite = clientContext.get_web();  
	        oList = oWebsite.get_lists().getByTitle("CustomLibrary");  
	        fileCreateInfo = new SP.FileCreationInformation();  
	        fileCreateInfo.set_url("Customfile.txt");  
	        fileCreateInfo.set_content(new SP.Base64EncodedByteArray());  
	        fileCreateInfo.set_overwrite(true);  
	        createRestUrl(siteurl, ItemCount, 'EmployeeInformation');  
	        //fileContent = JSON.stringify(items);  
	        fileContent = itemcollection;  
	        for (var i = 0; i < fileContent.length; i++) {  
	            fileCreateInfo.get_content().append(fileContent.charCodeAt(i));  
	        }  
	        this.existingFile = oList.get_rootFolder().get_files().add(fileCreateInfo);  
	        clientContext.load(this.existingFile);  
	        clientContext.executeQueryAsync(Function.createDelegate(this, successHandler), Function.createDelegate(this, errorHandler));  
	  
	        function successHandler() {  
	            $('#table_id').DataTable({  
	                // Definition of filter to display    
	                ajax: siteurl + "/CustomLibrary/Customfile.txt",  
	                dom: 'Bfrtip',  
	                buttons: [  
	                'excel'    //Export to excel  
	                ],  
	                "columnDefs": [{  
	                    "targets": 0,  
	                    "render": function (data, type, row) {//Hyper link to Column First  
	                        if (type === "display") {  
	                            return "<a style=\"text-decoration: none; border-bottom: 1px solid #337ab7;\" href=\"https://pointerone.sharepoint.com/sites/SPFXDemo/Lists/EmployeeInformation/DispForm.aspx?ID=" + encodeURIComponent(row[3]) + "\">" + data + "</a>";  
	                        }  
	                        return data;  
	                    }  
	                }  
	                ],  
	                filterDropDown: { //Multi-column Filtering  
	                    columns: [  
	            {  
	                idx: 0  
	            },  
	            {  
	                idx: 1  
	            },  
	                         {  
	                             idx: 2  
	                         },  
	                    ],  
	                    bootstrap: true  
	                }  
	            });  
	        }  
	        function errorHandler() {  
	            alert("error");  
	        }  
	    }  
	</script>  

Now if you see the final output will appear like below:

how to show SharePoint List Data using jQuery Datatable

You may like following SharePoint tutorials:

This SharePoint tutorial, we learned how to display SharePoint list items using jQuery datatable.

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

Sagar Pardeshi

I am Developer working on Microsoft Technologies for the past 6+years. I am very much passionate about programming and my core skills are SharePoint, ASP.NET & C#,Jquery,Javascript,REST. I am running this blog to share my experience & learning with the community I am an MCP, MCTS .NET & Sharepoint 2010, MCPD Sharepoint 2010, and MCSD HTML 5,Sharepoint 2013 Core Solutions. I am currently working on Sharepoint 2010, MOSS 2007, Sharepoint 2013,Sharepoint 2013 App Dev, C#, ASP.NET, and SQL Server 2008.

  • John Scott says:

    This didn’t work. It throws back a datatble/tn/1 and tn/7 error. Have you gotten this to work for a document library using folders with over 5k documents?

  • >