Paged Kendo Grid from SharePoint Search REST API Data

SharePoint data has had the history of being mysterious. Thankfully over the years, Microsoft has moved toward more web standard methods of the interacting with SharePoint list data. Nothing is more powerful than the SharePoint 2013 Search REST API:

• Supports Skip and Take
• Supports GET or POST
• Can return results in JSON
• Has many options to pinpoint the exact results you need

I have prepared a short demonstration to illustrate the SharePoint 2013 Search REST API: I will retrieve SharePoint list data across multiple lists, present it in a pageable grid, and do it in under 100 lines of well-formatted script and markup.

The Set-Up

I’ve chosen the SharePoint out-of-the-box Contacts list and Content Type for the demo. I simply created two Contacts lists on my sandbox, then I seeded some random data in these Contacts lists:

Once you have staged some data, it needs to be crawled by the SharePoint Search Indexer in order for it to show up in a Search query. As an example, for me on O365/SPO, the data was crawled in under 60 seconds. For those “on-prem” your crawls will take longer depending on how your farm is configured. ((Hey, I’m just a developer..))

The Query

Using the SharePoint Search Query Tool, I was able to test a query for “ContentType: Contact”:

https://spr.sharepoint.com/sites/dev.john.wefler/_api/search/query?querytext='ContentType:Contact'

This query returned my staged Contacts records from my site. As a matter of fact, it returned about 60 to 80 pieces of metadata about each Contact returned.  The “Contact” Content Type contains a number of out-of-the-box Fields. The Fields are important because Fields translate to Crawled and Managed Properties from a search perspective. Using out-of-the-box fields guarantees that our demo will work, because our Managed Properties are already defined in our Search Schema. If you are using custom Content Types and Field definitions, you will have to take an extra steps and create Managed Properties for your custom fields. ((Creating Managed Properties lies outside of the scope of this demo.))

We can select the metadata we want returned from the SharePoint Search REST API call, to limit the size of our AJAX response. This is accomplished via Search Managed Properties. I found these Managed Property names by looking at my Site Collection’s Search Schema. Then I searched the Crawled Properties with the internal names of the fields I wanted to include from my Contacts lists:

The Select Properties information is appended on to our AJAX request URL:

&selectproperties='JobTitleOWSTEXT%2cFirstNameOWSTEXT%2cTitle%2cCompanyOWSTEXT'

The Hook-Up

When I’m writing client-side script for SharePoint, I do NOT invest time in any of the following:

• SharePoint Designer
• Connecting Visual Studio to SharePoint virtual directories
• SharePoint Script Editor Web Parts

I find these “technologies” to be a complete waste of time. Instead, I open Visual Studio (or IDE of your choice), and I create an HTML file that I will upload to my SharePoint site’s Site Assets library. The I hook-up my new file into a SharePoint web part page within a Content Editor Web Part’s Content Link property:

With this methodology, once your Content Editor Web Part is configured and your web part page is saved, you never have to struggle with checking-out your pages, worrying about refresh post-backs, non-checked-in versions, etc. To update your code, you simply update your HTML and re-upload it to Site Assets using the same name. ((I upload a ton…))

Because my file is an HTML page, I can have a CSS links, Script tag, HTML content, Style and Script blocks. My demo script includes 4 CDN hosted CSS/JS files, a single HTML tag, and a script block with the AJAX call and Kendo magic all rolled into one.

The Kendo

Instead of writing a treatise on how to instantiate a Kendo UI Grid, I will describe the flow of operations:

1. AJAX calls the SharePoint Search REST API and gets a JSON string.
2. The search result data is sent to a generic JS class that objectifies the results into a more usable format.
3. The object data is sent to a function that turns data into a grid.

Kendo needs a data source object, and we transform our search result object into an object Kendo needs:

var apiDataSource = new kendo.data.DataSource({
    data: results.items,
    pageSize: 5,
    total: results.totalResults,
});

Then within the Kendo Grid setup, columns are defined using the Managed Property names we found earlier as field names:

