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.

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.

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!