SharePoint Blog: JSON

Query list data between two dates in SharePoint hosted app 2013 using REST API

Using below query i am getting data from last one month

DateTime is my date time column
PP is people picker column
Fetching 5000 items in one query
Fetching People picker and lookup column as well.

var hostweburl;
var appweburl;
var _listName;
var employeeListQueryUrl;
var EmployeesList = 'EmployeesList';
var EndDate = new Date();
var startdate = new Date();
startdate.setDate(startdate.getDate() - 30);

employeeListQueryUrl = appweburl + "/_api/SP.AppContextSite(@target)/web/lists/getbyTitle('" + EmployeesList + "')/Items?@target='" + hostweburl + "'&

$select=Title,DateTime,Email,ContactNo,Address,ID,PP/ID,PP/Title,Lookup/ID,Lookup/Title&$expand=PP/ID,PP/TitleLookup/ID,Lookup/Title&

$orderby=Created asc&

$top=5000&

$filter=DateTime ge datetime'" + startdate.toISOString() + "' and (DateTime le datetime'" + EndDate.toISOString() + "')"

Complete query:

employeeListQueryUrl = appweburl + "/_api/SP.AppContextSite(@target)/web/lists/getbyTitle('" + EmployeesList + "')/Items?@target='" + hostweburl + "'&$select=Title,DateTime,Email,ContactNo,Address,ID,PP/ID,PP/Title,Lookup/ID,Lookup/Title&$expand=PP/ID,PP/TitleLookup/ID,Lookup/Title&$orderby=Created asc&$top=5000&$filter=DateTime ge datetime'" + startdate.toISOString() + "' and (DateTime le datetime'" + EndDate.toISOString() + "')"

In this post i am going to describe how you can bind SharePoint list to Jquery datatable (just like grid view) with rich features like Export to excel, Export to PDF etc.

Key Points:

Querying people picker and lookup columns using REST API
Getting 5000 items in one query
Showing first column as hyperlink column to my list item display form.
Change column header name dynamically after page load
Export to excel, pdf, notepad, csv
List view search
Paging
This grid can be added as app part in sharepoint page as well (Same like list view webpart with rich features)

Thanks to this article.

Steps:

Create SharePoint hosted app using VS ( i used 2013).
Added below code to default.aspx page.
Add references to your datatable js files

4. Add HTML in div tag

initializing...

</p>

<thead>

<tr>

<th >Title</th>

<th>Email</th>

</tr>

</thead>

<tbody>

</tbody>

</table>

5. Created one js file and added below code, change column names and list name accordingly.

'use strict';

var hostweburl;

var appweburl;

var _listName;

var employeeListQueryUrl;

var EmployeesList = 'EmployeesList';

// This code runs when the DOM is ready and creates a context object which is

// needed to use the SharePoint object model

$(document).ready(function () {

//Get the URI decoded URLs.

hostweburl =

decodeURIComponent(

getQueryStringParameter("SPHostUrl"));

appweburl =

decodeURIComponent(

getQueryStringParameter("SPAppWebUrl"));

// Resources are in URLs in the form:

// web_url/_layouts/15/resource

var scriptbase = hostweburl + "/_layouts/15/";

// Using below string i am querying 5000 items and getting values of People picker column, lookup column as well.

employeeListQueryUrl = appweburl + "/_api/SP.AppContextSite(@target)/web/lists/getbyTitle('" + EmployeesList + "')/Items?@target='" + hostweburl + "'&$select=Title,LastName,Email,ContactNo,Address,ID,PP/ID,PP/Title,Lookup/ID,Lookup/Title&$expand=PP/ID,PP/TitleLookup/ID,Lookup/Title&$orderby=Created asc&$top=5000";

// Load the js file and continue to load the page with information about the list top level folders.

// SP.RequestExecutor.js to make cross-domain requests

// Load the js files and continue to the successHandler

$.getScript(scriptbase + "SP.RequestExecutor.js", execCrossDomainRequest);

});

// Function to prepare and issue the request to get

// SharePoint data

function execCrossDomainRequest() {

var executor;

executor = new SP.RequestExecutor(appweburl);

executor.executeAsync(

{

url: employeeListQueryUrl,

method: "GET",

headers: { "Accept": "application/json; odata=verbose" },

success: employeeSuccessHandler,

error: errorHandler

}

);

}

function employeeSuccessHandler(data) {

var jsonObject = JSON.parse(data.body);

var results = jsonObject.d.results;

fillData(results, EmployeesList);

$('#employeeList').dataTable({

"fnRowCallback": function (nRow, results, iDisplayIndex) {

$('td:eq(0)', nRow).html('<a target="_blank" href="' + hostweburl + '/Lists/' + EmployeesList + '/Dispform.aspx?ID=' + results[0] + '">' +

results[0] + '</a>');

//$('td:eq(0)', nRow).html('<a target="_blank" href="' + hostweburl + '/Lists/' + EmployeesList + '/Dispform.aspx?ID=' + results[0] + '"><img src="../Images/View_icon.png" border="0"/></a>');

return nRow;

//"createdRow": function (row, results, index) {

// if (results[2].indexOf('Y') == 1) {

// $('td', row).eq(2).addClass('highlight');

// }

//},

});

var table = $('#employeeList').dataTable();

var tableTools = new $.fn.dataTable.TableTools(table, {

aaSorting: [[4, 'desc']],

'aButtons': [

{

'sExtends': 'xls',

'sButtonText': 'Save to Excel',

'sFileName': 'Data.xls'

{

'sExtends': 'print',

'bShowAll': true,

{

'sExtends': 'pdf',

'bFooter': false

'copy',

'csv'

'sSwfPath': '//cdn.datatables.net/tabletools/2.2.4/swf/copy_csv_xls_pdf.swf'

});

document.getElementById('week1th').innerHTML = "Dynamic Column Name";

}

function errorHandler(data, errorCode, errorMessage) {

alert(data + ' >> ' + errorCode + ' >> ' + errorMessage);

}

function fillData(results, _ListName) {

var $appContent = $('#employeeList tbody');

for (var i = 0; i < results.length; i++) {

var $tr = $('<tr/>');

$('<td/>').text(results[i].ID).appendTo($tr);

$('<td/>').text(results[i].Title).appendTo($tr);

$('<td/>').text(results[i].Email + "Y").appendTo($tr);

$('<td/>').text(results[i].Lookup['Title']).appendTo($tr); // Set lookup text

$('<td/>').text(results[i].PP['Title']).appendTo($tr); // Set people picker value

$tr.appendTo($appContent);

}

// This function prepares, loads, and then executes a SharePoint query to get

// the current users information

//Utilities

// Retrieve a query string value.

// For production purposes you may want to use

// a library to handle the query string.

function getQueryStringParameter(paramToRetrieve) {

var params =

document.URL.split("?")[1].split("&");

for (var i = 0; i < params.length; i = i + 1) {

var singleParam = params[i].split("=");

if (singleParam[0] == paramToRetrieve)

return singleParam[1];

}

Final Output:

SharePoint Blog

Friday, 6 May 2016

Get Data between start date and end date in Sharepoint Rest API

Query list data between two dates in SharePoint hosted app 2013 using REST API

Thursday, 5 May 2016

Bind Sharepoint list to Jquery Datatable in SharePoint Hosted App

In this post i am going to describe how you can bind SharePoint list to Jquery datatable (just like grid view) with rich features like Export to excel, Export to PDF etc.