SharePoint REST API to retrieve more than 5000 items

Please refer below code :

Below is the REST Api url to get item count in a list using spHttpClient GET method

{YourSiteCollectionUrl}/_api/web/Lists/GetByTitle('YourListTitle')/ItemCount

Here we are doing recursive call to the GetListItems() function. This will fetch data in the bunch of 1000 items and concat it to response variable.

var url = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('DocumentList')/items?$select=customerID&$top=1000";
    var response = response || [];  // this variable is used for storing list items
    function GetListItems(){
        return $.ajax({
            url: url,  
            method: "GET",  
            headers: {  
                "Accept": "application/json; odata=verbose"  
            },
            success: function(data){
                response = response.concat(data.d.results);
                if (data.d.__next) {
                    url = data.d.__next;
                    GetListItems();
                }
                $.each(response, function(index, item) {
                    arrayCustomerID[index] = item.customerID;
                });
            },
            error: function(error){
            }
        });
    }

How often you have situation that our customers say that we will never ever have more than 100 items in our SharePoint list, then how quickly the list grows with 1000, 5000 items and finally it reach to a point that it exceed the list view threshold and our REST call don’t work anymore. I guess, it’s all most every time or?

Querying large list  “/_api/web/lists/getbytitle(‘<list title>’)/items”  that 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”.

In SSOM we have ContentIterator class but as for CSOM or JSOM the options are limited. I found solution at PnP but isn’t working for me. I have tried with Rowlimit and $filter and $top and nothing really worked. But finally I managed to find the right query.

/_api/web/lists/GetByTitle(‘largelist’)/Items?$Select=ID&$OrderBy=ID&$top=5

The above query give you top 5 elements ordered by ID. Note that ID is OOTB index column available in each SharePoint list.



In result, you will find a next URL that contains paging option, using that you can query next set of items. So loop until you don’t have next URL.




1


<link  rel="next" href="https://<yoursharepoint>/_api/web/lists/GetByTitle('largelist')/Items?%24skiptoken=Paged%3dTRUE%26p_ID%3d203&%24Select=ID&%24OrderBy=ID&%24top=5" />






In the sample code below I fetch 200 list items per page. I don’t recommend more than 200 items per page as it can affect the performance.

function getDataFromUrl(endpoint) {

     return jQuery.ajax({

         url: endpoint,

         method: "GET",

         headers: {

             "Accept": "application/json; odata=verbose",

             "Content-Type": "application/json; odata=verbose"

         }

     });

}

function getLargeList(nextUrl) {

     var dfd = new $.Deferred();

     if (nextUrl == undefined) {

         dfd.resolve();

         return;

     }

     getDataFromUrl(nextUrl).done(function (listItems) {

         var items = listItems.d.results;

         var next = listItems.d.__next;

 

         $.when(getLargeList(next)).done(function (){

             dfd.resolve();

         });

     });

     return dfd.promise();

}

function getListData {

     var documentLibName = 'LargeList';

     var listServiceUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('" + documentLibName + "')/Items?$Select=ID&$OrderBy=ID&$top=200";

 

     $.when(getLargeList(listServiceUrl)).done(function () {

 // Process data

     });

 }