Display SharePoint list data using HTML and jQuery table using Rest API

In this SharePoint tutorial, I will show you how to display SharePoint list data in an HTML table using Rest API. Also, we will see another example of displaying SharePoint Online list data using the jQuery data table and Rest API in SharePoint.

Display SharePoint list data in an HTML table using Rest API

According to our requirement, we have a SharePoint Online list as TrainingRequests with few columns and data like the below:

List Columns:

  • Title
  • Course
  • Comments
  • TrainerName
  • TrainingType
display sharepoint list data in html table using javascript

Now, according to our business requirement, we need to display this list data in an HTML table inside a web part page. So for this, first, we have to create a web part page, and then we can use a script editor web part to add our client-side code; here, we will use Rest API SharePoint Online code.

Example-1 (Without People Picker Column):

Below is the example where we are displaying all the columns in a grid except people picker control.

Below is the full code you can add inside a script editor web part inside a SharePoint Online web page.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script>
$(function(){
$("#btnGetTrainingRequests").click(function(){
var requestUri = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('TrainingRequests')/items";
$.ajax({
url: requestUri,
type: "GET",
headers: {
"accept":"application/json; odata=verbose"
},
success: onSuccess,
error: onError
});
function onSuccess(data) {
var items = data.d.results;
var fullResults = '<table id="tableCars" style="width:100%" border="1 px"><thead><tr><td><b>Title</b></td>' + '<td><b>Course</b></td>'+ '<td><b>TrainingType</b></td>' +'<td><b>Comments</b></td>' + '</tr></thead><tbody>';
for (var i = 0; i < items.length; i++) {
fullResults += '<tr>';
fullResults += '<td>' + items[i].Title + '</td>';
fullResults += '<td>' + items[i].Course + '</td>';
fullResults += '<td>' + items[i].TrainingType + '</td>';
fullResults += '<td>' + items[i].Comments + '</td>';
fullResults += '</tr>';
}
$('#resultsTable').append(fullResults);
}
function onError(error) {
alert('Error');
}
});
});
</script>
<input type="button" id="btnGetTrainingRequests" value="Get All Training Requests"/>
<br/><br/>
<table id='tableCars' style="width: 100%;" border="1 px">
<tr>
<td>
<div id="resultsTable" style="width: 100%"></div>
</td>
</tr>
</table>

Once you save the page and click on the button, you can see the output will come like below:

bind SharePoint Online list data using HTML table

Example-2: (With People picker column)

See also  The collection has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.

Here, we will see how to retrieve people picker column values using Rest API and display them in the HTML table or grid. We have “TrainerName” is a people picker column.

To retrieve the value from a people picker column, we need to change the rest endpoint like below:

var requestUri = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('TrainingRequests')/items?$select=Title,Course,TrainingType,Comments,TrainerName/EMail,TrainerName/FirstName,TrainerName/LastName&$expand=TrainerName";

Then we can access the Trainer FirstName like below “TrainerName.FirstName”.

The full code will look like below:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script>
$(function(){
$("#btnGetTrainingRequests").click(function(){
var requestUri = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('TrainingRequests')/items?$select=Title,Course,TrainingType,Comments,TrainerName/EMail,TrainerName/FirstName,TrainerName/LastName&$expand=TrainerName";
$.ajax({
url: requestUri,
type: "GET",
headers: {
"accept":"application/json; odata=verbose"
},
success: onSuccess,
error: onError
});
function onSuccess(data) {
var items = data.d.results;
var fullResults = '<table id="tableCars" style="width:100%" border="1 px"><thead><tr><td><b>Title</b></td>' + '<td><b>Course</b></td>' + '<td><b>TrainerName</b></td>' + '<td><b>TrainingType</b></td>' +'<td><b>Comments</b></td>' + '</tr></thead><tbody>';
for (var i = 0; i < items.length; i++) {
fullResults += '<tr>';
fullResults += '<td>' + items[i].Title + '</td>';
fullResults += '<td>' + items[i].Course + '</td>';
fullResults += '<td>' + items[i].TrainerName.FirstName + '</td>';
fullResults += '<td>' + items[i].TrainingType + '</td>';
fullResults += '<td>' + items[i].Comments + '</td>';
fullResults += '</tr>';
}
$('#resultsTable').append(fullResults);
}
function onError(error) {
alert('Error');
}
});
});
</script>
<input type="button" id="btnGetTrainingRequests" value="Get All Training Requests"/>
<br/><br/>
<table id='tableCars' style="width: 100%;" border="1 px">
<tr>
<td>
<div id="resultsTable" style="width: 100%"></div>
</td>
</tr>
</table>

