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!

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!

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
firstImage

Microsoft Flow Online Conference

Join us for a FULL day of FREE Microsoft Flow Sessions from some of the best minds in the industry!

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

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

thirdImage

New Flow Community Board!

Check out the new Microsoft Flow Community Blog Topic Suggestion board!

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 Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 161 members 5,097 guests
Please welcome our newest community members: