Update Rows in Microsoft Excel and CSV Files with Power Automate

In this Automate Excel series blog, we will look at how to update rows in your Excel files using Flowr’s ‘Update Rows in Excel‘ action. The action allows you to update rows in your Excel files in data tables and rows that aren’t contained in a table format!

Jump to:

• Scenario
• Excel file
• The Flow
• Get Items action
• Get File Content action
• Parse JSON action
• Update Rows in Excel action
• Specifying rows and columns
• Results
• Video

Scenario

Consider the following scenario: a ‘Sales’ list in SharePoint is updated by the sales team each time a new sale is completed. Once a month, the data contained within the ‘Sales’ SharePoint list must be transferred to a ‘Sales’ Excel file, which the finance team uses for reporting and other purposes. This is not necessarily a real-world scenario but a good basis for demonstrating how the ‘Update Rows in Excel’ action works.

Excel file

The Excel file that needs updating looks like this:

The SharePoint Sales list looks like this:

The Flow

For this sample solution I’ve created a manually trigger Power Automate Flow, in real-world solution you would probably use a scheduled trigger.

Get Items action

First, we use the SharePoint ‘Get items‘ action to get all the items from the SharePoint ‘Sales‘ list.

For the Excel file, we only need to collect the number of customers for each product. We can achieve this by filtering the results of ‘Get items’ by each product; the number of customers will be equal to the length of the filtered array.

As I am only using three products, this is a viable solution. In a real scenario, this may be unrealistic, and it will be better to filter the products dynamically.

Get File Content action

The following action is to get the Excel file content. I am using OneDrive, so I can use the ‘Get file content‘ action and target the ‘Sales’ Excel file.

The ‘Update Rows in Excel‘ action accepts data as JSON, so we need to transform the SharePoint list data into a JSON object. As I am only updating one column, I will use a ‘Compose‘ action to create the JSON array rather than appending it to an array variable.

Parse JSON action

The last step in preparing the data for the Flowr action is to use ‘Parse JSON‘. This transforms the data into JSON format so it is ready to use in the action. If you use an array variable for your row input data, you must also parse it. The content will be your row input data (either from the compose or from a variable). In this scenario, to generate from sample you can simply copy and paste what you have put into the ‘Compose‘ action, if you are using a variable you will need to add a compose step and run the flow so you can copy and paste the results.

The expression I am using to get the lengths of the filtered arrays are:

length(body('Filter_array_-_Vertr'))

length(body('Filter_array_-_Flowr'))

length(body('Filter_array_-_Trigr'))

They have been put in this order because it is the order of the rows in the Excel file.

Update Rows in Excel action

Now, we can use the ‘Update Rows in Excel’ action!

You need to use the outputs of the ‘Parse JSON‘ action for the ‘Row Data‘ input. If this doesn’t show up in your list of dynamic content, you can write the expression manually:

outputs('Parse_JSON')

Specifying rows and columns

The data in my Excel file isn’t in table format, so I need to specify the row and column from which the data needs to be updated. I need to make sure the values being updated are numeric; otherwise, my chart won’t show any data. To do this, I have set the ‘Convert Numeric and Date’ input to Yes. If you have formatted the number or currency cells you are updating, you will also need to set this value to Yes so the formatting can be applied.

When using the Flowr Excel actions, they make a copy of the original file, which contains the updated file. This means you can either create a new file with the changes or update the original file. As we are updating the Excel file in this solution, I am going to update the original file in OneDrive.

Results

Let’s look at the results!

This was the Excel file before the flow ran:

This is the file after the flow has run:

As we can see from the results, the data has been updated, which has, in turn, updated the chart.

Video

View Sophie’s companion video: Update Rows in Excel with Power Automate.