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:
- PowerApps uses Flow connector to make a call to it
- Flow uses “Send an HTTP request to SharePoint” and then…
- …parse returned result to JSON
- Flow join all rows as string variable and pass it as to PowerApps as respond
- 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.
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.
- For each string part we split it once again using following scheme:
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.
