How to read more than 5000 list items in office 365/ SharePoint 2013

This post was most recently updated on August 5th, 2024

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. So sometime there may be the condition where list reaches 5000 item and need to load all list item on custom dashboard page. In this case getting all 5000 item on the page is problematic and page load performance gets affected.

When list/view reach 5000 items following friendly error massage is arise in list information.

When we Query large list by rest “/_api/web/lists/getbytitle (‘<list title>’)/items” on the list which has more than 5000 items, you will get following exception “Request failed. The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator”.

Below I share code to load threshold exceed list on page with better page loading performance.

Steps: Page level

Create page and add following datatable css and js.
<link rel=”stylesheet” type=”text/css” href=”https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css” />
<script src=”https://code.jquery.com/jquery-1.12.4.js” type=”text/javascript”></script>
<script type=”text/javascript” src=”https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js”></script>

Create table with header according to SharePoint list.

<table id="table_id" class="display" role="grid" width="100%" cellspacing="0">  
        <thead>  
            <tr>  
                <th>ID</th>  
                <th>Country</th>
                <th>City</th>
		   <th>Profession</th>
            </tr>  
        </thead> 
</table>

<thead>

<tr>

<th>Country</th>

<th>Profession</th>

</tr>

</thead>

</table>

Steps: JS Level

Step 1: We need to get total item count from the list in order to read list data in the number of batches. We are using GetItemCount () function to get the total item count.

var TotalItemCount=0;

$(document).ready(function () { //UPDATED CODE START

 //Remove datatable warnings on loading
 $.fn.dataTable.ext.errMode = 'none';

    var siteurl = _spPageContextInfo.webAbsoluteUrl;
    
    var ItemCount= GetItemCount(siteurl, 'SiteTile');
    
    createRestUrl(siteurl,ItemCount,'SiteTile');
});  //UPDATED CODE END

//Step 1. get total item count of threshold exceed list.
function GetItemCount(siteurl, ListName){
     var ItemCount='';
     $.ajax({
           url: siteurl+"/_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;   
}

var TotalItemCount=0;

$(document).ready(function () { //UPDATED CODE START

//Remove datatable warnings on loading

$.fn.dataTable.ext.errMode = 'none';

var siteurl = _spPageContextInfo.webAbsoluteUrl;

var ItemCount= GetItemCount(siteurl, 'SiteTile');

createRestUrl(siteurl,ItemCount,'SiteTile');

}); //UPDATED CODE END

//Step 1. get total item count of threshold exceed list.

function GetItemCount(siteurl, ListName){

var ItemCount='';

$.ajax({

url: siteurl+"/_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: Next step create rest service url by splitting rest call according to item count of GetItemCount () function. Check item count 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 i.e. top=5000.

//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(siteurl, ItemCount, ListName) {

        if(ItemCount<=5000) {
		//Item count less than 5000 so we limit it as usual 5000
        	var listServiceUrl = siteurl+ "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=ID,Country,City,Profession&$top=5000";
	} else {
		//Item count more than 5000 so we split it in 1000 item per call
		var listServiceUrl = siteurl+ "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=ID,Country,City,Profession&$top=1000";
        }
        
	//Step 3: Rest call to procerss each items of list
       	$.when(processList(listServiceUrl,ItemCount)).done(function () { });
}

//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(siteurl, ItemCount, ListName) {

if(ItemCount<=5000) {

//Item count less than 5000 so we limit it as usual 5000

var listServiceUrl = siteurl+ "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=ID,Country,City,Profession&$top=5000";

} else {

//Item count more than 5000 so we split it in 1000 item per call

var listServiceUrl = siteurl+ "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=ID,Country,City,Profession&$top=1000";

}

//Step 3: Rest call to procerss each items of list

$.when(processList(listServiceUrl,ItemCount)).done(function () { });

}

Step 3: Process each item of threshold list by repeatedly calling getJSONDataFromUrl () function. GetJSONDataFromUrl () function gives Ajax json object.

//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) {
        
        	TotalItemCount = TotalItemCount+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 = $('#table_id').DataTable();     
             $.each(items, function(index, obj){
			tableHTML +='<tr><td>'+obj.ID+'</td><td>'+obj.Country+'</td><td>'+obj.City+'</td><td>'+obj.Profession+'</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"
            }
        });
}

//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) {

TotalItemCount = TotalItemCount+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 = $('#table_id').DataTable();

$.each(items, function(index, obj){

tableHTML +='<tr><td>'+obj.ID+'</td><td>'+obj.Country+'</td><td>'+obj.City+'</td><td>'+obj.Profession+'</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"

}

});

}

Final Screen shot

Click here to follow the steps to add Column level filters in the data table

Reference:

https://sharepoint.stackexchange.com/questions/217254/rest-to-read-more-than-5000-items-from-document-library/217451#217451

