Menu Close

Search in SharePoint from PowerApps

(ESTIMATION TIME: 6 MINUTES)

Inspired by this post I realized that built-in connectors does not allow to utilize SharePoint Search. So theoretically it is not possible to use SharePoint search from PowerApps. However using Flow as a middle layer you can call SharePoint Search and parse results for your needs. Let’s see how to do that.

Why you may find SharePoint Search useful in your app?

Let’s consider is it worth to have such possibility as using SharePoint Search from your PowerApps app. To answer this question let’s see what we can and cannot do using built-in connectors.

You can:

  • Get content of any list or library in a single SharePoint site
  • Read, Update, Delete elements of list or library
  • Use specific subsets of items (filtering, sorting) in delegate manner
  • Get content of Office 365 Groups
  • Get OneDrive content (only excel files)

You cannot:

  • Find all items of a specific Content Type across whole tenant
  • Get elements from list or library in a site – even if the list or library was created after the connector has been declared
  • List SP sites in your tenant
  • Get OneDrive for Business content (all kind of)

All above are limitation coming from OOB SharePoint connectors. So if you want workaround them this post will tell you how to achieve it.

PowerApps SharePoint Search Architecture

We need 3 components:

  • PowerApps: acts as front-end, provides screen and controls to user and also display results,
  • Flow: acts as back-end, call SharePoint search (also keeps SPO url) and pass results to PowerApps
  • SharePoint: contains all the data of course

Those components will communicate each other in following manner:

  1. PowerApps uses Flow connector to make a call to it
  2. Flow uses “Send an HTTP request to SharePoint” and then…
  3. …parse returned result to JSON
  4. Flow join all rows as string variable and pass it as to PowerApps as respond
  5. PowerApps filters out the data it needs

Let’s start building it! To not make this post too much long I’ll focus only on most important part skipping parts like header creation or pagination functionality.

Follow this link to download complete demo app (remember that url to SPO is in the flow)

Step 1: Create a PowerApp

First we need to create an app, add screen to it and add controls to the screen (if you don’t know how to do any of those please check out this post). Some parts that may need additional explanation:

  • numRowLimitNumber – number of items to return
  • btnCallFlow – this is invisible button that call Flow on OnSelect event. Other controls (like previous/next page buttons) can fire it using Select() function instead of duplicating code in it.
  • galSPSearchResultItems – gallery displaying results items
    • imgOpenInNewWindow – clicking it will open SharePoint item in browser
    • lblSPItemTitle – displays item Title
    • lblSPItemUniqueId – displays item UniqueId
    • lblSPItemRank – displays item ranking sum. Because why not 😉

Now let’s configure behavior of our controls.

  • On OnSelect action of the “Search” button put as follow:
Set(gblItemsToSkip,0);
Set(gblPaginationVisibility,false);
Select(btnCallFlow)
  • As you can see it fires btnCallFlow. On OnSelect action of that button we will call the flow
Clear(colSPSearchResultItems);
Set(
    gblRowLimitNumber,
    numRowLimitNumber.Text
);
Set(
    gblSearchResults,
    Searchforitemsintenant.Run(
        txtSearchPhrase.Text,
        numRowLimitNumber.Text,
        Text(gblItemsToSkip)
    )
);
<HERE WE WILL PUT CODE TO PARSE SP SEARCH RESULTS>
Set(
    gblPaginationVisibility,
    true
)

Now let’s add flow to our app. To do that click:
Action tab -> Flows button -> “Create a new flow”

Step 2: Create a Flow

On Microsoft Flow side add variables that will keep input parameters from PowerApps. Then add “Send and HTTP request to SharePoint”.

The Uri attribute contains function that replace apostrophe (‘) to empty sign in case user put ‘ in the search phrase (accidentally or intentionally) which will break the query:

