SharePoint REST API Order by, Filter, Pagination, and Select

by

Do you want to learn about SharePoint REST API operations such as Select, Order by, Pagination, and Filter?

In this SharePoint tutorial, we will discuss how to perform various REST API operations like Order by, Filter, Pagination, and Select with examples.

  • SharePoint rest api select
  • SharePoint rest api order by desc
  • SharePoint rest api order by modified date descending
  • SharePoint rest api order by two columns
  • SharePoint rest api filter

SharePoint REST API Select

Here, we will see how to perform a selection operation on the result SharePoint list using $select in the SharePoint Rest api.

You can customize the fields returned from a SharePoint list by specifying which columns you need using the $select parameter in the REST API URL. This allows you to retrieve only the specific fields required for your result items, even if the list contains numerous columns with data.

For example, we will use the SharePoint Sales list and perform a REST API GET operation. We will utilize the $select parameter to retrieve specific fields.

The sales list contains the following columns:

  • Id [Number]
  • Product Name [title: Single line of text]
  • Product Category [Choice]
rest api select

Below is the complete REST API code that you can add using a script editor web part.

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>

    var siteUrl = "https://szg52.sharepoint.com/sites/Sales";
    var listName = "Sales";
    var selectFields = "$select=ID,Title";
    
    $.ajax({
        url: siteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items?" + selectFields,
        method: "GET",
        headers: {
            "Accept": "application/json; odata=verbose"
        },
        success: function (data) {
            var itemsHtml = "";
            if (data.d.results.length > 0) {
                itemsHtml += "<ul>";
                data.d.results.forEach(function (item) {
                    itemsHtml += "<li>ID: " + item.ID + ", Product Name: " + item.Title + "</li>";
                });
                itemsHtml += "</ul>";
            } else {
                itemsHtml = "No items found in the Sales list.";
            }
            $("#salesList").html(itemsHtml);
        },
        error: function (error) {
            $("#salesList").html("Error occurred: " + JSON.stringify(error));
        }
    });
</script>

<div id="salesList"></div>

Now you can see the Result. Based on the values passed in the $Select parameter, it only retrieves those four fields: title, product name, product Category, and Unit Price.

sharepoint rest api select parameter

SharePoint REST API Order By Desc

Here, we will see how to sort the SharePoint list results in descending order based on the field using the SharePoint rest api.

When receiving a list of items from a SharePoint REST API response, you may need to arrange them based on a particular field. To achieve this, use the $ order by parameter, specifying whether you want to ascend (ASC) or to descend (desc) sorting order.

For example, we will use the SharePoint Sales list to retrieve items from the fields ‘ID’ and ‘product name’ using the SharePoint REST API. Additionally, we will sort the IDs in descending order.

order by in rest api

To do this, we will add the SharePoint REST API code below to the Script Editor web part of the SharePoint site.

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
    var siteUrl = "https://szg52.sharepoint.com/sites/Sales";
    var listName = "Sales";
    var selectFields = "$select=ID,Title";

    // Provide the field to sort by and the sorting order
    var orderByField = "ID";
    var orderByDirection = "desc";
    var apiUrl = siteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items?" + selectFields + "&$orderby=" + orderByField + " " + orderByDirection;

    $.ajax({
        url: apiUrl,
        method: "GET",
        headers: {
            "Accept": "application/json; odata=verbose"
        },
        success: function(data) {
            if (data.d.results.length > 0) {
                // Loop through the results
                var itemsHtml = "<ul>";
                $.each(data.d.results, function(index, item) {
                    var id = item.ID;
                    var productName = item.Title;
                    // Append each item's details to the HTML string
                    itemsHtml += "<li>ID: " + id + ", Product Name: " + productName + "</li>";
                });
                itemsHtml += "</ul>";
                $("#salesList").html(itemsHtml);
            } else {
                $("#salesList").html("No items found in the Sales list.");
            }
        },
        error: function(error) {
            $("#salesList").html("Error occurred: " + JSON.stringify(error));
        }
    });
</script>
<div id="salesList"></div>

Now you can see the SharePoint rest api results are ordered in descending order.

sharepoint rest api rest api order by desc

SharePoint REST AP Order By Modified Date Descending

Here, we will see how to order the results of the SharePoint list in descending order based on the Modified date column using SharePoint REST API.

For example, we will implement SharePoint REST API in the SharePoint Sales list to retrieve the selected fields: Title, Product Name, Product Category, and Modified column. The results will be sorted in descending order based on the Modified Date.

order by in rest api