This Article is TAGGED in SharePoint List or Library, SharePoint REST api, Threshold list. BOOKMARK THE permalink.

15 thoughts on “How to read more than 5000 list items in office 365/ SharePoint 2013”

If i add any filter parameters it’s throwing me error. Eventhough filtered columns are being indexed.

It’s very urgent can you help please.

TechBytes Admin says:

December 2, 2020 at 12:55 pm

It will fail for large list with filter event columns is indexed, So avoid filter get all the data and filter the object using JavaScript. Hope this will helps.

Reply

Please tell us how to add export button like excel,pdf etc in above example.

I tried below code but its giving error ” Cannot perform DeepCopy() because a circular reference was encountered”

var table = $(‘#table_id’).DataTable({
buttons: [
‘copy’, ‘csv’, ‘excel’, ‘pdf’, ‘print’
]
} );

TechBytes Admin says:

September 5, 2018 at 11:54 am

Hello Mala,
We are using Datatable, we have to check whole code for that error. You can check here for print https://datatables.net/extensions/buttons/examples/initialisation/export.html.
Maybe this link will help you https://forums.asp.net/t/1223398.aspx?circular+reference+error+when+returning+DataTable+from+WebService

Thank You for the comment.

Reply

Thanks for your response but i am able to get data by adding “aaData”: items before line

“aoColumns”: [{
“mData”: “ParentDocName”
}, {
“mData”: “ChildDocNames”
}, {
“mData”: “ParentDocId”
}, {
“mData”: “ParentListName”
}, {
“mData”: “Modified”,

but again a problem is that table got initialize each time function runs. Please provide me solution that can stop repeat initialization of Data table and can get data

TechBytes Admin says:

December 18, 2017 at 1:19 pm

Thank you, Keshav for your comment. We updated whole code to process of fetching list items. I hope this will help you.

Reply
1. keshav says:
  
  December 20, 2017 at 10:40 pm
  
  Thanks, the code is working fine but since I have >50k items in the list so getting all items one by one in internet explorer got crashed. Since you have good knowledge of Jquery, Is it possible to get only those items in first page which are selected for a page like 10 items in your screenshot and again 10 items in second pagination page
  
  Reply
  1. TechBytes Admin says:
    
    December 21, 2017 at 11:53 am
    
    Thank you, You can achieve by making the request for pagination and reduce the item limit.
    
    Reply

Hi I am not getting any data. Please help me if anything wrong

MYCODE:

$(document).ready(function () {
loadMyItems();
});

}]
});
}
});
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”
}
});
}

Hi, I am not getting any data. Please help

TechBytes Admin says:

December 15, 2017 at 1:12 pm

Thank you, Keshav for your comment. Check updated code with comments. Hope it solves your problem.

$(document).ready(function () {
loadMyItems();
});

function loadMyItems() {
var siteurl = _spPageContextInfo.webAbsoluteUrl; //CHANGE siteAbsoluteUrl TO webAbsoluteUrl
var RestUrl = siteurl + “/_api/web/lists/getbytitle(‘BP_EDP_GDMP_RELATIONSHIPLIST’)/items?$select=ParentDocName,ChildDocNames,ParentDocId,ParentListName,Modified&$top=5000″;
var ItemCount = ”;

$.ajax({
url: siteurl + “/_api/web/lists/getbytitle(‘BP_EDP_GDMP_RELATIONSHIPLIST’)/ItemCount”,
method: “GET”,
async: false,
headers: { “Accept”: “application/json; odata=verbose” },
success: function (data) {
ItemCount = data.d.ItemCount;
},
error: function (data) {
console.log(data);
}
});
var date = new Date();//GET DATE FROM DATE OBJECTS
console.log( date.getMonth()+1+”/”+date.getDate() + “/” + date.getFullYear()); // Get full date format
}

Reply

Excellent!
Question: getLargeList function missing?

TechBytes Admin says:

August 22, 2017 at 4:09 pm

Gilbert,
Thanks! I updated this article please check.

Reply
1. Gilbert says:
  
  August 27, 2017 at 7:46 am
  
  Thanks.
  One more thing: The processList function re-initializes the datatables and throwing the following error: DataTables warning: table id=table_id – Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3
  
  Reply
  1. TechBytes Admin says:
    
    September 20, 2017 at 4:16 pm
    
    Thank you Gilbert for your valuable comments.
    
    Reply

How to read more than 5000 list items in office 365/ SharePoint 2013

Get in Touch

15 thoughts on “How to read more than 5000 list items in office 365/ SharePoint 2013”

Leave a Reply Cancel reply

About Us

Get in Touch

Related articles

How to create View?

PowerApps send email notification

How to create List using List Template?

15 thoughts on “How to read more than 5000 list items in office 365/ SharePoint 2013”

Leave a Reply Cancel reply