cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ArshUser
Helper II
Helper II

Writing to a dynamically created excel file

So basically I am creating an excel file (with a specific template) inside a Power Automate Flow. I have an Excel template already stored in a folder in OneDrive and I am using "Get File contents" from the template excel and pasting them into the dynamically created Excel file (Using the "Create File" action and giving the "Body" as file contents). This is going fine.

 

This is my template file:

ArshUser_0-1645531023976.png

 

Just using a simple Table for demo purposes.

 

Now I want to paste data into this dynamically created excel sheet. 

 

"Add row into the table" action:

ArshUser_1-1645531116023.png

I assume it does not show the table columns because the excel sheet was dynamically created. 

Is there any other way to do this?

 

I need to access the columns of table so that I can fill them in.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
mattias1172
Responsive Resident
Responsive Resident

As far as i know, this won't work since the file/table does not exist yet. Power Automate can't lookup the columns. 

 

This can be done dynamically with json however. It's a little finicky, but it works. 

 

This is the layout of the test flow I used:

mattias1172_7-1645547409804.png

 

Your trigger and likely filename would both be dynamic. 

 

Just as your current flow, I used the "Get file content" action to get the content of the template excel file. In my case, it has a single table with ID, Name, and Email:

mattias1172_1-1645546798728.png

 

We use the "create file" action and pass in our filename and the content from the previous action:

mattias1172_2-1645546927233.png

 

Next, we use a compose action to format the json. The row schema is "<ColumnName>":"Content". You can use dynamic content here instead of these hard set strings I used. 

mattias1172_3-1645546988816.png

 

I tried with just the above and then using the "add a row into table" action. Even though I named the table in the template "DataTable", Power Automate was never able to find the created table. To get around this, I added a "Get tables" action which will get an array of all tables in the file. In this case, there's just 1. I passed in the "path" dynamic content from the "Create file" action. 

mattias1172_4-1645547150724.png

 

Because this outputs an array, the "Add a row into table" action needs to be setup in an "Apply to each" loop. In our case, this loop runs just once since theres only 1 table. Inside the "Add a row into table" action, we pass in the "Path" dynamic content again, we get the "Name" dynamic content from "Get tables" and we pass in our json from the "Compose" action. 

mattias1172_5-1645547266564.png

 

If you have multiple rows to add, you'd need to setup another loop here for mutliple "Add a row into a table" actions. 

 

The end result is a new file called "Test_Table.xlsx" (from my generic variable name) with the json added to the table. 

mattias1172_6-1645547349230.png

 

 

View solution in original post

4 REPLIES 4
mattias1172
Responsive Resident
Responsive Resident

As far as i know, this won't work since the file/table does not exist yet. Power Automate can't lookup the columns. 

 

This can be done dynamically with json however. It's a little finicky, but it works. 

 

This is the layout of the test flow I used:

mattias1172_7-1645547409804.png

 

Your trigger and likely filename would both be dynamic. 

 

Just as your current flow, I used the "Get file content" action to get the content of the template excel file. In my case, it has a single table with ID, Name, and Email:

mattias1172_1-1645546798728.png

 

We use the "create file" action and pass in our filename and the content from the previous action:

mattias1172_2-1645546927233.png

 

Next, we use a compose action to format the json. The row schema is "<ColumnName>":"Content". You can use dynamic content here instead of these hard set strings I used. 

mattias1172_3-1645546988816.png

 

I tried with just the above and then using the "add a row into table" action. Even though I named the table in the template "DataTable", Power Automate was never able to find the created table. To get around this, I added a "Get tables" action which will get an array of all tables in the file. In this case, there's just 1. I passed in the "path" dynamic content from the "Create file" action. 

mattias1172_4-1645547150724.png

 

Because this outputs an array, the "Add a row into table" action needs to be setup in an "Apply to each" loop. In our case, this loop runs just once since theres only 1 table. Inside the "Add a row into table" action, we pass in the "Path" dynamic content again, we get the "Name" dynamic content from "Get tables" and we pass in our json from the "Compose" action. 

mattias1172_5-1645547266564.png

 

If you have multiple rows to add, you'd need to setup another loop here for mutliple "Add a row into a table" actions. 

 

The end result is a new file called "Test_Table.xlsx" (from my generic variable name) with the json added to the table. 

mattias1172_6-1645547349230.png

 

 

DamoBird365
Microsoft
Microsoft

Hi @ArshUser 

 

You need to supply an object - have a watch from here Create a new Excel File in Power Automate and dynamically populate with a Table and Rows #Excel - Yo...

 

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

Hey @mattias1172,

Thankyou so much for this!
Totally worked for me.

mattias1172
Responsive Resident
Responsive Resident

Glad it worked. 

 

Strange that even though the table name is exactly the same (since the raw content is being copied from the template) using the table name as a normal string doesn't seem to work in the "Add a row into a table" action. 

I'm sure with a bit more troubleshooting, it could be done without the "Get tables" action. 

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (3,334)