Once you run the code, you can see the output; in the Trainer Name, we can see the FirstName from the People picker control.

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

Display SharePoint list data in jQuery data table using Rest API & jQuery

Now, we will discuss how can we display SharePoint list data in a jQuery data table using Rest API and jQuery.

The code you can use to bind list data to the jQuery table in SharePoint.

DataTables is a powerful Javascript library (js) for adding interactive features to HTML tables. This datatables provides inbuilt paging, sorting, search, etc. There are very interactive and beautiful features provided by the js library.

To work with DataTables, you need two files.

  • DataTables Javascript file (Can add the from DataTables CDN https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js)
  • DataTables CSS file (Can add the from DataTables CDN https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css)
See also  Column does not exist. it may have been deleted by another user error in SharePoint Online

If you want to use more extensions, you can get the js files from the below URL: https://cdn.datatables.net/

The full path is given from like below:

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>

Apart from this, you can also download both the files and stores inside a document library, style library, or site assets library in SharePoint site like below:

<link rel="stylesheet" type="text/css" href="/DataTables/datatables.css">
<script type="text/javascript" charset="utf8" src="/DataTables/datatables.js"></script>

Display SharePoint list data in jQuery datatables using Rest API

Now, we will see how to display SharePoint Online list data in Datatables using Rest API and jQuery.
Here I have a SharePoint Online list which has some data like below:

Display SharePoint list data in jquery datatables using Rest API

Now, we will see how to display the list data using grid and datatables using Rest API in SharePoint.

Below is the code that you can use inside a script editor web part or you can use in a script editor web part in a web part page.

<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="https://onlysharepoint2013.sharepoint.com/sites/DevSite/SiteAssets/RetriveListItemsRestAPI.js"></script>
<!--External js file to get data from SharePoint List -->
<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="Employee" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>EmpName</th>
<th>Gender</th>
<th>ContactNumber</th>
<th>Department</th>
<th>Address</th>
</tr>
</thead>
</table>
</div>
</body>
</html>
RetriveListItemRestAPI.js:-
$(document).ready(function() {
GetItems();
});
function GetItems() {
var siteUrl = _spPageContextInfo.webAbsoluteUrl;
var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('MyCompanyEmployeeList')/items?$select=Title,Gender,ContactNumber,Department,Address";
$.ajax({
url: oDataUrl,
type: "GET",
dataType: "json",
headers: {
"accept": "application/json;odata=verbose"
},
success: OnSuccess,
error: OnFailure
});
}
function OnSuccess(data) {
try {
$('#Employee').DataTable({
"aaData": data.d.results,
"aoColumns": [
{
"mData": "Title"
},
{
"mData": "Gender"
},
{
"mData": "ContactNumber"
},
{
"mData": "Department"
},
{
"mData": "Address"
}
]
});
} catch (e) {
alert(e.message);
}
}
function OnFailure(data, errMessage) {
alert("Error: " + errMessage);
}

Once you run the code, you can see it will display the data using datatables using Rest API in SharePoint 2013/2016/Online.

Display SharePoint Online list data in jquery datatables Rest API

I hope this SharePoint tutorial explains how to bind SharePoint Online list data using an HTML table or jQuery Datatable using Rest API and jQuery in SharePoint.

See also  CAML Query Builder SharePoint Online + Examples

You may also like:

  • Hello, I have been trying to ‘Get” list data using this method and I can’t seem to get it to work. All I am trying to do is get “Announcements” list data to post to within bootstrap html card. can you show how this is done?

  • >