So the SharePoint rest api Url will be: https://szg52.sharepoint.com/sites/Sales/_api/web/lists/getbytitle(‘Sales’)/items?$select=ID,Title,Modified&$orderby=Modified desc

Here is the Full code for the SharePoint rest api order by modified date descending:

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
    var siteUrl = "https://szg52.sharepoint.com/sites/Sales";
    var listName = "Sales";
    var selectFields = "$select=ID,Title,Modified";

    // Provide the field to sort by and the sorting order
    var orderByField = "Modified";
    var orderByDirection = "desc";
    var apiUrl = siteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items?" + selectFields + "&$orderby=" + orderByField + " " + orderByDirection;

    $.ajax({
        url: apiUrl,
        method: "GET",
        headers: {
            "Accept": "application/json; odata=verbose"
        },
        success: function(data) {
            if (data.d.results.length > 0) {
                // Loop through the results
                var itemsHtml = "<ul>";
                $.each(data.d.results, function(index, item) {
                    var id = item.ID;
                    var productName = item.Title;
                    var modifiedDate = new Date(item.Modified);
                    // Format modified date to display in a user-friendly format
                    var formattedModifiedDate = modifiedDate.toLocaleDateString() + " " + modifiedDate.toLocaleTimeString();
                    // Append each item's details to the HTML string
                    itemsHtml += "<li>ID: " + id + ", Product Name: " + productName + ", Modified Date: " + formattedModifiedDate + "</li>";
                });
                itemsHtml += "</ul>";
                $("#salesList").html(itemsHtml);
            } else {
                $("#salesList").html("No items found in the Sales list.");
            }
        },
        error: function(error) {
            $("#salesList").html("Error occurred: " + JSON.stringify(error));
        }
    });
</script>
<div id="salesList"></div>

Once you save the page, you will see the result of the SharePoint REST API, ordered by modified date in descending order.

SharePoint rest api order by modified date descending

SharePoint REST API Order By Two Columns

Here, we will see how to sort the results of the SharePoint list by two columns using the SharePoint REST API.

For example, we will implement SharePoint REST API in the SharePoint Sales list to retrieve the selected fields ID, Product Name, and Product Category. The results will be sorted in descending order based on ID and ascending order based on Product Category using the SharePoint REST API.

order by in rest api

So, for this rest api URL will be: “https://szg52.sharepoint.com/sites/Sales/_api/web/lists/getbytitle(‘Sales’)/items?$select=Title,field_1,field_2&$orderby=ID dsc, ProductCategory asc”;

Here is the full code for the SharePoint rest api order by two columns:

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>

    var apiUrl = "https://szg52.sharepoint.com/sites/Sales/_api/web/lists/getbytitle('Sales')/items?$select=ID,Title,ProductCategory&$orderby=ID desc,ProductCategory asc";

    // Make a GET request to the SharePoint REST API
    $.ajax({
        url: apiUrl,
        method: "GET",
        headers: {
            "Accept": "application/json; odata=verbose"
        },
        success: function(data) {
            if (data.d.results.length > 0) {
                var itemsHtml = "<ul>";
                $.each(data.d.results, function(index, item) {
                    var id = item.ID;
                    var productName = item.Title;
                    var productCategory = item.ProductCategory;
                    // Append the selected fields to the HTML string
                    itemsHtml += "<li>ID: " + id + ", Product Name: " + productName + ", Product Category: " + productCategory + "</li>";
                });
                itemsHtml += "</ul>";
                $("#salesList").html(itemsHtml);
            } else {
                $("#salesList").html("No items found in the Sales list.");
            }
        },
        error: function(error) {
            // Display an error message if an error occurs
            $("#salesList").html("Error occurred: " + JSON.stringify(error));
        }
    });
</script>

<div id="salesList"></div>

Once you save the page, you can see the data is sorted in descending order based on the ID and ascending order based on Product category using the SharePoint rest api.

SharePoint rest api order by two columns

SharePoint REST API Pagination

Here, we will learn how to utilize the paging parameter in the SharePoint REST API to retrieve items from the SharePoint Sales list.

sharepoint pagination using rest api


Paging is an operation that we can perform using the SharePoint REST API to segment a large dataset into smaller chunks of data. We achieve this by utilizing three parameters in our REST API requests.

  • $top: This parameter is used for paging. It is applied to the SharePoint REST API result to ensure that we retrieve only the top n entries.

