Using the SharePoint Search API with Power Automate

Power Automate can call the SharePoint Search API but working with Search Results isn’t for the faint hearted. Here is a little journey I went on recently when I had a need to interact with Search results with Power Automate.

To get results from Search, start with an API call using the Send an HTTP request to SharePoint action

The Uri value is the API request including the querytext. In this example I am searching for documents containing the search term “LAB” where the filetype is .DOCX.

The API call returns results in JSON format. Using the Parse JSON action, makes it possible to use elements of the JSON result in the workflow. Initialising a variable (or using a Compose action) allows the JSON output to be converted to an Array. To get an array of Search results, use this expression to get data into an array.

body(‘ParseJSON’)?[‘d’]?[‘query’]?[PrimaryQueryResults’]?[‘RelevantResults’]?[‘Table’]?[‘Rows’]?[‘results’]

Each individual result has 54 attributes. The format has a Key, Value and ValueType. Some useful Keys include:

  • Title
  • Author
  • Size
  • OrginalPath

Now the results are in an Array, they can be accessed using the Array Index value. For example using Array ‘SearchResultCells’ (defined in the step above), we can reference the third item using this format.

variables(‘SearchResultCells’)[3]

To reference an item within a result, use this expression format. Rows contain Cells, Cells contain results.

body(‘ParseJSONtoGetRows’)?[‘Cells’]?[‘results’]

Now you have an individual result element such as the Title, Path, Author etc, you can do some interesting things. In my case, I needed to generate a list of documents that will be bundled up together from across multiple sites and libraries.

I also experimented with writing the results to a SharePoint List. This could be used for a variety of tasks such as locating documents with a specific managed property or content type, auditing or other content review requirements.

One tip, the workflow can take a while to run. I used a variable to limit the number of times my workflow iterated through the array data. This saved me a lot of time as I really only needed to check 2-3 results while developing my workflow. Once done, I removed the restriction.

Tips:

Remember that Search is security trimmed and the results that are returned will include both OneDrive and SharePoint unless you limit the search scope.

Use query string filters such as filetype:PDF to return specific types of results.

Consider the different methods you can use to start the Flow

  • Manual triggers e.g. on a selected item
  • Automatic triggers e.g. when an item is added to a List
  • Buttons in PowerApps
  • Scheduled Flows
  • Webhooks (premium)

Remember you could return a lot of results, so make sure you are prepared for this. One approach is to read the results into an array but only process the first X items, rather every result.

Additional resources

Power Automate Data Operations

SharePoint Search API Reference

Tech Community JSON article that helped me

2 thoughts on “Using the SharePoint Search API with Power Automate

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s