In my last couple of posts, I talked about how to use the OData operators to select, expand, sort, and filter data. In this post I’m going to introduced a couple more operators, $top and $skipToken, which can be combined to provide pagination functionality through the SharePoint REST APIs.
The Base Query
As in my last post, I’m going to use a base query throughout which I’ll describe once here, and then show how the results are modified by tacking on $top and $skipToken query parameters. The list I’m going to query is called SalesState, and it looks like this:
It is a pretty simple list. The title field holds the state and there is a SalesDivision field which is a lookup to another list. Also, there is enough data for me to play around with pagination and see how it manipulates the results.
As in my previous post, I’m going to be running these queries in the browser console, and my query will use $.ajax, so I need to make sure jQuery is loaded into the page. To do that, I’ll run the following code in the console first, which inserts a script tag into the page that loads jQuery from a CDN:
1 2 3 4 | var script = document.createElement('script'); script.type = 'text/javascript'; script.src = 'https://cdn.jsdelivr.net/npm/jquery@1.12.4/dist/jquery.js'; document.head.appendChild(script); |
And the base query is going to bring back the id and title of the item plus the id and title of the SalesRegion lookup field (if you don’t understand the syntax, see my previous post). It looks like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | var url = _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/getByTitle(@Target)/Items?" + "$select=Id,Title,SalesDivision/Id,SalesDivision/Title" + "&" + "$expand=SalesDivision" + "&" + "@Target='SalesState'"; $.ajax({ url: url, async: false, headers: { 'accept': 'application/json;odata=nometadata' } }); |
The JSON result from this query looks like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | { "value": [ { "SalesDivision": { "Id": 1, "Title": "New England" }, "Id": 1, "Title": "Connecticut", "ID": 1 }, { /*... a bunch more states */ }, length: 50 ] }; |
Nothing earth shattering to see here.
Paginating results with $top and $skipToken
The first step to paginate results is to limit the number of results returned from a query, for which you use $top. The value of $top is an integer, so if I want 10 results I add:
1 | &top=10 |
and I get back:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | { odata.nextLink: "https://intellipointsol.sharepoint.com/sites/csrdemos/_api/Web/Lists/getByTitle(@Target)/Items?" + "%24skiptoken=Paged%3dTRUE%26p_ID%3d10&%24" + "select=Id%2cTitle%2cSalesDivision%2fId%2cSalesDivision%2fTitle&%24" + "expand=SalesDivision&%24" + "top=10&%40Target=%27SalesState%27", value: [ 0: { ID: 1, Id: 1, SalesDivision: { }, Title: "Connecticut" }, { /* 9 more items */ }. length: 10 ] } |
So I get back 10 states. You can see from the results of my base query that I have 50 states in the list. I’m only showing the first item above, but I actually get back the items with Id 1 to 10 in ascending order. If I had deleted some of those items, I still would have gotten back the lowest 10 Ids in ascending order, since I didn’t specify an $orderBy, and ordered by Id in ascending order is the default for SharePoint.
Now I also get back something else; namely an odata.nextLink. This is the query that I need to run in order to return the next 10 items in the list. The query string is all URI encoded, but below I show what it looks like decoded and split out nicely for easier reading:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | var url = "https://intellipointsol.sharepoint.com/sites/csrdemos/_api/Web/Lists/getByTitle(@Target)/Items" + "$skipToken=" + encodeURIComponent("Paged=TRUE&p_ID=10") + "&" + "$select=Id,Title,SalesDivision/Id,SalesDivision/Title" + "&" + "$expand=SalesDivision" + "&" + "$top=10" + "&" + "@Target='SalesState'"; $.ajax({ url: url, async: false, headers: { 'accept': 'application/json;odata=nometadata' }, complete : function(json) { console.log(json.responseJSON); } }); |
As you can see, this is the original query I used with one parameter added: $skipToken. This has an opaque, vendor specific, value, meaning that it isn’t specified in the OData specification and Microsoft could change it at any time without violating the specification in any way, so technically we’re not supposed understand its internal structure or to use it directly.
That said, it’s pretty simple. It consists of two name value pairs separated by an ampersand. The first is just Paged=TRUE, and the second is called p_ID and has an integer value. I’ve read numerous places that p_ID is the Id of the last item returned so far, but this is not correct. It’s actually the ordinal of the next item to be returned. In other words, if I deleted the first two states and ran this query again, the last item returned would be Id=12, but the p_ID for $skipToken would still be 10. This has some interesting consequences that you need to be aware of, but I’ll talk more about that in a moment. Also, because this value contains equals signs and ampersands, and those will screw up parsing of the query string, this value has to be URI encoded. If you just take the entire URL passed to you in odata.nextLink like you’re supposed to, this is already taken care of for you.
Anyway, the results of this query are:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | { odata.nextLink: "https://intellipointsol.sharepoint.com/sites/csrdemos/_api/Web/Lists/getByTitle(@Target)/Items?%24skiptoken=Paged%3dTRUE%26p_ID%3d20&%24select=Id%2cTitle%2cSalesDivision%2fId%2cSalesDivision%2fTitle&%24expand=SalesDivision&%24top=10&%40Target=%27SalesState%27", value: [ 0: { ID: 11, Id: 11, SalesDivision: { }, Title: "Indiana" }, { /* 8 more items */ } 9: { ID: 20, Id: 20, SalesDivision: { }, Title: "South Dakota" }, length: 10 ] } |
In my case, since I’ve never deleted anything from the list, I get the items with Ids 11 through 20, but in general you can only count on the next 10 lowest Ids in ascending order.
And if I change only the p_ID to 40 like so:
1 | "$skiptoken=" + encodeURIComponent("Paged=TRUE&p_ID=40") |
The results of the query are:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | { value: [ 0: { ID: 41, Id: 41, SalesDivision: { }, Title: "Montana" }, { /* 8 more items */ }, 9: { ID: 50, Id: 50, SalesDivision: { }, Title: "Washington" }, length: 10 ] } |
Here I got the items with Ids 41 through 50. I also didn’t get back an odata.nextLink at all. That’s the only indication you get that you are at the end of the result set.
Now I mentioned before that REST pagination in SharePoint is ordinal in nature, and this had consequences you should be aware of. Let’s say I ran my first query above and got back items Id 1 through 10. Then I took a sip of my coffee, munched on a donut, answered a call…you get the idea (and also a pretty good picture of how I work ;). Then I run my second query with $skipToken=Paged%3dTRUE%26p_ID%3d10 (i.e. p_ID=10, exactly what I got back from odata.nextLink). In the meantime, my evil cubicle neighbor decides to screw with me and deletes the first two states. The REST service will happily return the items with Ids 13 through 22 to me. And if I carry though running each successive query using odata.nextLink until I don’t have any more results, I will “see” every item in the list except the states with Ids 11 and 12.
This could produce some wonky results at times if you’re developing a user interface with pagination. There really isn’t any other way it could work. There isn’t any context in which multiple queries can run; each query is independent and the state can change between queries. But if you’re doing some kind of processing where you need to do something to every item in the list reliably, pagination really isn’t a reliable option. Just keep that in mind.
How About Using $skip instead of $skipToken?
The OData specification does have another operator called $skip. It’s value is just an integer, the number of items to be skipped (i.e. ordinal again), so $skip=10 should be the same as $skipToken=Paged%3dTRUE%26p_ID%3d10, right? So why have I been mucking around with $skipToken at all? The simple reason is, $skip is not supported for list data. Ugh! That’s clearly pointed out in the SharePoint REST API documentation. $skip is only supported for what they call ‘collections’, like if you asked for all lists (never mind that list items exist in the underlying API as an SPListItemCollection?).
So here is an example of a query for the top 10 list titles in a site:
1 2 3 4 5 6 7 8 9 | var url = _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists?$top=10&$select=Title"; $.ajax({ url: url, async: false, headers: { 'accept': 'application/json;odata=nometadata' } }); |
And here is the result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | { 0: { Title: "Announcements" } 1: { Title: "appdata" } 2: { Title: "appfiles" } 3: { Title: "Companies" } 4: { Title: "Composed Looks" } 5: { Title: "Contacts" } 6: { Title: "ContactsClean" } 7: { Title: "Content type publishing error log" } 8: { Title: "Converted Forms" } 9: { Title: "CSR Demos" } length: 10 } |
First, notice that there is no odata.nextLink. I guess Microsoft didn’t think it was needed because a simple $skip would work for pagination. But without it, how do I know if there is any more data to read? This probably isn’t that big of an issue, because of the places where $skip is an option. I mean, having 5000 items in a list is not so uncommon making pagination an important function, but if you have 5000 lists in a site you’re probably doing something wrong. And you can always look at the count on the list collection to figure out where pagination should end.
Putting that aside, if I add $skip like so:
1 | &skip=10 |
The results of the query are:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | { 0: { Title: "Docs" } 1: { Title: "FAQ" } 2: { Title: "Form Templates" } 3: { Title: "JobTitles" } 4: { Title: "List Template Gallery" } 5: { Title: "Maintenance Log Library" } 6: { Title: "Master Page Gallery" } 7: { Title: "MicroFeed" } 8: { Title: "Pictures" } 9: { Title: "Project Policy Item List" } length: 10 } |
Cool, actually works as expected. If you specify $skip on a query for list data, however, it is simply ignored. And I’ve read numerous posts over the years asking about this on stack exchange and what not, going back as far as 2013, to which the response is generally that Microsoft hasn’t implemented this for list data “yet”. We’re rounding the corner into 2019, and it still works this way even in SharePoint online. I don’t think it’s coming!
Also note that on collections where $skipToken works, $skip is ignored. I haven’t seen that anywhere in the documentation and I’ve only done limited testing, but that’s been my experience. And trying to use both in the same query will always produce and invalid query exception.
Sum Up
At one time I thought that column indexes plus pagination would be a reasonable workaround for some of the boundaries and limits of large lists in SharePoint. Unfortunately, the implementation is somewhat inconsistent and unreliable; not exactly the hallmarks of everything I’m looking for in an API. Inconsistent in that you need to use $skip on some collections, but need to use $skipToken for others. And unreliable in that there is no accounting for changes to the underlying data between pagination REST calls. This is really just another specific example of a general problem with all SharePoint APIs, the lack of any kind of transactional semantics. For a user interface where an occasional hiccup is acceptable, it can be quite useful, but be sure you understand it’s limitations before you depend on it heavily.