How to bind SharePoint Online list data using HTML and jQuery table using Rest API

In this SharePoint online tutorial, we will discuss how to bind SharePoint Online list data using HTML and jQuery table using Rest API.

Also, I will show how to display SharePoint Online list data using the jQuery data table and Rest API in SharePoint Online/2013/2016.

Display SharePoint list data in HTML table using Rest API

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

List Columns:

• Title
• Course
• Comments
• TrainerName
• TrainingType

And the list has few data like below:

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.

Display SharePoint list data in grid or HTML table using Rest API (Without People Picker Control)

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 which you can add inside a script editor web part inside a SharePoint Online web part 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:

Display SharePoint list data in grid or HTML table using Rest API (With People Picker Control)

Example-2: (With People picker column)

Here we will see how to retrieve people picker column value using Rest API and display 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";

And 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 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 jQuery table in SharePoint 2013/2016 or SharePoint Online.

What is DataTables and how to use DataTables?

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 provides 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)

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

The full path can be 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 we can 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:

Now, we will see how can we display the list data using grid and datatables using Rest API in SharePoint Online/2013/2016.

Below is the code which you can use inside a script editor web part or also you can use in 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 List Data using Rest API

Now, let us see another example of how to display SharePoint list data using Rest API.

Here I have a SharePoint Online list which has columns like LinkName and URLPath and has been added a few rows of data in that. The data looks like below:

Our requirement was to display the data on the dashboard page and with some attractive UI. So we will use Rest API to retrieve the list data from the SharePoint Online list and then we will do some branding like we will add some CSS to HTML elements so that the look and feel will be good.

Below are some prerequisites, which are required before we jump in to implement the below code.

• https://jquery.com/download/ – To get latest jquery.min.js
• Any IDE that supports HTML and JS i.e. Notepad++, VS 2015.
• Little knowledge about CSS, HTML, JS and REST API

HTML File

In the below HTML file make sure to add below 2 major references as discussed above in <Head> section.

<head>
<Script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<link rel="stylesheet" href="https://onlysharepoint2013.sharepoint.com/sites/Bhawana/SiteAssets/CSS/stylesheet.css">
</head>

CSS styles (stylesheet.css)

You can also use the below styles inline into the same HTML page or else create a CSS file include the below changes and refer to the file. Here I have created a .css file with name as stylesheet.css and stored it inside SiteAssets document library under the CSS folder.

.title-div-leftBlue
{
margin: 20px 10px;
border-bottom: .625rem solid mediumblue;
text-align: left;
padding: 10px 5px;
display: table;
}

.red-button1
{
background:#dd1d21;
color:#ffffff !important;
width: 200px !important;
text-align: center;
padding: 10px 20px;
display: block;
font-weight: bold;
margin-bottom: 10px;
}
.red-button1:hover
{
background:#fbce07;
color:#dd1d21 !important;
border-color:#dd1d21 !important;
width: 200px !important;
text-align: center;
padding: 10px 20px;
display: block;
font-weight: bold;
margin-bottom: 10px;
}

Implementation Details

Things to understand before implementation

• ExecuteOrDelayUntilScriptLoaded(startIt, “sp.js”); – This method will wait until sp.js is loaded into the page and then trigger main function ‘startit’
• onQuerySucceeded– Execute in case of successful execution. It has main logic implemented.
• onQueryFailed- Execute in case of any exception.
• GET – REST Api method to fetch the data from the list.
• Accept”: “application/json;odata=verbose – REST Api header defined.
• _spPageContextInfo.webAbsoluteUrl – This will return a web absolute URL of the site. In our case it will return “https://onlysharepoint2013.sharepoint.com/sites/Bhawana/”
• _spPageContextInfo.webAbsoluteUrl + /_api/web/lists/getByTitle(‘ImportantLinks’)/items?$select=LinkName,URLPath&$top=6&$orderby=Created”

REST API endpoint defined to fetch the data from the ‘ImportantLinks’ list, here in this endpoint we are taking only top=6 items ordered by Created so we can restrict no of records we want to fetch at a time, the max limit is 100.

You can also run the endpoint and see the output in the browser as below.

https://onlysharepoint2013.sharepoint.com/sites/Bhawana/_api/web/lists/getByTitle(‘ImportantLinks’)/items?$select=LinkName,URLPath&$top=6&$orderby=Created

HTML Code (usefullLink.html)

Below is the HTML file:

<html>
<head>
<script src=”https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js”></script>
<link rel=”stylesheet” href=”https://onlysharepoint2013.sharepoint.com/sites/Bhawana/SiteAssets/CSS/stylesheet.css”>
<script>
ExecuteOrDelayUntilScriptLoaded(startIt, “sp.js”);
function startIt() {
var fullUrl = _spPageContextInfo.webAbsoluteUrl + “/_api/web/lists/getByTitle(‘ImportantLinks’)/items?$select=LinkName,URLPath&$top=6&$orderby=Created”;
$.ajax({
url: fullUrl,
type: “GET”,
headers:
{
“accept”: “application/json;odata=verbose”,
“content-type”: “application/json;odata=verbose”,
},
success: onQuerySucceeded,
error: onQueryFailed
});
}
function onQuerySucceeded(data)
{
var listItemInfo = ”;
var listItemImagePath = ”;
$.each(data.d.results, function (key, value)
{
listItemInfo += ‘<a class=”red-button1″ href=’+ value.URLPath +’>’+ value.LinkName +'</a><br/>’;
});
$(“#linkApp”).html(listItemInfo);

}
function onQueryFailed()
{
alert(‘Error!’);
}
</script>
</head>
<div class=”title-div-leftBlue”>
<h1> Important Scripting Links</h1>
</div>
<p>
<div id=”linkApp” class=”row”>
</div><br>
</p>
</html>

The above file I have to save inside the SharePoint Site Pages document library.

Page URL- /sites/Bhawana/SiteAssets/SitePages/usefullLink.html

Now we need to provide a reference to the file inside a content editor web part which we can add inside a web part page. Edit the web part page and add a content editor web part inside it.

Then edit the content editor web part and in the Content Link, give the above HTML file path.

Once you save the above changes in the content editor webpart property and save the page, you can see the below output. The main advantage of this module is once this is set up you just need to add or modify data in the list rest it will reflect below. Therefore, maintenance would be easy.

On click, it will navigate to the linked item.

This is how to display SharePoint Online list items using Rest API.

Read some SharePoint Rest API tutorials:

• Update People Picker Group field using Rest API and jQuery in SharePoint 2013
• Send Email with jQuery and REST API in SharePoint
• SharePoint CRUD operations using Knockout.js
• Rest API filter list items created by logged in user SharePoint online or SharePoint 2013
• SharePoint Online Branding: Display List items into page using AngularJS and REST Api in SharePoint online or SharePoint 2016/2013
• Rating and Generate File plan Report in SharePoint Online/2013/2016 Document Library
• Working with Survey List in SharePoint 2016/2013 or SharePoint Online
• Add multiple Office templates as a content type to a document library in SharePoint Online/2013/2016
• Change SharePoint Online/2013/2016 List Column Order

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 Online/2013/2016.