cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdamD
Level: Powered On

Copy Excel table with forumulas, paste only values into table on new sheet using Flow.

Hello,

I'm not sure if this is necessarily possible, but I have a request to build a workflow that takes a table with values calculated by formulas, and applies only the values to a new table that can be edited manually by users. While I know how to copy the exact content from one sheet to another, I'm not sure Microsoft Flow can intelligently paste-special just the calculated values. Is there a clever way to collect only the calculated cell values using Flow?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Copy Excel table with forumulas, paste only values into table on new sheet using Flow.

This is doable with the Excel Online (Business) connector, you'd just use Value for the data to copy over. However, I'd suggest that you reconisider using and/or limit where you use Excel within your workflow, mainly because it's severe known issues and limitations including strict file lockout timers of 6-12 minutes

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Copy Excel table with forumulas, paste only values into table on new sheet using Flow.

This is doable with the Excel Online (Business) connector, you'd just use Value for the data to copy over. However, I'd suggest that you reconisider using and/or limit where you use Excel within your workflow, mainly because it's severe known issues and limitations including strict file lockout timers of 6-12 minutes

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

v-lin-msft
Level 10

Re: Copy Excel table with forumulas, paste only values into table on new sheet using Flow.

Hello @AdamD,

 

I'm not sure if you meant 'calculated cell' to be a whole column or a single cell.

Is this cell part of the table and is the cell outside the table?

Could you tell me what's the formulas you use, please?

You can tell me these answers, which can help us better solve the problem, thank you.

 

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AdamD
Level: Powered On

Re: Copy Excel table with forumulas, paste only values into table on new sheet using Flow.


@Brad_Groux wrote:

This is doable with the Excel Online (Business) connector, you'd just use Value for the data to copy over. However, I'd suggest that you reconisider using and/or limit where you use Excel within your workflow, mainly because it's severe known issues and limitations including strict file lockout timers of 6-12 minutes

 


Yikes, I wasn't aware of these limitations. In a separate workflow I try to delete rows that I've copied from one table to another. I'm finding that some of the rows get deleted like they should, but others seem to fail randomly in the ApplyToEach step. I'm guessing this is most likely because I'm asking it to delete roughly 300 rows and it tries to execute this faster than the 100 actions per minute limitation?

The extended lockouts wouldn't be too much of a problem, but the above is definitely a dealbreaker. Would the overall recommendation be to abandon manipulating this data in Excel spreadsheets and instead do all the data manipulation in a SharePoint list first and export to excel as needed? Besides Excel and Sharepoint I'm not sure I have another alternative (besides the effort of trying to migrate to some kind of SQL solution). Thanks for the reply it was very helpful!

AdamD
Level: Powered On

Re: Copy Excel table with forumulas, paste only values into table on new sheet using Flow.


@v-lin-msft wrote:

Hello @AdamD,

 

I'm not sure if you meant 'calculated cell' to be a whole column or a single cell.

Is this cell part of the table and is the cell outside the table?

Could you tell me what's the formulas you use, please?

You can tell me these answers, which can help us better solve the problem, thank you.

 

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


Basically the whole sheet that I'm trying to copy is populated by calculated cells (meaning their contents are formulas) in every column, those formulas are based off of values in another sheet in the same Excel file. I want to copy just the values in this particular sheet of formulas (so, not the formulas but the represented values on screen) into a completely separate sheet in another excel file.

Yes, the sheet is formatted as a table with header columns, so I could just pull the table data, but I'm find that the list rows function and ApplyToEach flow have some inconsistent results in MS Flow.

v-lin-msft
Level 10

Re: Copy Excel table with forumulas, paste only values into table on new sheet using Flow.

Hello @AdamD ,

 

I have made a test on my side and collect only the calculated cell values using Flow. Please take a try with the following workaround:

Annotation 2019-05-09 165904.png

This is the table of data source:

Annotation 2019-05-09 170303.png

 

And This picture is the result of the run:

Annotation 2019-05-09 170932.png

It's possible to configure your flow like pictures to do what you want, and you can use the value to append to the other table.Annotation 2019-05-13 .png

Update the row, for instance Height.

These functions are

"body('Filter_array')[0].Name"

"body('Filter_array')[0].Name"

"body('Filter_array')[0].Age"

"body('Filter_array')[0].Sex"

"body('Filter_array')[0].Height"

Please have a try.

 

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,422)