(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:
- 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.
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,
Hm, what do you have in REST query to SP search? Is there a “select” query parameter?
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?
Hey Emma, thank you for your question. Try adding Path parameter to query string: http://host/site/dev1/_api/search/query?querytext='path:http://host/site/dev1‘
Hi Michal, thank you for your reply!
When you put that string, http://host/site/dev1/_api/search/query?querytext='path:http://host/site/dev1‘,
how do you combine with “@{replace(variables(‘SearchQuery’),””,”)}”, which is the search box input.
I did:
http://host/site/dev1/_api/search/query?querytext='path:http://host/site/dev1‘ =’@{replace(variables(‘SearchQuery’),””,”)}’
But doesn’t look like it is working.
Could you help me with this one?
does the @{replace(variables(‘SearchQuery’),””,”)} returns you the correct value?
yes it does
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
Just append |null.
eg. [0-9][a-z]|null
btw I strongly recommend using this tool for regular expressions: https://regexr.com/
Have you ever meet a problem like that:
Only Title column have values – rest from query return “Value m:null=”true””
Yes, it may occur once the column has been selected as mandatory.
Maybe im just stupid, can you tell me the variable value on the flow?
You’re definitely not. Check my previous question.
I downloaded the demo and only changed the sharepoint URL on the flow but its not working
Is there any error? What’s not working?
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.
Did you investigate the response of the standard SP REST/GRAPH API?
Source code do not seem to be available anymore (I subscribed to the newsletter).
Is it possible to get access to it ?
Thanks.
My bad, it works
Hi, Does it apply also to search for a specific content of the files and return those file with that matching content?
Yes. SharePoint Search can do that however please be you configure it properly.
Is it possible to create the search API with Custom Connector
Yes! It is a very good idea actually.
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
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?
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.