Showing results for 
Search instead for 
Did you mean: 
Resolver I
Resolver I

Copy Excel Data with formula and paste only values



I need to copy data from table of excel which having excel formula but paste only values in same excel in same rows and columns.


Please suggest!!





Hi @amsrivas ,


Wondering if you would like to try out the new "Run script" action (included in the Excel Online (Business) connector):



You'll first need to create your script (TypeScript) on Excel Online. Here is a sample script that copies values only from a table called "Table1" (which can contain formulas) to a range whose top-left cell is F11 on the worksheet called "Weekly Report":



function main(workbook: ExcelScript.Workbook) {
    let sourceRange = workbook.getTable("Table1").getRangeBetweenHeaderAndTotal();
    let targetRange = workbook.getWorksheet("Weekly Report").getRange("F11");
    targetRange.copyFrom(sourceRange, ExcelScript.RangeCopyType.values);




Here are some links that might be helpful to learn more about the new Run script action and the Office Scripts in general:

Hope this helps!


Hi @Yutao , Thanks for the reply.

But unfortunately, I do not have privilege to write any script on source file.


Any suggestions!!




Hey @amsrivas ,


You need to create/edit/record the script from inside Excel Online when a workbook is opened, but the script itself won't be saved into that workbook. Instead, it will be saved to your own OneDrive for Business.


So if the Office Scripts feature is turned on in your organization/tenant (hopefully), you should be able to see the "Automate" tab. You can click on "Code Editor" to open up the script Code Editor pane.



Then type in the script into the Code Editor pane. Save it with a meaningful name.



After that, from the Run script action in Power Automate, you should be able to pick that script from the Script dropdown list.


But if you're not seeing the "Automate" tab in Excel Online, that means your admin hasn't turned on Office Scripts for your organization yet. In that case, I guess you'll probably need to do some data wrangling with the other existing Excel Online (Business) actions like "List rows present in a table", "Update a row", "Add a row into a table", etc.



Hi @Yutao 


I will receive source file daily so i couldn't be able to write script everyday to the new file.

Will it be done problematically using flows?




Hello @amsrivas ,


You don't need to write script for every single workbook. You just need to write the script once and you can run it against any workbook you have access to.


How do you pass the workbook you receive daily to the flow? Something like an email trigger or form submission trigger? You will need to properly configure your flow and the "Run script" action so it can work against dynamic workbooks.


A script can accept parameters so it's also possible to pass in things like sheet name, table name, column index, conditions, etc. from the Run script action to direct the script do different things if you want to reuse a single script.



Helpful resources

MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (2,563)