This SharePoint rest api tutorial explains, how to get all SharePoint list items using Rest API and then how to display SharePoint list items in a data table using Rest API and jQuery in SharePoint Online or SharePoint 2013/2016.
We will see how to get all list items in sharepoint using rest api.
Here, we will use the datatables plug-in to display the SharePoint list items in a tabular format.
SharePoint Tutorial Contents
DataTables
DataTables is a plug-in build on top of jQuery to enhance any HTML table with its own advanced features. Without writing any code, you can enhance your HTML tables like you can have paging, search, formating, etc.
To work the DataTables JavaScript library or plug-in, you just require to refer the following .css and .js file.
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.js"></script>If you do not want to provide the path from the CDN, you can also download and save it in your site and refer it from there.
Now, we will see how to get all list items in sharepoint using rest api and then display in a tabular format.
Here, I have a SharePoint Online list which has few columns like:
- Title
- Gender
- ContactNumber
- Department
- Address
And the SharePoint list looks like below:
Now, we will see how to display these SharePoint list items in a data table using Rest API, HTML, jQuery, etc in a web part page in SharePoint.
Here I will use a content editor web part to add the code into the web part page in SharePoint. So here, I have added two separated file, one is the HTML file which contents the HTML code and the other one is a js file which contains our JavaScript and Rest API code.
Both the files I have uploaded to the SiteAssets library in the SharePoint Online site.
HTML Code:
<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/SharePointSky/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>RetriveListItemsRestAPI.js Code
$(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);
}Then add a content editor web part to the web part page and give the HTML file reference like below:
Once you save the page, you can see the list data will appear in the datatable like below:
If you are familiar with the script editor web part, then you can add a script editor web part in the SharePoint web part page. Then add the below code in the script editor web part.
<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>
<!–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">
<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>
<script>
$(document).ready(function() {
GetItems();
});
function GetItems() {
var siteUrl = _spPageContextInfo.webAbsoluteUrl;
var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('EmployeesList')/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);
}
</script>The result will come like below:
Here I am retrieving list items from the default Announcements list using Rest API. The Announcements list has 3 item which we will retrieve using REST API.
Put the below code inside a Script editor web part and save the page.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script>
$(function(){
$("#btnClick").click(function(){
var requestUri = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle('Announcements')/items";
$.ajax({
url: requestUri,
type: "GET",
headers: {
"accept":"application/json; odata=verbose"
},
success: onSuccess,
error: onError
});
function onSuccess(data) {
var items = data.d.results;
for (var i = 0; i < items.length; i++) {
alert(items[i].Id+ " : " + items[i].Title);
}
}
function onError(error) {
alert(JSON.stringify(error));
}
});
});
</script>
<input type="button" id="btnClick" value="Get all List Items"/>When you will click on the button, it will display all the items like below:
Now we will see how we can retrieve a particular item from a SharePoint 2013 list using REST API. Here we will use to getitembyid(id) to retrieve the particular item from the list.
In this example, I have a list where I have 3 columns name as Title, FirstName and LastName.
You can write the below code in a Script Editor web part or content editor web part and Save the web part page.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script>
$(function(){
$("#btnClick").click(function(){
var requestUri = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('bijayTestList')/getitembyid(1)";
var requestHeaders = { "accept": "application/json;odata=verbose" };
$.ajax({
url: requestUri,
contentType: "application/json;odata=verbose",
headers: requestHeaders,
success: onSuccess,
error: onError
});
function onSuccess(data, request) {
var listiteminfo = data.d;
alert('Title: '+listiteminfo.Title + ' First Name:'+listiteminfo.FirstName +' Last Name: '+listiteminfo.LastName);
}
function onError(error) {
alert("error");
}
});
});
</script>
<input type="button" id="btnClick" value="Click Here"/>When you click on the button, the result will come like below:
You may like following SharePoint Rest API tutorials:
- Create custom SharePoint Survey poll using REST API and AngularJS
- Create a custom User profile web part using SharePoint REST API and bootstrap
- Retrieve and Display TASK status using REST API in SharePoint
- Create a Custom Calendar in SharePoint using Rest API and jQuery
- Create Highcharts in SharePoint using Rest API
- Update People Picker Group field using Rest API and jQuery in SharePoint 2013/2016
- Testing SharePoint 2016 or 2013 or SharePoint Online REST API using Postman
- Get All Attachments From SharePoint 2013 List Item using Rest API
- Coronavirus (COVID 19) Tracker in SharePoint Online
- Bind SharePoint list items to dropdownlist using Rest api and jQuery
I hope this SharePoint tutorial helps to get all SharePoint list items using Rest API and display SharePoint list items in a datatables plug-in using Rest API and jQuery in SharePoint Online or SharePoint 2013/2016. Also, we saw how to get all list items sharepoint rest api.
I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SPGuides.com
[…] Display SharePoint List items in a data table using Rest API and jQuery […]
[…] Display SharePoint List items in a data table using Rest API and jQuery […]
[…] Display SharePoint List items in a data table using Rest API and jQuery […]
Hello, I’m so confused.
How do the code know which list we want to display. I can’t see the link to the list.
Thank you.
In the below line you can see the list name:
var oDataUrl = siteUrl + “/_api/web/lists/getbytitle(‘MyCompanyEmployeeList’)/items?$select=Title,Gender,ContactNumber,Department,Address”;
Here MyCompanyEmployeeList: Is my list name, you can change according to your list name.
Not sure why I can’t get the list. I just got the header for the table but not the list.. Do I need to change anything else besides the list name and JS file in HTML file?
You have to change the column names as per your list.
Items is not filtering after 5000, Can you please assist me if I have more than 5000 items in SharePoint List.