REST api querying large list

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.

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

If you like to include additional fields in the $Select just make sure you have column index defined for those columns.