Using Power Automate as a Data Integration Tool with Excel (for Bulk Processing)

Power Automate connects into multiple different platforms and systems, making it a very powerful integration tool. Power Automate has been positioned by Microsoft as a platform that aims to facilitate Robotic Process Automation (RPA). Although there is still some work to be done before the Power Automate toolset can match competitors, it does make for a very powerful integration tool.

For example, if you were to update an Excel spreadsheet by adding multiple lines (possibly updating certain lines with contents from SharePoint), it would take significantly more time than handing off the bulk of the processing to an API. This would require developing your own API (in the form of a Web API hosted as an Azure API Service or Azure Function).

For the purpose of this post, I will be demonstrating how to use Power Automate to integrate SharePoint with an Azure Function that does bulk processing. To set up the Azure Function that will handle the bulk process, check this post out.

What do we need?

To get this working we will need some sort of trigger, which we will make Manual for now, which you can replace with a SharePoint trigger, for example. We will need to make use of HTTP Request action and Create SharePoint File action. Please note that the HTTP Request action is a Premium connector.

Getting Started

Navigate to Power Automate and create a blank Instant cloud flow:

Name your flow then select Manually trigger flow before hitting Create.

Add an action and search for HTTP. Click on the HTTP action:

To configure the HTTP action, assign the HTTP method, the URL and the body:

Hit Save and then Test the flow manually:

You should expect to be seeing this level of gibberish in the body of the output once the test has run successfully (with status code 200):

If you received a 401, make sure you’ve added your authorisation headers or that your Function is set to Authorization.Anonymous (and not Authorization.Function). If you are receiving a 204 code, it means that no content is being returned, so make sure that you are able to read the file from Blob storage. That’ll be an issue on the Azure Function (or API) side of things.

We will need to check that the status code is 200 before adding the SharePoint create file action:

Now let’s configure this action with the Site URL, folder path, file name and file content. Due to the fact that we will be using the output body from the HTTP request to populate the SharePoint file content, we will need to add an apply to each loop that encloses the Create file action:

The expression to convert the output result to file content is:

base64ToBinary(items('Apply_to_each:_result_in_output_body'))

Now manually test the flow to see if your file creates correctly. You should expect to see the following:

Did this work for you? Feel free to email me at jacqui.jm77@gmail.com.