Power Apps leveraging the SharePoint Search API

In this blog post  I will guide in building a power app that trigger the SharePoint Search APi through Power Automate.

So first let's have a look on the Power App

Basically we have one screen, on that screen an input box a button and a gallery.

The button triggers a power automate flow with the content of the search box as parameter

an put the results in a collection called _apiResults

Then the Gallery is just binded to the _apiResults collection.

Before we go to the details of the Gallery let's see the SPSearchAPi flow that the Search button triggers

The flow is triggered by Power Apps, call the Sharepoint Search APi with a HTTP request, parse the results and then send a response.

Let's look into the HTTP request

Basically here we are calling the search api with a search termbut we are specifying with IsDocument:true and FileExtension<>apsx that we want only documents that are not aspx page files.

The we are selecting only the Title, Path, Version and ParentLink of every documents filtered by last modified date.

Then with the Parse Json we will only be getting the part of the response that matters to us with this schema

{
    "type": "object",
    "properties": {
        "odata.metadata": {
            "type": "string"
        },
        "ElapsedTime": {
            "type": "integer"
        },
        "PrimaryQueryResult": {
            "type": "object",
            "properties": {
                "CustomResults": {
                    "type": "array"
                },
                "QueryId": {
                    "type": "string"
                },
                "QueryRuleId": {
                    "type": "string"
                },
                "RefinementResults": {},
                "RelevantResults": {
                    "type": "object",
                    "properties": {
                        "GroupTemplateId": {},
                        "ItemTemplateId": {},
                        "ResultTitle": {},
                        "ResultTitleUrl": {},
                        "RowCount": {
                            "type": "integer"
                        },
                        "Table": {
                            "type": "object",
                            "properties": {
                                "Rows": {
                                    "type": "array",
                                    "items": {
                                        "type": "object",
                                        "properties": {
                                            "Cells": {
                                                "type": "array",
                                                "items": {
                                                    "type": "object",
                                                    "properties": {
                                                        "Key": {
                                                            "type": [
                                                                "string",
                                                                "null"
                                                            ]
                                                        },
                                                        "Value": {
                                                            "type": [
                                                                "string",
                                                                "null"
                                                            ]
                                                        },
                                                        "ValueType": {
                                                            "type": [
                                                                "string",
                                                                "null"
                                                            ]
                                                        }
                                                    },
                                                    "required": [
                                                        "Key",
                                                        "Value",
                                                        "ValueType"
                                                    ]
                                                }
                                            }
                                        },
                                        "required": [
                                            "Cells"
                                        ]
                                    }
                                }
                            }
                        },
                        "TotalRows": {
                            "type": "integer"
                        },
                        "TotalRowsIncludingDuplicates": {
                            "type": "integer"
                        }
                    }
                },
                "SpecialTermResults": {}
            }
        }
    }
}

As you can see the api reply in a way that I like to call it vertically, meaning that it will reply for every document a Row object with an array of Cells object that contains the name and the value of the properties selected.

To consume it like that in Gallery in Power App it is not convenient and believe me I tried it :)

So we have to pivot in a way the structure of the results and this is why the Select composit function comes handy.

With this composite function we are able to select the items we want 

We will map every index of Cells array to a property, here the Title is mapped to item()?['Cells'][2]['Value'] , Path is mapped to item()?['Cells'][3]['Value'] etc... We can use this technique because the search api always returns the object in the same order.

And finally we reply with the output generated by the Select composite action

With this schema structure

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Title": {
                "type": "string"
            },
            "Path": {
                "type": "string"
            },
            "UIVersionStringOWSTEXT": {
                "type": "string"
            },
            "ParentLink": {
                "type": "string"
            },
            "FileExtension": {
                "type": "string"
            },
            "LinkingUrl": {
                "type": "string"
            }
        },
        "required": [
            "Title",
            "Path",
            "UIVersionStringOWSTEXT"
        ]
    }
}

Now back to the Power App, the Gallery displays the Title, Version and document type. It has two icons as well, one for opening the document in the browser and one for opening the location of the document.

To open a document in the browser knowing its path just do a

Launch(ThisItem.Path&"?web=1")

 

The full code is available on my github at

https://github.com/alaabitar/powerapps/blob/master/PowerAppSearchSPAPI_20200512133953.zip

That is it ! Happy coding !

Stay safe

 

Comments (3) -

Szymon Bochniak 5/12/2020 2:52:57 PM

Do you know if this API calls are covered in Power Apps Standard license?

The SharePoint API call is within the standard license but the Response Connector is premium.

Thank you for this!  It's exactly what I needed to be able to locate an original document based on the Document ID (which should be an out of the box connector).

For reference the API call I ended up with was:
_api/search/query?queryTemplate='DlcDocId:<**** Document ID ****>'&selectProperties='Title,Path,UIVersionStringOWSTEXT,ParentLink,FileExtension'&sortlist='LastModifiedTime:descending'

It would be nice if it also returned the item ID in the library but given it has the path I can deduce that.

Add comment