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

write to excel table flow only working the first time its run

I have an excel file with a table I want to write too. Once my data is written, I email it to the user, then overwrite the excel file with an identical blank copy so that I have an empty table ready for the next run of the flow.

 

But for some reason after the flow runs once successfully, future runs always end up emailing with empty tables because of an API error.  I don't know how to solve this.  I think its because I am overwriting the target file with an empty copy, even though the excel files are identical.

 

David2831_0-1656187053161.png

 

I just need some way to populate the table, email it out, and reset for the next flow run.  I thought of saving my data to a CSV and using PQ to import that into the excel file, but it seems that to refresh the PQ connection, I would have to use a desktop flow, we don't have those licenses.

 

Any ideas?

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @David2831 

 

Since the table name is dynamic Power Automate will ask for Row value to update. It won't display the column names dynamically. Here is another video I made. Hopefully this will resolve the issue.

 

https://www.youtube.com/watch?v=pKKk-sgyDYE&t=36s

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

View solution in original post

10 REPLIES 10
abm
Super User
Super User

Hi @David2831 

 

Believe its a new excel empty file for the next run you have a new table Id, yes its the same table name but Id is different hence you getting this error. Try to create the table dynamically. Have a look at my video tutorial.

 

https://www.youtube.com/watch?v=Q4Q_OWEa-Jw&t=133s

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

I am trying your solution in my flow to see if it will work, but its changing my available options in a way I don't know how to answer.

 

David2831_0-1656206529567.png

 

 

Before the "Add a row" action would detect the available columns and ask me to map values into each.  With this setup I instead get a single required "Row" field, and no idea how to fill it.  Can you give me an example to work from?  Google isn't helping.

 

Also, I used the dynamic content "Name" from the table name value in create table.  I assume that's the key to making this work.

Hi @David2831 

 

Since the table name is dynamic Power Automate will ask for Row value to update. It won't display the column names dynamically. Here is another video I made. Hopefully this will resolve the issue.

 

https://www.youtube.com/watch?v=pKKk-sgyDYE&t=36s

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
David2831
Helper II
Helper II

Double post mistake, sorry.

 

Thank you very much for your continued help with this.  I have followed your advice and watch the videos you linked.  I have everything working the way you suggested, but unfortunately the "Create Table" action seems to have a similar issue to the "Add a Row" action with excel.  The flow will work once after I reset everything, but a second run will fail at the create table action with a 404 resource not found error.

 

By 'reset everything', I mean that I first place a fresh copy of my file in the target folder.  Then I go into the flow and on each of the 2 excel actions I click the folder icon at the end of the 'File' setting and point it to the excel file again.  This seems to reset the actions so that they will find the new file.  After this the flow will run smoothly and I am emailed the result.  I have reviewed the email attachment and the file arrives with all the correct data in the proper places.

 

But I then wait about 5 minutes and run the flow again, and it loops on my error check at the "Create Table" action until I cancel it.

 

Can you offer any additional advice?

 

 

David2831_0-1656496923781.png

 

 

Hi @David2831 

 

What's your trigger? Can't you use when a new file is created as trigger?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Its manual trigger, this report is run on demand.  The excel file is always just sitting there, waiting for the report to be run.

 

David2831_0-1656505555357.png

 

To be more clear, here are the major steps of my flow =

 

1. A user that has been given access to the flow kicks it off, and gives start and end dates.

2.  The flow pulls records from dataverse and does some logic to get the values we need, and arranges the data into an array variable.

3. The table in the excel file is created, and the array is written to the table in excel.

4. The excel file (now full of data) is copied to an archive location and renamed to include the date it was run.

5. An empty copy of the excel file is copied over the original one used in step 3 above, this empties/resets it for the next run of this flow.

6. A copy of the filled excel file in the archive is emailed to the user that originally started the flow.

 

End of flow.

 

Hopefully this gives you an idea of what I am doing.  My problem is that step 3 fails on the second run of this flow.

For anyone in the future that has this issue, It has now been resolved.  The fix was to use the create table action as ABM recommended, and also to replace the File option with a compose dynamic output, instead of using the file icon to select the exact file.

 

David2831_0-1656526855490.png

 

The compose line is simply the path and filename of my excel file, copy / pasted from the file line above.  This seems to create enough separation for the add row action that it doesn't hard code the excel file ID into the code, instead dynamically finding it each time the flow is run.

 

My flow works correctly now every time, thank you ABM!

Hi @David2831 

 

Thanks for the screenshare. So what we did is change the flow to copy the existing Excel file before it start inserting the records. This means the we always have a new file to start with. Again for the file and table name use the dynamic mappings. Glad to see that all working as expected.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

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

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (1,580)