Yes, Virginia, You Can Get More than 5000 SharePoint Items with REST

If you haven’t been paying any attention, you might not know that I loathe the 5000 item limit in SharePoint. I may have mentioned it here and here and here and a bunch of other places, but I’m not sure. But given it’s there, we often need to work around it.

No 5000 item limit

I’ve written this little function before, but alas today I needed it again but couldn’t find any previous incarnations. That’s what this blog is for, though: to remind me of what I’ve already done!

In this case, I’m working with AngularJS 1.x. We have several lists that are nearing the 5000 item level, and I don’t want my code to start failing when we get there. We can get as many items as we want if we make repeated calls to the same REST endpoint, watching for the __next link in the results. That __next link tells us that we haven’t gotten all of the items yet, and provides us with the link to get the next batch, based on how we’ve set top in the request.

Here’s an example. Suppose I want to get all the items from a list which contains all of the ZIP codes in the USA. I just checked, and that’s 27782 items. That’s definitely enough to make SharePoint angry at us, what with that 5000 item limit and all. Let’s not get into an argument about whether I need them all or not. Let’s just agree that I do. It’s an example, after all.

Well, if we set up our requests right, and use my handy-dandy recursive function below, we can get all of the items. First, let’s look at the setup. It should look pretty similar to anything you’ve done in an AngularJS service. I set up the request, and the success and error handlers just like I always do. Note I’m asking for the top 5000 items, using "&$top=5000" in my REST call.

self.getZIPCodes = function () {

  var deferred = $q.defer();

  var request = {
    method: 'GET',
    url: _spPageContextInfo.webAbsoluteUrl +
    "/_api/web/lists/getbytitle('ZIP Codes')/items?" +
    "$select=ID,Title" +
    "&$top=5000",
    headers: {
      "Accept": "application/json; odata=nometadata"
    }
  };
  var success = function (response) {

    angular.forEach(response.value, function (obj, index) {

      self.zipCodes.push({
        ZIPCode: obj.Title
      })

    });
    deferred.resolve(self.zipCodes);
  };

  var error = function (response) {
    deferred.reject(response.data);
  };

// This is the "normal" call, which would get us up to 5000 items
// $http(request).then(success, error);

// This gets us all the items, no matter how many there are.
  self.getAllItems(request).then(success, error);

  return deferred.promise;

};

If there are fewer than 5000 items, then we don’t have a problem; the base request would return them all. Line 32 is what would do that “normal” call. Instead, I call my recursive function below, passing in the request only, even though the function can take two more arguments: results and deferred.

// Recursively get all the items in a list, even more than 5000!
self.getAllItems = function(request, results, deferred) {

  // The first time through, these three variables won't exist, so we create them. On subsequent calls, the variables will already exist.
  var deferred = deferred || $q.defer();
  var results = results || [];
  results.data = results.data || [];

  // Make the call to the REST endpoint using Angular's $http
  $http(request).then(function(response) {

    // The first time through, we don't have any data, so we create the data object with the results
    if (!results.data.d) {
      results.data = response.data;
    } else {
      // If we already have some results from a previous call, we concatenate this set onto the existing array
      results.data.d.results = results.data.d.results.concat(response.data.d.results);
    }

    // If there's more data to fetch, there will be a URL in the __next object; if there isn't, the __next object will not be present
    if (response.data.d.__next) {
      // When we have a next page, we call this function again (recursively).
      // We change the url to the value of __next and pass in the current results and the deferred object we created on the first pass through
      request.url = response.data.d.__next;
      self.getAllItems(request, results, deferred);
    } else {
      // If there is no value for __next, we're all done because we have all the data already, so we resolve the promise with the results.
      deferred.resolve(results);
    }

  });

  // Return the deferred object's promise to the calling code
  return deferred.promise;

};

The recursive function simply keeps calling itself whenever it sees that the __next attribute of the response is present, signifying there is more data to fetch. It concatenates the data into a single array as it goes. In my example, there would be 6 calls to the REST endpoint because there are 27782 / 5000 = 5.5564 “chunks” of items.

