34

When doing a GET call to the List API from JavaScript: "/_api/web/lists/GetByTitle('ListName')/items" the results returned are limited to 100 rows.

Is there any way to return more rows in the result, like return 1000 rows instead? Or do I have to resort to paging?

32

The limit is due to server-driven paging. It protects against developers inadvertently making a request that returns large result sets.

You can use $top if you really want a large result set to be returned. Otherwise you can get the results a page at a time by checking __next. If it has a value, it contains the url that will return the next set of items. If it's null, you've hit the end of the result set.

function callToHostWeb() {
    var message = jQuery("#message");
    message.text("");

    var hostUrl = decodeURIComponent(getQueryStringParameter("SPHostUrl"));
    var appUrl = decodeURIComponent(getQueryStringParameter("SPAppWebUrl"));

    var scriptbase = hostUrl + "/_layouts/15/";
    var url = appUrl + "/_api/SP.AppContextSite(@target)/Web/Lists/getByTitle('Order Details')/Items?$select=Title&@target='" + hostUrl + "'";
    jQuery.getScript(scriptbase + "SP.RequestExecutor.js", getOrderDetails);

    function getOrderDetails() {
        var executor = new SP.RequestExecutor(appUrl);
        executor.executeAsync(
            {
                url: url,
                method: "GET",
                dataType: "json",
                headers: {
                    Accept: "application/json;odata=verbose"
                },
                success: function (data) {
                    var response = JSON.parse(data.body);
                    message.append(String.format("Retrieved {0} items", response.d.results.length));
                    message.append("<br/>");

                    if (response.d.__next) {
                        url = response.d.__next;
                        getOrderDetails();
                    }
                },
                error: function (data, errorCode, errorMessage) {
                    alert(errorMessage);
                }
            }
        );
    }

    function getQueryStringParameter(paramToRetrieve) {
        var params =
            document.URL.split("?")[1].split("&");
        var strParams = "";
        for (var i = 0; i < params.length; i = i + 1) {
            var singleParam = params[i].split("=");
            if (singleParam[0] == paramToRetrieve)
                return singleParam[1];
        }
    }
}
  • 1
    Just wanted to confirm that $top is the one you should be going after. $limit does not work for me in SharePoint 2013. – koniczynek Jan 3 '14 at 12:34
  • $limit didn't work for me either – yngrdyn Mar 22 '16 at 15:04
  • Good answer +1. However your callback on success may need to work on all results. See my answer for handling that case. – Emiliano Poggi Apr 21 '16 at 11:07
  • i there any way to get all data of list using REST. Because i dont want to give static limit to top. i am making graph from that records . so i want all data of list. – Vishal Thakkar Aug 11 '16 at 5:17
  • 1
    what if we want to use filter like _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('ListName')/items?$filter=ColumnName eq 'abc'&$top=1000". Where we have more than 5000 items having value 'abc' in the ColumnName. Will it work? – Harsh Dec 13 '17 at 8:57
26
/_api/web/lists/GetByTitle('ListName')/items?$top=1000
  • The problem with $top is that you do a single ajax call with potentially a lot of results in your response. This may cause performance issues. – Emiliano Poggi Apr 21 '16 at 11:11
12

the "$limit=" option above does NOT work. however, the $top= does.

  • 1
    The problem with $top is that you do a single ajax call with potentially a lot of results in your response. This may cause performance issues. – Emiliano Poggi Apr 21 '16 at 11:11
6

The option of querying items with $top is not advisable for performance reasons and impact on the network traffic.

The best option is recursion. Note: Normally you have a callback on success and you may have to work on all items: in that case you need to concat all results before executing the callback. Also you may need a check on complete callback. The following example address this use case:

function GetAllItems (ListGuid, Query, OnSuccess, OnComplete) {

    function GetItems() {
        jQuery.ajax({
            url: url,
            method: "GET",
            headers: { "Accept": "application/json;odata=verbose" },
            success: function (data) {
                results = results.concat(data.d.results);
                if (data.d.__next) {
                    url = data.d.__next;
                    GetItems();
                }
                else {
                    nonext = true;
                    if (typeof OnSuccess != 'undefined') OnSuccess(results);
                }
            },
            error: SPUtils.onQueryFailed,
            complete: function () {
                if (typeof OnComplete != 'undefined' && nonext) {
                    OnComplete();
                }
            }
        });
    }

    var nonext = false;
    var results = [];
    var url = LMS.WebApi + "/lists(guid'" + ListGuid + "')/items?" + Query;
    GetItems();
}
  • 2
    Just a note; data.d.__next will be data['odata.nextLink'] if you are using odata=nometadata , verbose should not be used imo unless there is a reason for it. – Anders Aune May 5 '16 at 18:28
  • @AndersAune good point, in most scenarios nometadata provides all we need with minimal payload. – Emiliano Poggi May 6 '16 at 9:01
  • 1
    Clean code!!! ++ – EasyE Oct 3 '16 at 22:15
  • 1
    I disagree - if you literally want all of the items, a single request is more performant and has less network overhead. If you're display items to a user, however, you'll want to use paging. If you're doing a "type-ahead" dropdown, you'll want to limit. But if you actually want all items, request all items in a single request. – Kieren Johnstone Jun 20 '18 at 9:07
  • also, this isn't recursion – Kieren Johnstone Jun 20 '18 at 10:42
5

100 items is the default limit Please use $limit in your request:

/_api/web/lists/GetByTitle('ListName')/items?$limit=1000
-2

You need to be sure to have $skiptoken=Paged=TRUE for this to work in 2013.

Not the answer you're looking for? Browse other questions tagged or ask your own question.