Sharepoint 2013 REST API not returning all items for a list

Question

The title states my problem quite exactly. If I try to gather all 400+ items from a list using sharepoint's REST API, I only get first 100.

I have read http://msdn.microsoft.com/en-us/library/office/dn292552(v=office.15).aspx and in the "Working with list items by using REST" part, they're stating that

The following example shows how to retrieve all of a list’s items.

url: http://site url/_api/web/lists/GetByTitle(‘Test')/items

method: GET

headers: ...

I have highlighted word all, because that's not what I'm getting ...

Am I missing something? Is there some option I should disable/enable to gett truly all items?

Thanks

Upcast

Could you show your own code? Are you retrieving items from the list or from a specific view? – Hobie

No code is necessary to see that I'm receiving only the first 100 items. I get only this amount of items just by visiting the URL in my browser (so just replacing the site url and Test in the http://site url/_api/web/lists/GetByTitle(‘Test')/items with my own values and I get XML with these 100 items) – Upcast

no code is necessary to see the result, but code might be necessary to understand why ;-) – Hobie

(re-reading my answer ... it might've sounded a bit offensive, sorry about that :P) Well ... then I'm bit confused. I don't really have a code for it. I just simply make a GET request in powershell with these headers $req.headers.add("X-FORMS_BASED_AUTH_ACCEPTED", "f"), $req.method = "GET" and $req.Credentials = [system.net.credentialcache]::defaultcredentials, that's pretty much it. – Upcast

I understand, I was actually referring to the REST url itself. It seems that you are using lists and not views. – Hobie

... yes ...? I don't really know the difference between the two since I'm quite new to sharepoint. Any tip on how to transform this list into a view? – Upcast

The URL itself looks like https://$DOMAIN/$SITE/_api/web/Lists/getByTitle('$LIST')/Items – Upcast

This guy had a similar problem whilst trying to return a list of sites and solved it by returning batches: #45026496 – Mymya

Answer 1

The limitation is due to server side paging.

A workaround is to retrieve 100 items at a time, or override the limitation by entering a count of items:

https://$DOMAIN/$SITE/_api/web/Lists/getByTitle('$LIST')/Items?$top=1000

Note that there is also a threshold at 5000.

Hobie

For now I don't have more records than 500 so this should do. Thanks for your help ^^! – Upcast

... though, just out of curiosity, how would I retrieve 100 items in batches (so that I could have control over which batch number I want to get next)? – Upcast

it's not easy... Maybe start here msdn.microsoft.com/en-us/library/office/… then browse blogs and forums to find out what actually works – Hobie

Thanks again. I'll have a look at it. – Upcast

Answer 2

You can use RowLimit & RowsPerPage in rest call. Below is the example

$.ajax({
url: siteurl + "/_api/web/lists/getbytitle('NewList')/items",
method: "GET",
headers: { "Accept": "application/json; odata=verbose" },
RowLimit : null, //Specify the row limit
RowsPerPage : null, //Specify no of rows in a page
success: function (data) {
     $('#oDataFilter').append("<table>");
     $.each(data.d.results, function(index, item){
         $('#oDataFilter').append("<tr><td class="+styleClass+">" + item.ID + "</td><td class="+styleClass+">"+ item.Title + "</td></tr>");
     });
     $('#oDataFilter').append("</table>");
},
error: function (error) {
    alert('Error getListItems :: '+JSON.stringify(error));
}

Ous

Totally awesome example. Tests out beautifully. – Fulbright

Answer 3

Adding to Christophe's answer I would say listing all (potentially 5000) items in a list and parsing them would result in performance issues.

If you query sharepoint to show all items in a particular list it would only print out the first 100. But the xml response also provides the url to query for the next 100 list items.

At the very end of the xml response you will see a tag like this

<link rel="next" href="https://xxxxx.sharepoint.com/_api/web/lists/GetByTitle('list')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d100" />

The url inside href="...."is what you'll need.

Querying the above would provide you with a list of the next 100 or less items. If there are still more items left this xml response would in turn provide an other <link rel="next"> tag and if not this tag won't exist.

Better to handle 5000 items in sets of 100 rather than them all together in my opinion.

Sincere

I'm having a problem that the url is encoded and decode it doesn't work, is there a fix for that? – Bum

Answer 4

To anyone now seeing this, you can use data.d.__next to get the next 100 items. Using some good old recursion, you can get all the items like so

function getItems(url) {
  $.ajax({
    url: url,
    type: "GET",
    headers: {
      "accept": "application/json;odata=verbose",
    },
    success: function(data) {
      console.log(data);

      // Do work

      if (data.d.__next) {
        getItems(data.d.__next);
      }
    },
    error: function(jqxhr) {
      alert(jqxhr.responseText);
    }
  });
}

Araliaceous

Does this have a limitation of 5000 items aswell? – Paxwax