columns:
            [
                {
                    title: "Job Title",
                    field: "JobTitleOWSTEXT",
                },
                // etc.
            ],

The Whole Shebang

Here’s our Kendo UI Grid. What we created here is a “data first” type of grid, where some key features all happen via the magic of the Kendo UI Grid script itself. The data set retrieved from the SharePoint Search REST API call is all the data the grid will need. Paging, sorting, and filtering all happen on the client-side, without posting multiple requests to the server.

The big limitation here is that at most 500 search results we be returned from the API. Beyond 500 records, which is a lot of records to be returned from a REST call, you’d have to implement a Kendo UI Grid with server-side paging, sorting and filtering. Since the SharePoint Search REST API can handle Skip, Take, Sorting and Filtering, this server-side paging can be accomplished with code similar to what we built today.

Here’s the Grid Output:

Here’s the Code (my HTML file):

<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2016.2.607/styles/kendo.common.min.css" />
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2016.2.607/styles/kendo.blueopal.min.css" />
<script src="https://kendo.cdn.telerik.com/2016.2.607/js/jquery.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2016.2.607/js/kendo.all.min.js"></script>

<div id="contactsGrid"></div>

<script type="text/javascript">

    $(function () {

        // my O365/SP Sandbox
        var webUrl = "https://spr.sharepoint.com/sites/dev.john.wefler";

        // create the api url
        var fullUrl = webUrl + "/_api";

        var searchQueryUrl = fullUrl + "/search/query?" +
            "querytext='ContentType:Contact'" +
            "&selectproperties='JobTitleOWSTEXT%2cFirstNameOWSTEXT%2cTitle%2cCompanyOWSTEXT'";

        // data first
        $.ajax({
            method: "GET",
            url: searchQueryUrl,
            headers: {
                "Accept": "application/json; odata=verbose"
            },
            success: function (data) {
                var results = new SearchResults(data.d.query);
                renderGrid(results);
            },
            error: function (xhr, ajaxOptions, thrownError) {
                console.log("SharePoint REST API ajax call failed with status: " + xhr.status + " and error: " + thrownError);
            }
        });
    });

    var renderGrid = function (results) {

        var apiDataSource = new kendo.data.DataSource({
            data: results.items,
            pageSize: 5,
            total: results.totalResults,
        });

        $("#contactsGrid").kendoGrid({
            dataSource: apiDataSource,
            sortable: true,
            filterable: true,
            pageable: true,
            columns:
            [
                {
                    title: "Job Title",
                    field: "JobTitleOWSTEXT",
                },
                {
                    title: "Company",
                    field: "CompanyOWSTEXT"
                },
                {
                    title: "First Name",
                    field: "FirstNameOWSTEXT",
                },
                {
                    title: "Last Name",
                    field: "Title"
                }
            ],
        });
    };

    var SearchResults = function (queryResponse) {
        this.elapsedTime = queryResponse.ElapsedTime;
        this.suggestion = queryResponse.SpellingSuggestion;
        this.resultsCount = queryResponse.PrimaryQueryResult.RelevantResults.RowCount;
        this.totalResults = queryResponse.PrimaryQueryResult.RelevantResults.TotalRows;
        this.totalResultsIncludingDuplicates = queryResponse.PrimaryQueryResult.RelevantResults.TotalRowsIncludingDuplicates;
        this.items = convertRowsToObjects(queryResponse.PrimaryQueryResult.RelevantResults.Table.Rows.results);
    };

    var convertRowsToObjects = function (itemRows) {
        var items = [];

        for (var i = 0; i < itemRows.length; i++) {
            var row = itemRows[i],
                item = {};

            for (var j = 0; j < row.Cells.results.length; j++) {
                item[row.Cells.results[j].Key] = row.Cells.results[j].Value;
            }

            items.push(item);
        }

        return items;
    };

</script>

You're on a roll! Try these next:

Why You Need to Migrate .NET Applications to Containers