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.

RESTResult

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.

Advertisements
This entry was posted in SharePoint 2013 and tagged , . Bookmark the permalink.

One Response to REST api querying large list

  1. Egidio Caleiro Santoro egidiocs says:

    Well, using odata=verbose in the Accept header, exponetially “kbfies” the response. Use odata=nometadata if you don’t need to do any metadata related task with your data. Check this out: JSON Light support in REST SharePoint API released https://blogs.office.com/2014/08/13/json-light-support-rest-sharepoint-api-released/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s