For example, we will use the SharePoint sales list, and we will fetch the top 10 items using the $top parameter in the SharePoint rest api.

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
    $.ajax({
        url: "https://szg52.sharepoint.com/sites/Sales/_api/web/lists/getbytitle('Sales')/items?$select=ID,Title&$top=10",
        method: "GET",
        headers: {
            "Accept": "application/json; odata=verbose"
        },
        success: function(data) {
            if (data.d.results.length > 0) {
                // Loop through the results
                var itemsHtml = "<ul>";
                $.each(data.d.results, function(index, item) {
                    var id = item.ID;
                    var productName = item.Title;
                    // Append each item's details to the HTML string
                    itemsHtml += "<li>ID: " + id + ", Product Name: " + productName + "</li>";
                });
                itemsHtml += "</ul>";
                $("#salesList").html(itemsHtml);
            } else {
                $("#salesList").html("No items found in the Sales list.");
            }
        },
        error: function(error) {
            $("#salesList").html("Error occurred: " + JSON.stringify(error));
        }
    });
</script>
<div id="salesList"></div>
SharePoint rest api pagination
  • $skip: This parameter is also used for paging purposes. It tells the SharePoint REST API to skip the first n entries from the result set. It’s important to note that the $skip parameter does not function on list items in SharePoint 2013 but is only effective on Lists.

For example, if we want to skip the first 5 lists from the SharePoint site, we can add the parameter $skip=5 to the REST API URL. This will tell the API to skip the first 5 items from the SharePoint REST API result.

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
    // Specify the number of items to skip
    var skipCount = 5; 

    $.ajax({
        url: "https://szg52.sharepoint.com/sites/Sales/_api/web/lists?$skip=" + skipCount,
        method: "GET",
        headers: {
            "Accept": "application/json; odata=verbose"
        },
        success: function(data) {
            if (data.d.results.length > 0) {
             
                var listsHtml = "<ul>";
                $.each(data.d.results, function(index, list) {
                    var listName = list.Title;
                    // Append each list's title to the HTML string
                    listsHtml += "<li>List Name: " + listName + "</li>";
                });
                listsHtml += "</ul>";
                $("#ListNames").html(listsHtml);
            } else {
                $("#ListNames").html("No lists found.");
            }
        },
        error: function(error) {
            $("#ListNames").html("Error occurred: " + JSON.stringify(error));
        }
    });
</script>
<div id="ListNames"></div>

It will skip the first 5 lists from that SharePoint site, and it will show the rest of the list names using the SharePoint rest api.

sharepoint api pagination
  • $expand: This parameter is crucial when dealing with SharePoint person or lookup fields, as it allows us to retrieve additional information beyond just the ID. By using this parameter, we can obtain the corresponding field values associated with the IDs returned.

For example, the Sales list contains four columns: ID (Number), Product Name (Title: Single line of text), Product Category (Choice), and User column (Person).

We will see how to retrieve the user’s name and ID using the $expand parameter in the REST API. Below is the code that will retrieve the ID, Product name, User ID, and User name from the Sales list using the SharePoint REST API.

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
    $.ajax({
        url: "https://szg52.sharepoint.com/sites/Sales/_api/web/lists/getbytitle('Sales')/items?$select=ID,Title,User/Id,User/Title&$expand=User",
        method: "GET",
        headers: {
            "Accept": "application/json; odata=verbose"
        },
        success: function(data) {
            if (data.d.results.length > 0) {
              
                var itemsHtml = "<ul>";
                $.each(data.d.results, function(index, item) {
                    var id = item.ID;
                    var title = item.Title;
                    var userId = item.User.Id;
                    var userName = item.User.Title;
                   
                    itemsHtml += "<li>ID: " + id + ", Product Name: " + title + ", User ID: " + userId + ", User Name: " + userName + "</li>";
                });
                itemsHtml += "</ul>";
                $("#salesList").html(itemsHtml);
            } else {
                $("#salesList").html("No items found in the Sales list.");
            }
        },
        error: function(error) {
            $("#salesList").html("Error occurred: " + JSON.stringify(error));
        }
    });
</script>
<div id="salesList"></div>

Now you can see we retrieved the User ID and User name from the SharePoint sales list using the $expand parameter in the rest api

pagination parameters in rest api

This is how we can use the pagination parameters in the SharePoint rest api.

SharePoint REST API Filter

Here, we will see how to implement a filter operation using the SharePoint REST API.

The $filter operator helps us retrieve only items from the SharePoint list that match the condition.

In the filtering operation, various types of operators are used to implement the condition. Here are the operators listed below:

