cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amsrivas
Resolver I
Resolver I

Copy Excel Data with formula and paste only values

Hi,

 

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!!

 

Thanks

Amit 

5 REPLIES 5
Yutao
Microsoft
Microsoft

Hi @amsrivas ,

 

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

Yutao_0-1602015847816.png

 

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!

Yutao

Hi @Yutao , Thanks for the reply.

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

 

Any suggestions!!

 

Thanks

Amit

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.

Yutao_0-1602022053265.png

 

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

Yutao_1-1602022075459.png

 

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.

 

Yutao

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?

 

Thanks

Amit

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.

 

Yutao

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Users online (109,728)