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) {

  var deferred = deferred || $q.defer();
  var results = results || [];
  results.data = results.data || [];

  $http(request).then(function(response) {

    if (!results.data.d) {
      results.data = response.data;
    } else {
      results.data.d.results = results.data.d.results.concat(response.data.d.results);
    }

    if (response.data.d.__next) {
      request.url = response.data.d.__next;
      self.getAllItems(request, results, deferred);
    } else {

      deferred.resolve(results);
    }

  });

  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 http://ift.tt/2fFFS6Y

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!

by Marc D Anderson via Marc D Anderson's Blog