Display Task List data in a table using SharePoint REST API and filter by status column

display sharepoint list data rest api filter
SharePoint deveopment training course

Here I explained, how to populate SharePoint Task list data in a table using SharePoint REST API and filter by status column in SharePoint Online/2013/2016. Here we will use jQuery, Bootstrap, moment JS with Rest API code in SharePoint. Also, I will show you can export the data to ex

If you are new to SharePoint Rest API, check out an article on Create, Update and Delete SharePoint List using Rest API in SharePoint 2013/2016/Online.

Step-1: Here I have created a SharePoint list and add the columns that same as the below list. Here Ticket no is a calculated column and Ticket_no: OrderName is a lookup column for other lists.

sharepoint 2013 rest api example
sharepoint 2013 rest api example

Step-2: Go to the SharePoint page and create a new .aspx page (Web part page).

Step-3: Edit the page and Add a script editor Web Part in SharePoint web part page.

Step-4: Add the jQuery, Bootstrap, moment JS and export to excel libraries in your code.

Step-5: Add the below code in your page.

<script type="text/javascript" src="https://canvasjs.com/assets/script/jquery-1.11.1.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js"></script> 
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
<link href="//netdna.bootstrapcdn.com/bootstrap/3.0.3/css/bootstrap.min.css" rel="stylesheet">
<script src="https://netdna.bootstrapcdn.com/bootstrap/3.0.3/js/bootstrap.min.js"></script>
<script type='text/javascript' src='//code.jquery.com/jquery-1.8.3.js'></script>
<script src="//cdn.rawgit.com/rainabba/jquery-table2excel/1.1.0/dist/jquery.table2excel.min.js"></script>

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.5.0/css/bootstrap-datepicker3.min.css">
<script type='text/javascript' src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.5.0/js/bootstrap-datepicker.min.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<link rel="stylesheet" type="text/css" href="https://code.jquery.com/ui/1.12.0/themes/smoothness/jquery-ui.css">


<style>
body {
    padding: 15px;
}

table {
    border-collapse: separate;
    border-spacing: 0 5px;
}

thead th {
    background-color: #006DCC;
    color: white;
}

tbody td {
    background-color: #EEEEEE;
}

tr td:first-child,
tr th:first-child {
    border-top-left-radius: 6px;
    border-bottom-left-radius: 6px;
}

