Power Automate - Excel - Update a row

[ecooney007]

Hey Guys,

I just wanted to add another post about updating a row in excel.  I read a bunch of stuff about this topic, and it was still a little confusing.

I then found this very helpful blog post by Ajith Madathil regarding get a row --> Retrieving a row from an Excel table using Power Automate (powerautomatetraining.co.uk) 

https://www.powerautomatetraining.co.uk/post/microsoft-flow-excel-get-row

Here are a few others that were also very helpful;

Solved: Help using Flow to update row in Excel - Power Platform Community (microsoft.com) 

https://powerusers.microsoft.com/t5/Connecting-To-Data/Help-using-Flow-to-update-row-in-Excel/td-p/200530 

Dynamically Updating Excel Rows - Power Platform Community (microsoft.com) 

https://powerusers.microsoft.com/t5/Building-Flows/Dynamically-Updating-Excel-Rows/td-p/77972 

Solved: How to use Flow to update a single Excel cell when... - Power Platform Community (microsoft.com) 

https://powerusers.microsoft.com/t5/Connecting-To-Data/How-to-use-Flow-to-update-a-single-Excel-cell-when-a-task-is/td-p/235564

Here is the Power Automate help page also;

Excel Online (Business) - Connectors | Microsoft Docs 

https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#update-a-row 

I wanted to kind of pull together all of the things found in these posts to go step by step in order to...

Update cells in a sheet that are on different rows

The MS FLOW I was trying to build was triggered by an intake form (FORMS - Microsoft) that has information related to a product that was brought back to the warehouse to be serviced. When the user hits submit on the form, the MS FLOW is triggered, a row is created in a TEAMS LIST, a new spreadsheet is created in a TEAMS FOLDER which is a copy of a template spreadsheet, and the information from the form need to populated at the top of the spreadsheet.

The top header info portion of the template spreadsheet is below. The yellow, empty cells are the ones I need to populate from

the MS FLOW after I create a copy of the template and rename it.

I really wasn't clear on what I needed to do to just update a cell in that top area.

After reading the posts above, it seemed like the MS FLOW Excel "Update a row" action was built more for updating rows in a 

tabular format, not necessarily picking and choosing a random cell like A:6 and updating it.  This makes sense as I'm sure, this would be

95 percent of what Power Automate users need.

Requirements

1. TABLE - I know that in order to update or work with any Excel data/cells from MS FLOW, the area surrounding that data in the spreadsheet MUST be in a "Table".

2. COLUMNS, ROWS - I also knew that the easiest way to get the data updated was to somehow have it in a more straight-forward column, row format.

3. UNIQUE KEY FOR EACH ROW - Within the column, row format, its recommended that the first value of the first column be unique,

so that the "Update a row" action can find the correct row you want and then update the column value for that row.

4. LOOP - From the examples I saw, I would also probably need to put the updates inside a LOOP Control Action for all the rows I needed to update.

With that being said, what I decided to do was to create a new Excel Sheet in the template called "Mappings" that would act as a hidden data mapping sheet, where I could do the updates, meet all the requirements above, and then just link those values back to the main spreadsheet. And again this data mapping table in this new sheet itself needs to be wrapped in a "Table".

So the new raw data mapper sheet looks like this;

I named the "FieldName" row values in the first cell with unique names that are the same names exact names on the main Sheet above.

These are fields that I want to update on that main sheet.

I then had to wrap the data mapper table inside a "Table" by selecting the data and clicking "Insert", "Table" in MS Excel. I also clicked on the "My table has headers" checkbox.

I then went to the main spreadsheet and created a cell link to each field I wanted updated on the "Mappings" sheet;

In other for the value of "Part Dealer:" which is in cell "E:5" above, I entered =IF(ISBLANK(Mappings!$B$2),"",Mappings!$B$2).

This pulls in the value from the "Mappings" sheet from cell $B$2 into the main sheet.

I repeated this for all the cells I want values updated in main sheet.

The "Mappings" sheet, when all fields are populated with data,' looks like;

The main sheet then looks like the following with all fields linked to the "Mappings" sheet;

When I save the template I just hide the "Mappings" sheet so the users don't need to worry about it.

So that takes care of the template spreadsheet and how the values can be update in main sheet.

But how do we make the MS FLOW "Update a row"?

The "Update a row" MS FLOW

In my MS FLOW to "Update a row", One of the first things I do after I add the form data to a list row, is create a copy of the template, I use the "Sharepoint" action "Get file content using path", you can see the path below refers to the specific "TEMPLATE NAME" in that folder;

I then create a new file which is a copy of the template, and rename it;

Now we start the process of updating a row in the new spreadsheet.

1. Create KEY VALUE OBJ after you add the item to the list - After the initial trigger in the FLOW, we Initialized a variable called "KEY VALUE OBJ" (which is type object) which will hold the same field names in the "FieldName" column from the "Mappings" Sheet, as well and the associated values from the form data. After you add the form data to a list, set values on "KEY VALUE OBJ" variable and inside the curly braces, you type in the "Mappings" sheet field names, and then add the values from the "Create item" action - the actual names of my fields from "Create item" are blurred out, but essentially you could name them the same as the field names if you wanted.  This variable will become important later as we loop through the rows.

2. Add action "List rows present in a table" - get a list of rows in the table we are going to update - this is the table in the "Mappings" sheet and I named it "Table2" in the spreadsheet.  Note - The "Id" variable in the "* File" field below is from the "Create file" action above.

3. Create the "Apply to each" LOOP action - what we do now is create the "Apply to each" LOOP action to each row in the "Mappings" sheet in our new spreadsheet (copied from the template).  You just take the "value" from the "List items in a spreadsheet" action and put that in the "*select an output from the previous steps" field;

4. To be continued...

[skibär]

Wow, I want to do pretty much exactly the same!

Could you finish this flow? Would you share some details?

Thanks!

[noorfar]

@ecooney007 thank you for sharing this, I'm trying to automate filling excel report not in a tabular form. Can you please provide part 2 for this

[takolota]

Thanks @ecooney007 

By the way, if you have a method or template to share, you can also make a post in the Power Automate Cookbook section of the community:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/bd-p/MPA_Cookbook

[Anonymous]

Hi Everyone, 

I'm looking to get assistance on how to use power automate function to update and copy certain columns from 2 excel files a new third sheet. 

In more detail: I have created a new excel sheet which i want to be my goto file, labelled as Final Excel Sheet. This final excel sheet will pull in data from both excel sample data sheet 1 and excel sample data sheet 2. Both sample data sheets have varying data but common column in both is Name. 

I would like to automate this process as the files "sample data sheet 1 & 2" in reality are updated weekly. 

thank you for your help and guidance in this inquiry.