Copy Excel Data with formula and paste only values

(0) Share

Report

Posted on by amsrivas

177

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

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

Yutao Huang Microsoft Employee on at

Like (0)

Report
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:
Office Scripts in Excel on the web (overview)
Announcing support for Office Scripts in the Excel Online (Business) connector
Run Office Scripts with Power Automate
Call scripts from a manual Power Automate flow
Pass data to scripts in an automatically-run Power Automate flow
Hope this helps!
Yutao

Was this reply helpful? Yes No
amsrivas 177 on at

Like (0)

Report

Hi @Yutao , Thanks for the reply.
But unfortunately, I do not have privilege to write any script on source file.

Any suggestions!!

Thanks
Amit

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (0)

Report

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.

Yutao

Was this reply helpful? Yes No
amsrivas 177 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (0)

Report

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

Was this reply helpful? Yes No
KK6387 2 on at

Like (0)

Report

HI Yutao,
Good Day!
I have tried the same but when calling the office script from Power Automate the new sheet is populating with #Name? instead of actual values. But the same script is working fine when I execute it from Excel. ExcelScript.RangeCopyType.values is not working as expected when triggering from Power Automate. Could you please help me with this issue?

Regards,
Karthik

Was this reply helpful? Yes No