tr td:last-child,
tr th:last-child {
    border-top-right-radius: 6px;
    border-bottom-right-radius: 6px;
}
.input-group { 
    padding-bottom: 100px;
}
.row1 {
    margin-top: 15px;
}
</style>
				
	<script type="text/javascript">
    jQuery(document).ready(function () {
        var testdata = [];
        var html = "";
        var taskstatus = "";
		loadMyItems();
		$("#getMonthData").click(function(){
			    $('#tlbtaskDetails').find('tbody').empty();
				
				loadMyItems();				
			});
			$("#getExcelData").click(function(){

			$("#tlbtaskDetails").table2excel({

				// exclude CSS class                 
				name: "Facility Work Order Report",
	    		filename: "Completed Task Report" //do not include extension

            });			
            });
		});
		function loadMyItems() 
		{
		var SearchDate=document.getElementById('startMonth').value;

			siteUrl = _spPageContextInfo.siteAbsoluteUrl ; 
			if(SearchDate == 'undefined' || SearchDate == '' || SearchDate == undefined )
			{
				var RestUrl = siteUrl + "/_api/web/lists/GetByTitle('Assign Task')/items?$select=Ticket_no/WorkOrderNo,Ticket_no/Created,Ticket_no/OrderName,AssignedTo/Title,StartDate,Completed_x0020_Date,Duration,Status&$expand=Ticket_no/WorkOrderNo,Ticket_no/OrderName,AssignedTo/Id&$filter=Status eq 'Completed' ";   
			}
			else
			{
				var RestUrl = siteUrl +"/_api/web/lists/GetByTitle('Assign Task')/items?$select=Ticket_no/WorkOrderNo,Ticket_no/Created,Ticket_no/OrderName,AssignedTo/Title,StartDate,Completed_x0020_Date,Duration,Status&$expand=Ticket_no/WorkOrderNo,Ticket_no/OrderName,AssignedTo/Id&$filter=(M_Y eq '"+SearchDate+"') and (Status eq 'Completed')";   

			}
        jQuery.ajax({
            url:RestUrl,
            type: "GET",     
            headers: { "Accept": "application/json;odata=verbose" },
            success: function (data) {
                 var html = "";
                 html += "<tbody>";
                    
                jQuery.each(data.d.results, function (index, value) {
				var startDate = value.StartDate;
                startDate = new Date(startDate).toUTCString();
                startDate = startDate.split(' ').slice(0, 4).join(' ');
				
				var EndDate = value.Completed_x0020_Date;
                EndDate = new Date(EndDate).toUTCString();
                EndDate = EndDate.split(' ').slice(0, 4).join(' ');
				
				var IssuedDate = value.Ticket_no.Created;
                IssuedDate = new Date(IssuedDate).toUTCString();
                IssuedDate = IssuedDate.split(' ').slice(0, 4).join(' ');
		
               var AssigUser="";
                var days=value.Duration;
				var time = days.slice(0, 5);    
                  jQuery.each(value.AssignedTo.results, function (i, Uservalue) {
		if(value.AssignedTo.results.length == 1)
		{
		AssigUser+=Uservalue.Title;
			}
			else
			{
		if(value.AssignedTo.results.length==i & value.AssignedTo.results.length >1)
		{
		AssigUser+=Uservalue.Title;
		}
		else
		{
		AssigUser+=Uservalue.Title+",";
		}
		}		
		})
                html += "<tr><td>" + value.Ticket_no.WorkOrderNo + "</td><td>" + value.Ticket_no.OrderName + "</td><td>" + IssuedDate + "</td><td>" + AssigUser + "</td><td>" + startDate+ "</td><td>" + EndDate + "</td><td>" + time +" hours"  + "</td><td>" + value.Status + "</td></tr>"
                   
                });
                 html += "</tbody>";
                 $('#tlbtaskDetails').append(html);                

            },
            error: function (data) {
                //output error HERE
                alert(data.statusText);
            }
        });		
	}
	$(function() {
    $('#startMonth').datepicker( {
        changeMonth: true,
        changeYear: true,
        showButtonPanel: true,
        dateFormat: 'MM-yy',
        onClose: function(dateText, inst) { 
            $(this).datepicker('setDate', new Date(inst.selectedYear, inst.selectedMonth, 1));
        }
    });
});	
</script>

 <div class="container">
 <div class="row">
<div class="col-sm-3">
 <input type="text" class="form-control" id="startMonth" placeholder="Choose a Month" >
</div>
<div class="col-sm-2">
<button type="button" class="btn btn-success btn-md btn3d" id="getMonthData"><span class="glyphicon glyphicon-ok"></span> Show</button>
</div>
<div class="row">
<div class="col-sm-2">
<button type="button" class="btn btn-danger clearfix" id="getExcelData"><span class="fa fa-file-excel-o"></span> Export to Excel</button>
</div>
</div>
<div class="row1">
               
         
                            <table class="table" id="tlbtaskDetails">
                                <thead class="black white-text">
                                    <tr>
                                        <th scope="col">Ticket No</th>
										<th scope="col">Task Name</th>
										<th scope="col">Task Issued Date</th>
                                        <th scope="col">Assigned User</th>
                                        <th scope="col">Task Start Date</th>
                                        <th scope="col">End Date</th>
                                        <th scope="col">Total Hours</th>
                                        <th scope="col">Status</th>

                                    </tr>
                                </thead>
                            </table>
                        </div>
						
</div>

Step-6: Next, you will get the SharePoint list data output in a tabular format.

sharepoint 2013 rest api get list items examples
sharepoint 2013 rest api get list items examples

Step-7: You can also filter the item based on month which you have to provide in the above text box.

sharepoint 2016 rest api example
sharepoint 2016 rest api example

Step-8: Here you can export this list data to excel by clicking above button.

sharepoint 2013 rest api javascript example
sharepoint 2013 rest api javascript example

Step-9: You will get the exported file like this.

sharepoint 2016 rest api javascript example
sharepoint 2016 rest api javascript example

Step-10: This is the whole functionality of this article.

Note: You can update the jQuery references, if are facing any issues while executing this code.

You may like following SharePoint client side object model tutorials:

Hope this SharePoint tutorial explains, how to display SharePoint list data using a HTML table using Rest API.

Check out Best Alternative to InfoPath -> Try Now

free sharepoint training

SharePoint Online FREE Training

JOIN a FREE SharePoint Video Course (3 Part Video Series)

envelope
envelope

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 →
  • hi, I have followed but there is an error, “Not Found” not sure what is this error for? When it export to Excel; can not open the file, any idea what is going on? I am new to JS, please advise
    Thank you