OperatorDescription
ltLess than
gtGreater than
leLess than or equal to
geGreater than or equal to
eqEqual
nenot equal
startsWithFetch all the records starting with the particular value
endsWithFetch all the records ending with the particular value
substringofFilters all the items that contain certain characters in the specific column
concatFetch all the records starts with the particular value
day()It converts the date string to a day of the month
month()It converts the date string to a month of the year
year()It converts a date string to a year
hour()It converts the date string to an hour
Concatenate the two strings and filter the recordsIt converts the date string to a minute

For example, from the sales list, we will filter out IDs less than 8, so it will return 7 items from the SharePoint list.

sharepoint rest api get list items with filter

To implement this using the SharePoint rest api, we can use the code below

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
    $.ajax({
        url: "https://szg52.sharepoint.com/sites/Sales/_api/web/lists/getbytitle('Sales')/items?$select=ID,Title,ProductCategory&$filter=ID lt 8",
        method: "GET",
        headers: {
            "Accept": "application/json; odata=verbose"
        },
        success: function(data) {
            if (data.d.results.length > 0) {
                var itemsHtml = "<ul>";
                $.each(data.d.results, function(index, item) {
                    var id = item.ID;
                    var productName = item.Title;
                    var productCategory = item.ProductCategory;
                    // Append each item's details to the HTML string
                    itemsHtml += "<li>ID: " + id + ", Product Name: " + productName + ", Product Category: " + productCategory + "</li>";
                });
                itemsHtml += "</ul>";
                $("#salesList").html(itemsHtml);
            } else {
                $("#salesList").html("No items found in the Sales list with ID less than 8.");
            }
        },
        error: function(error) {
            $("#salesList").html("Error occurred: " + JSON.stringify(error));
        }
    });
</script>
<div id="salesList"></div>

Now you can see the SharePoint list items get filtered and display 7 items using the $filter in the SharePoint rest api.

sharepoint rest api filter

SharePoint REST API Search List

Here, we will see how to create a search query that searches the entire list using the SharePoint REST API.

For example, let’s consider the same SharePoint sales list. If we search for the character ‘ch’, the search operation will scan the entire list and return results containing the ‘ch’ character sequence.

To implement this functionality, we can utilize the filter parameter with the substringof operator in our SharePoint REST API call.

Here is the code for adding a text box and search button to your SharePoint web part or UI. You can type the character you want to search for into the text box, and by clicking on the search icon, you can search the entire SharePoint list using the REST API.

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
    $(document).ready(function() {

        function searchItems() {
            var searchText = $("#searchText").val();
            var apiUrl = "https://szg52.sharepoint.com/sites/Sales/_api/web/lists/getbytitle('Sales')/items?$select=ID,Title,ProductCategory&$filter=substringof('" + searchText + "', Title) or substringof('" + searchText + "', ProductCategory)";

            $.ajax({
                url: apiUrl,
                method: "GET",
                headers: {
                    "Accept": "application/json; odata=verbose"
                },
                success: function(data) {
                    if (data.d.results.length > 0) {

                        var itemsHtml = "<ul>";
                        $.each(data.d.results, function(index, item) {
                            var id = item.ID;
                            var productName = item.Title;
                            var productCategory = item.ProductCategory;

                            itemsHtml += "<li>ID: " + id + ", Product Name: " + productName + ", Product Category: " + productCategory + "</li>";
                        });
                        itemsHtml += "</ul>";
                        $("#salesList").html(itemsHtml);
                    } else {
                        $("#salesList").html("No items found in the Sales list matching the search query.");
                    }
                },
                error: function(error) {
                    $("#salesList").html("Error occurred: " + JSON.stringify(error));
                }
            });
        }


        $("#searchButton").click(function(event) {
            event.preventDefault(); 
            searchItems();
        });
    });
</script>
<div>
    <input type="text" id="searchText" placeholder="Enter search text">
    <button id="searchButton">Search</button>
</div>
<div id="salesList"></div>

Now save the code. In the text box, add the text you want to search for, then click on the search button.

sharepoint rest api search list

Once we click on the button, we can see the result below from the SharePoint rest api.

rest api filter sharepoint

Conclusion

In this SharePoint tutorial, we learned how to implement sorting, paging, filtering, and selection using the SharePoint REST API.

You may like the following tutorials:

>

Build a High-Performance Project Management Site in SharePoint Online

User registration Power Apps canvas app

DOWNLOAD USER REGISTRATION POWER APPS CANVAS APP

Download a fully functional Power Apps Canvas App (with Power Automate): User Registration App

Power Platform Tutorial FREE PDF Download

FREE Power Platform Tutorial PDF

Download 135 Pages FREE PDF on Microsoft Power Platform Tutorial. Learn Now…