_api/search/query?querytext='@{replace(variables('SearchQuery'),'''','')}'&clienttype='ContentSearchRegular'&selectproperties='UniqueId, Title, OriginalPath'&rowlimit=@{variables('RowLimit')}&startrow=@{variables('RowsToSkip')}

Next add Parse JSON with Body output in Content field and a Schema.

The schema however is a bit tricky because using sample (i.e. from search REST call using browser or Postman) may not include all variations of results (that was my case). So after quick debugging I’ve successfully created following schema – feel free to copy-paste it.

{
    "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": {},
                        "Properties": {
                            "type": "array",
                            "items": {
                                "type": "object",
                                "properties": {
                                    "Key": {
                                        "type": "string"
                                    },
                                    "Value": {
                                        "type": "string"
                                    },
                                    "ValueType": {
                                        "type": "string"
                                    }
                                },
                                "required": [
                                    "Key",
                                    "Value",
                                    "ValueType"
                                ]
                            }
                        },
                        "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"
                                                        },
                                                        "Value": {
                                                            "type": [
                                                                "null",
                                                                "string"
                                                            ]
                                                        },
                                                        "ValueType": {
                                                            "type": [
                                                                "null",
                                                                "string"
                                                            ]
                                                        }
                                                    },
                                                    "required": [
                                                        "Key",
                                                        "Value",
                                                        "ValueType"
                                                    ]
                                                }
                                            }
                                        },
                                        "required": [
                                            "Cells"
                                        ]
                                    }
                                }
                            }
                        },
                        "TotalRows": {
                            "type": "integer"
                        },
                        "TotalRowsIncludingDuplicates": {
                            "type": "integer"
                        }
                    }
                },
                "SpecialTermResults": {}
            }
        },
        "Properties": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "Key": {
                        "type": "string"
                    },
                    "Value": {
                        "type": "string"
                    },
                    "ValueType": {
                        "type": "string"
                    }
                },
                "required": [
                    "Key",
                    "Value",
                    "ValueType"
                ]
            }
        },
        "SecondaryQueryResults": {
            "type": "array"
        },
        "SpellingSuggestion": {
            "type": "string"
        },
        "TriggeredRules": {
            "type": "array"
        }
    }
}

At the end, we just have to join rows (using some special separator like “/n” which is unique enough) and pass it back to PowerApps along with totalRows number

If you did everything properly your Flow should look like this

Now we can get back to PowerApps and parse the results.

Step 3: Parsing results in the PowerApps

Flow pass result to PowerApps in a string field (called “results”). But we cannot use it in a gallery right away. Just look at it:

{"Cells":[{"Key":"Rank","Value":"17.1283779144287","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17594717461461","ValueType":"Edm.Int64"},{"Key":"Title","Value":"Michał Guzowski Team Site","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595142086123","ValueType":"Edm.Int64"},{"Key":"Title","Value":"pl","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595383574480","ValueType":"Edm.Int64"},{"Key":"Title","Value":"en","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595390074495","ValueType":"Edm.Int64"},{"Key":"Title","Value":"fr","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595412154536","ValueType":"Edm.Int64"},{"Key":"Title","Value":"da","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595412284536","ValueType":"Edm.Int64"},{"Key":"Title","Value":"de","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595451564436","ValueType":"Edm.Int64"},{"Key":"Title","Value":"sv","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595498094501","ValueType":"Edm.Int64"},{"Key":"Title","Value":"lv","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595513184435","ValueType":"Edm.Int64"},{"Key":"Title","Value":"ro","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0754928588867","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17594717461464","ValueType":"Edm.Int64"},{"Key":"Title","Value":"How To Use This Library","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}

How to extract specific parts of that?

  • Split string with our special delimiter: “/n”
    • For each string part we split it once again using following scheme:
      • “_FieldName_”,”Value”:
      • I.e. for Title field it will looks like this “Title”,”Value”:
      • Also we need to encode quote mark (“) so example after encoding looks like this: Char(34)&”Title”&Char(34)&”,”&Char(34)&”Value”&Char(34)&Char(58)&Char(34)
    • We take second element (above split always returns only 2 elements) and split one more time using following scheme:
      • “,”ValueType
      • After encoding it look like this: Char(34)&”,”&Char(34)&”ValueType”
    • We take first element (above split always returns 2 elements) and add it as value of a specific column.

Wow, felt a little dizzy? Don’t worry. Just copy below code, paste it in your app and replace name “Title” with field name you want to extract.

ClearCollect(
    sposearchitems,
    AddColumns(
        Split(
            Items,
            "/n"
        ),
        "Title",        First(Split(Last(Split(Result,Char(34)&"Title"&Char(34)&","&Char(34)&"Value"&Char(34)&Char(58)&Char(34))).Result,Char(34)&","&Char(34)&"ValueType")).Result
    )
)

In my case (for fields UniqueId, Title, OriginalPath and Rank) my parse operation looks like this:

Step 4: Display results

Finally! We made up to this part. Just set the values of labels in the gallery and enjoy the results!

For me the final result looks like below but your can be totally different (and better than mine ;] )

THAT’S IT! Wasn’t that hard, was it? 😉 If it was, remember that you can download my Demo SharePoint Search PowerApps application to compare or just copy paste some parts – just click here!

Hope you find this post useful. If so please share it and comment if you like.

Thanks and have a great coding 🙂

Update 22.02.2019 – If you’re curious if you can use Flow for parse computations I’ve done such comparison in my next blog post.

Related Posts

26 Comments

  1. Emma

    Thank you so much for the detailed instruction and a template.

    I cannot display ‘modified’ date. ( i could get file name, original path, etc.)
    how can i get that value?

    I did this:

    “Modified”, First(Split(Last(Split(Result,Char(34)&”Modified”&Char(34)&”,”&Char(34)&”Value”&Char(34)&Char(58)&Char(34))).Result,Char(34)&”,”&Char(34)&”ValueType”)).Result,

  2. Emma

    I want to get documents from a specific folder. currently when i run this, it displays all documents from a tenant.
    if i want to filter a specific site, how can i do so?
    i did specified a specific site on power automate>send a http request to sharepoint> site address. but it still gets results from a whole tenant.

    Could you please help me?

  3. Guy

    Hi Michal,

    This was a HUGE help. Thank you so much!

    My knowledge of regex is pretty weak so there is one thing that is tripping me up. How do I deal with null values? When parsing the results in PowerApps, any fields that are null do not parse correctly because the “Value” schema changes.

    Example: Job Title is present
    —————————————-

    JobTitle
    IT Manager
    Edm.String

    Example: Job Title is null
    —————————————-

    JobTitle

    Null

  4. Akhan

    Thank you for this post, its been very helpful. If you could share your thoughts on how to extract the SP custom metadata fields that the user is interested in.

  5. Christophe ANGOT

    Source code do not seem to be available anymore (I subscribed to the newsletter).
    Is it possible to get access to it ?

    Thanks.

  6. Omar

    Hi, Does it apply also to search for a specific content of the files and return those file with that matching content?

  7. Julio

    Hi! I’m having some trouble with the formation step, do you have any idea of what might be wrong?

    This is my code when I press the button:

    Clear(Items);;
    Set(resultFlow;SharepointBuscaArquivos.Run(TextInput1.Text;RowLimit;RowSkip));;
    ClearCollect(
    Items;
    AddColumns(
    Split(
    resultFlow.results;
    “/n”
    );
    “Title”;
    First(Split(Last(Split(Value;Char(34)&”Title”&Char(34)&”;”&Char(34)&”Value”&Char(34)&Char(58)&Char(34))).Value;Char(34)&”;”&Char(34)&”ValueType”)).Value;
    “OriginalPath”;
    First(Split(Last(Split(Value;Char(34)&”OriginalPath”&Char(34)&”;”&Char(34)&”Value”&Char(34)&Char(58)&Char(34))).Value;Char(34)&”;”&Char(34)&”ValueType”)).Value
    )
    )

    The gallery display the value not formated

  8. Jade

    Getting an Error in the Power Automate for the Parse JSON action: [
    {
    “message”: “Invalid type. Expected String but got Null.”,
    “lineNumber”: 0,
    “linePosition”: 0,
    “path”: “SpellingSuggestion”,
    “schemaId”: “#/properties/SpellingSuggestion”,
    “errorType”: “type”,
    “childErrors”: []
    }
    ]

    When I type a keyword to search for. Do you know how I can fix this?

  9. Jade

    How to limit searches to only the SharePoint site? It’s giving me items that are across the tenant, but I only want to see items on a specific site.

Leave a Reply

Your email address will not be published. Required fields are marked *