SharePoint REST API: Retrieve List Items Comparison

SharePoint REST API Items vs GetItems

In this post, I am going to perform a comparative analysis between the two SharePoint REST API methods, getitems and items. Both are used to fetch items from a SharePoint List.

GetItems v/s Items

GetItems Items
Method Type POST GET
List Item Query Supports both,
OData query as well as, CAML query which, can be send in the body of the POST request.
Supports only OData query.
Query Special column data like, Author/Lookup No support.
Cannot retrieve items of these special columns. Both, CAML query as well as OData $expand query don’t work. At best you can only retrieve the unique id of these special columns.
OData $expand works beautifully over here and returns the entire information of these special fields. For ex, for a column of type People(Author), it will return both, the unique id as well as the value(full name). This, of course, when you request them using $expand.
Performance If items are being fetched by OData query only then, the performance is identical. However, if the items are being fetched by CAML query then, the performance deteriorates as the resultant data will include some default columns irrespective of whether or not you request them. Performance is better than CAML query as it returns data of only the columns requested explicitly. No extra data gets transferred.
Limitation There is no limitation on the CAML query length. We can easily send a big and complex CAML query as it will be appended in the POST body. Since it’s a good practice to keep your URL under 2000 characters, there exist a query length limitation. Now, 2000 itself is a big number but, if your site’s url is quite big then appending a large query string may cause unexpected behavior in different browsers.
Default number of items returned All items. Watch out for Lists having more than 5000 list items (threshold) 100. Also you’ll get the url to fetch the next 100 items as well in the property, __next.

Now that the comparisons are out, let’s dive deep into each of these two options and fetch items from a SharePoint List. I’ll start with the one I usually recommend using first, Items.

Items


PK.prototype.items = function () {
    var executor = new SP.RequestExecutor(appweburl);

    executor.executeAsync({
        url: appweburl + "/_api/Sp.AppContextSite(@target)/Web/Lists(guid'783A6040-E88C-4675-A49F-155A9C37B437')/items?$select=Customer%5Fx0020%5FContinent,Customer%5Fx0020%5FCountry,OrderDate,Total%5Fx0020%5FPurchase%5Fx0020%5FAmoun&@target='" + hostWebUrl + "'",
        method: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
        success: PK.prototype.successHandlerItems,
        error: PK.prototype.errorHandlerItems
    });
}

Response

SharePoint REST API Items response

GetItems


PK.prototype.getitems = function () {
    var executor = new SP.RequestExecutor(appweburl);

    executor.executeAsync({
        url: appweburl + "/_api/Sp.AppContextSite(@target)/Web/Lists(guid'783A6040-E88C-4675-A49F-155A9C37B437')/getitems?$select=Customer%5Fx0020%5FContinent,Customer%5Fx0020%5FCountry,OrderDate,Total%5Fx0020%5FPurchase%5Fx0020%5FAmoun&@target='" + hostWebUrl + "'",
        method: "POST",
        headers: {
            "accept": "application/json; odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "content-type": "application/json; odata=verbose"
        },
        body: JSON.stringify({"query": { "__metadata": { "type": "SP.CamlQuery" }, "ViewXml": "<View><Query><OrderBy><FieldRef Name=\"OrderID\" /></OrderBy></Query></View>" } }),
        success: PK.prototype.successHandlerGetItems,
        error: PK.prototype.errorHandlerGetItems
    });
}

Response

SharePoint REST API GetItems response

Key Takeaways

  • The source code is available for download, as solution, on GitHub.
  • The data source used here is the one provided in AdventureWorksDB which is, Sales Order Details.
  • The examples given here are used in a SharePoint-hosted app but, can be utilized in any application using JSOM.

One thought on “SharePoint REST API: Retrieve List Items Comparison

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s