Get more than 5000 list items in SharePoint using Deferred Promises jQuery
How to more than 5000 list items in SharePoint using Deferred Promises jQuery
In the below post you will learn how we can get more than 5000 list items using REST api. As we know that in SharePoint, number of items we can fetch at one time which should not be more than 5000 items that default architect of SharePoint list says. But in some of the requirements we may need to get more than 5000 list items. When we Query large list by rest "/_api/web/lists/getbytitle ('<list title>')/items" on the list which has more than 5000 items, we get following exception "Request failed. The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator".
So to resolve the above error you will learn to how code to get more than 5000 records.
Code -
var TotalCAPItemCount = 0;
$(window).load(function() {
var ItemCount = GetItemCount('CAP');
createRestUrl(ItemCount,'CAP');
});
//Step 1. get total item count of threshold exceed list.
function GetItemCount(ListName){
var ItemCount='';
$.ajax({
url: _spPageContextInfo.webAbsoluteUrl +"/_api/web/lists/GetByTitle('"+ListName+"')/ItemCount",
method: "GET",
async: false,
headers: { "Accept": "application/json; odata=verbose" },
success: function (data) {
ItemCount = data.d.ItemCount;
},
error: function (data) {
console.log(data);
}
});
return ItemCount;
}
//Step 2. create rest service url if item count is above 5000 then we split calling with 1000 item per call top=1000 for better performance. Otherwise we call as usual 5000 items top=5000
function createRestUrl(ItemCount, ListName) {
if(ItemCount<=5000) {
//Item count less than 5000 so we limit it as usual 5000
var listServiceUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$top=5000";
}
else {
//Item count more than 5000 so we split it in 1000 item per call
var listServiceUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$top=1000";
}
//Step 3: Rest call to procerss each items of list
$.when(processList(listServiceUrl,ItemCount)).done(function () { });
}
//Step 3: Rest call to procerss each items of list
function processList(nextUrl,ItemCount) {
var dfd = new $.Deferred();
if (nextUrl == undefined) {
dfd.resolve();
return;
}
//Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object.
getJSONDataFromUrl(nextUrl).done(function (listItems) {
TotalCAPItemCount = TotalCAPItemCount + listItems.d.results.length;
var items = listItems.d.results;
var next = listItems.d.__next;
$.when(processList(next,ItemCount)).done(function (){
dfd.resolve();
});
var tableHTML='';
//Create datatable object
var table = $('#tblResourcesData').DataTable({
dom: 'Bfrtip',
buttons: [{
extend: 'pdf',
text: 'Export to PDF'
}, {
extend: 'excel',
text: 'Export to Excel'
}]
});
$.each(items, function(index, obj){
tableHTML +='<tr><td>'+obj.Title+'</td><td>'+obj.Solution+'</td></tr>';
});
table.rows.add($(tableHTML)).draw(); //Append each list row to data tabel
});
return dfd.promise();
}
//Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object.
function getJSONDataFromUrl(endpoint) {
return jQuery.ajax({
url: endpoint,
method: "GET",
headers: {
"Accept": "application/json; odata=verbose",
"Content-Type": "application/json; odata=verbose"
}
});
}
Sharepoint Interview Questions and Answers
ReplyDelete