Image from https://s-media-cache-ak0.pinimg.com/564x/16/bb/03/16bb034cb3b6b0bdc66d81f47a95a59f.jpg
Image from https://www.pinterest.com/pin/323062973239383839/

NOW, before a bunch of people get all angry at me about this, the Stan Lee rule applies here. If you have tens of thousands of items and you decide to load them all, don’t blame me if it takes a while. All those request can take a lot of time. This also isn’t just a get of of jail free card. I’m posilutely certain that if we misuse this all over the place, the data police at Microsoft will shut us down.

In actual fact, the multiple calls will be separated by short periods of time to us, which are almost eternities to today’s high-powered servers. In some cases, you might even find that batches of fewer than 5000 items may be *faster* for you.

In any case, don’t just do this willy-nilly. Also understand that my approach here isn’t as great at handling errors as the base case. Feel free to improve on it and post your improvements in the comments!

Similar Posts

11 Comments

  1. This looks really wonderful. In this case, you say you’re working with Angular.js
    Sorry, but if I don’t know Angular – does it require a lot of other calls/etc?
    Could this be accomplished just with, say jQuery?

    1. @George:

      You could modify this function to work with jQuery or any other mechanisms you might be using to call into the REST endpoint to get data. Angular sits above the REST calls and uses the data they return.

      M.

  2. Never ran into this pulling data using jquery.SPServices. Why do you not just use that? Or even just use a basic HTTP/AJAX request using the built-in SharePoint web services? All of this, to me, Angular included, is just bloated overkill and never had any use for it. The SharePoint Object Model also doesn’t apply this limit to reads. Only time I ever ran into the 5000 item limit was with updating the list via the GUI, or when you try updating it with Access, but even then, not if you do it during the defined maintenance window or are a Site Collection Admin. And inserting/updating wasn’t ever a problem with SPServices or Object Model, either-even outside the maintenance window and without being an SCA. One more reason for me to hate Angular, looks like, to me.

    1. @Tom:

      The 5000 item limit has nothing to do with Angular. It’s a restriction imposed by Microsoft in the REST endpoint.

      I’m not sure what you mean by the maintenance window, but the SOAP services – which you are calling when you use SPServices – have been deprecated by Microsoft. That means at some future point (which could be soon but likely is not) those services will no longer work.

      M.

  3. Hi Marc,

    This does not work if you apply filter and the result set is > 5000 items.

    The column on which I have applied filter is indexed.

    It only works if there is no filter.

    Any turnaround in case we are using filter?

    1. @Sunny:

      This code should work if you have more that 5000 items in the result set because it makes multiple calls for “pages” of 5000 items each. That’s the whole point her. I’m not sure why it isn’t working in your case.

      M.

    2. Sunny, try indexing the field you are filtering by using the list settings. Check out this Microsoft article for more information. It is intended for out of the box, but applies to rest from my experience:

      https://support.microsoft.com/en-us/office/manage-large-lists-and-libraries-in-sharepoint-b8588dae-9387-48c2-9248-c24122f07c59

      Mark, I’m a huge fan, but I can confirm Sunny’s experience. I entered the below two urls in my browser (no code). It is a 2013 environment with about 30k items in the list. The first works, the second exceeds list view threshold.

      /_api/web/lists/getByTitle(‘Case2’)/Items?$select=Title,ID&$top=3000

      /_api/web/lists/getByTitle(‘Case2’)/Items?$select=Title,ID&$top=3000&$filter=startswith(FirstName,%27a%27)

      When I need to filter I usually get the item count from the list endpoint and divide that by the list view threshold to establish chunks. Each chunk makes a filter using an id range 1 less than the threshold. The ID Column is indexed by default. That allows additional filtering since all results will be under the threshold. Also, this approach supports asynchronous options. Be sure to filter after the id range. SharePoint seems to process the query in order left to right and additional filters only work if the results are already under the threshold when the non-indexed filtering happens.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.