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

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 some REST API operations like SharePoint rest api order by, SharePoint rest api pagination, rest api filter SharePoint, SharePoint rest api search list, and many more like:

  • 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 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

In this case, we will retrieve the ID and Product name from the Sales List and display the result in the web part. So, the rest api url will be ‘https://szg52.sharepoint.com/sites/Sales/_api/web/lists/getbytitle(‘Sales’)/items?$select=ID,Ttitle

  • Open the Page on the SharePoint Classic site, and then click on the Edit button to edit the page.
type of rest api
  • Then click on the Insert tab and click on the Web part icon. Under the Categories, select Media and Content, then select the Script Editor from the option. Then click on the Add button
sharepoint rest api  operation

Then click the arrow icon and select the Edit webpart from the drop-down options.

sharepoint crud operations

Then click on the Edit Snippet link in the web part and write the code below.

<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>
sharepoint rest api select

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 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 webpart of the SharePoint classic 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>
sharepoint rest api rest api sort desc

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

sharepoint rest api rest api order by desc

SharePoint rest api 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.

See also  Windows Server roles and features cannot be automatically installed or uninstalled via the Windows Features Control Panel while installing .Net framework 3.5

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 SharePoint 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 see the result of SharePoint rest api order by modified date descending.

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 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 SharePoint REST API.

SharePoint rest api order by

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 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 descending based on the ID and ascending order based on Product category using SharePoint rest api.

SharePoint rest api order by two columns

SharePoint rest api pagination

Here, we will see how to use the paging parameter in 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 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.
See also  SharePoint Online Microsoft Forms Web part

For example, we will use the SharePoint sales list, and we will fetch the top 10 items using the $top parameter in 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 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 SharePoint rest api.

See also  How to hide document library in SharePoint Online

SharePoint rest api filter

Here, we will see how to implement filter operation in 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 an 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 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 SharePoint rest api.

sharepoint rest api filter

SharePoint rest api search list

Here, we will see how to create a search query so it will search the whole list using 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 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 below result from the SharePoint rest api.

rest api filter sharepoint

Some more SharePoint REST API articles you may also like:

Conclusion

In this SharePoint tutorial, we saw how to implement sorting, paging, filtering, and selecting in the SharePoint REST API.

These are the topics we covered:

  • 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 pagination
  • SharePoint rest api filter
  • SharePoint rest api search list
>