Copy data between Excel Workbooks using Power Automate and Office Scripts

(1) Share

Report

Posted on by CU22031144-0

Hello everyone,

I’m new in Power Automate and Office Scripts, but I’m very familiar with Excel.

I use Office 365 and I’d like to automate a task in SharePoint using Power Automate and Office Scripts.

In a SharePoint folder called ‘FolderName’, I have two Excel files which I’ll call "A.xlsx" and "B.xlsx"

In "A.xlsx", I have several tabs, only one of which I’m interested in, I’ll call it "FromContents".

In the "B.xlsx" file, I have a tab which I’ll call “ToDestination”.

The aim is to copy and paste the contents of “FromContents” ("A.xlsx") into “ToDestination” ("B.xlsx").

From a few YouTube videos and web articles, I understand that Power Automate only works with tables (Ctrl + T).

However, in my “FromContents” sheet, I have a range of several dozen rows and about twenty columns. Within this range, there are blank rows to separate the accounting entries.

Could Power Automate work with such a structure ?

If so, could you please provide me with some useful links on how to achieve this ?

Thank you in advance.

NB: Please excuse me if my explanations are unclear. I am not a native English speaker and have used Google Translate.

Best regards

Categories:

General topics

I have the same question (0)

All responses (16)

Answers (1)

Sort by

Suggested answer

frago 226 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi,

Power Automate + Office Scripts works most reliably when your source data in A.xlsx is in an Excel Table. In this approach it’s best to create the table manually once (Ctrl+T), because it’s a one-time setup and keeps the script stable (fixed table name, consistent range).

Script 1 (A.xlsx) — “Copy Data”

function main(workbook: ExcelScript.Workbook): string[][] { let table = workbook.getTable("Table1"); return table.getRange().getTexts(); }

Script 2 (B.xlsx) — “Paste Data”

function main(workbook: ExcelScript.Workbook, dataToPaste: string[][]) { let sheet = workbook.getWorksheet("ToDestination"); let targetRange = sheet.getRangeByIndexes(0, 0, dataToPaste.length, dataToPaste[0].length); targetRange.setValues(dataToPaste); }

Power Automate flow uses two actions only:

Excel Online (Business) → Run script on A.xlsx to read data (returns string[][])
Excel Online (Business) → Run script on B.xlsx to paste data (takes string[][] as input)

------------------------------------------------------------

If this solved your issue, please mark it as ✅ Accepted Answer.
If it helped, feel free to give it a 🩷 Like!

Best regards,
Piotr

Was this reply helpful? Yes No
rzaneti 4,476 Super User 2026 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @CU22031144-0,

The answer provided by @frago is totally correct.

If you need more clarity on the process of sending data from Power Automate to Office Scripts, which will be necessary for Script 2, you can refer to this blog: https://digitalmill.net/2024/01/17/sending-values-from-power-automate-to-excel-with-office-scripts/

By the way, I recently started a website to share Office Script templates created by the community: https://www.officescriptsgallery.com/. If any of you have interesting scripts that you would like to share, please get in touch :)

Let me know if it works for you or if you need any additional help!

✅ If this solved your issue, please mark it as Accepted Answer.
👍 If it helped, feel free to give it a like!

🌐 Explore more Power Platform content on my Website or on my ▶️ YouTube
💼 Find me on LinkedIn

Was this reply helpful? Yes No
CU22031144-0 51 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello everyone, @frago, @rzaneti,

Thank you very much for your replies, which I have read carefully.

@frago, I have copied the two scripts individually into each of my Excel workbooks in SharePoint 365.

So far, I don’t think there’s a problem.

@rzaneti, thank you very much for the links, I have started looking through them. I look forward to watching the YouTube videos.

However, I’m stuck at the point where I want to run the two scripts manually in Power Automate.

I created a flow starting with the screenshots in the attached PDF.

Where I’m stuck is at the last screenshot, where I need to select the location

If I click on the drop-down list, I’ve tested about ten folders one by one, then ‘Document Library’ and ‘File’, but I haven’t managed to get to my SharePoint folder.

Do I need to go to my SharePoint folder, or to ‘OneDrive’/‘OneDrive for Business’ to retrieve the scripts?

If I understand correctly, I need to start with the trigger, add an action to retrieve the first script, then add a third step to add the second script.

But I’m already stuck at the second step.

Am I doing it wrong? Is there an easier way to do this?

Thanks in advance

Best regards

Power Automate Ru...

Was this reply helpful? Yes No
Verified answer

frago 226 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @CU22031144-0

Just to confirm one thing first:

have you already added the scripts directly in Excel Online (Office Scripts) and created them as separate scripts inside the Excel workbooks stored on SharePoint?

If yes, then you are on the right track, and using Run a script in Power Automate is exactly the correct approach.

What usually causes confusion at this point is the Location dropdown. Not all SharePoint sites appear there, especially Teams-connected SharePoint sites. This is a known limitation and does not mean anything is wrong with your setup.

In that case, instead of selecting from the list, you can simply choose “Enter custom value” and paste the SharePoint site URL manually. Power Automate will still find the Excel file and the scripts correctly.

So overall: your approach is correct, Run a script is the right action to use, and this is just a UI limitation of the dropdown rather than an issue with your flow.

------------------------------------------------------------

If this solved your issue, please mark it as ✅ Accepted Answer.
If it helped, feel free to give it a 🩷 Like!

Best regards,
Piotr

Was this reply helpful? Yes No
CU22031144-0 51 on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you very much @frago

I created the scripts directly in the Excel file.

They appear straight away in the Automate tab, just like in your first screenshot.

I think I’ve got it now, so that’s reassuring. But, If I'm not mistaken, there are no parameters to add inside the trigger.

Otherwise, I think I also tried copying and pasting the path directly into Location.

I’ve switched off my work computer. I’ll give it a go tomorrow evening after work.

I’ll get back to you then.

Have a good evening

Was this reply helpful? Yes No
CU22031144-0 51 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello everyone, @frago, @rzaneti

Here are the steps I’ve taken today :

Step 1: Creation of a manual trigger with no parameters

Step 2: Creation of a first ‘Run script’ action

In step 2 with the following parameters:

Location = I copied the path of the A.xlsx file and shortened it to the root. This worked (thanks to Frago for suggesting this solution)

Document Library = Folder name

File = file A.xlsx

Script = Name of the script I had named ‘FromSource’ in the first script provided by Frago

Step 3: Create a second ‘Run script 1’ action

With the following parameters:

Location = Same as Step 2

Document Library = Same as Step 2

File = file B.xlsx

Script = Name of the script I had named ‘ToDestination’ in the second script provided by Frago

ScriptParameters/dataToPaste = This is where I’m stuck. I don’t know what to enter (see screenshot)

I have a few more questions :

1- In the scripts provided by Frago, what do the following correspond to:

let table = workbook.getTable(‘Table1’) = The name of the worksheet (tab) or the name of a structured table?

let sheet = workbook.getWorksheet(‘ToDestination’) = Similarly, the name of the worksheet?

2- Would both scripts work equally well with tables and ranges?

Thanks in advance for your support

Best regards

Was this reply helpful? Yes No
frago 226 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @CU22031144-0

Hi! It’s great to see you’ve successfully bypassed the SharePoint location issue. You are at the final stage now.

1. Solving the dataToPaste Problem
In Power Automate, when a script returns data (like your first script does with return table.getRange().getTexts();), the flow captures that data as the "result" of that action.
How to set it up:
Click inside the dataToPaste field in your second "Run script" action.
A "Dynamic content" window should appear.
Look for the section related to your first "Run script" action (the one for File A).
Select the item named result.
Power Automate will then automatically pass the array of data collected from File A into the script for File B.
2. Answers to your specific questions:
Question 1: What do the names in the code correspond to?
workbook.getTable("Table1"): This refers to a Structured Excel Table Name, not the worksheet (tab) name.
To find this, click anywhere inside your data in Excel, go to the Table Design tab in the top ribbon, and look at the Table Name field on the far left.
workbook.getWorksheet("ToDestination"): This refers to the Worksheet (Tab) Name. It is the name you see on the tab at the bottom of the Excel window.
Question 2: Would both scripts work equally well with tables and ranges?
Script 1 (Source): This script currently requires a Table. The command getTable("Table1") specifically looks for a table object.
Why use a table? It is much more reliable. If you add or remove rows, the table grows or shrinks automatically, and the script will always grab the exact amount of data without you having to define coordinates manually.
Script 2 (Destination): This script pastes data into a Worksheet starting at a specific coordinate (0, 0, which is cell A1). It doesn't strictly need a table object in the destination file; it will simply overwrite the cells in that sheet.
Final Tip:
You are definitely on the right track and doing a great job setting this up! Don't forget that Copilot (inside Excel or Power Automate) can definitely help you understand these scripts even better or help you modify them if your table names change.

------------------------------------------------------------

If this solved your issue, please mark it as ✅ Accepted Answer.
If it helped, feel free to give it a 🩷 Like!

Best regards,
Piotr

Was this reply helpful? Yes No
CU22031144-0 51 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you, frago for your clear and precise reply.

I’ll be able to test this tomorrow on my work computer.

I’ll let you know how it goes tomorrow evening.

Have a good evening.

Edit : I should have guessed the answer to those specific questions 🙁

Objects Table and Worksheet are very common in VBA and Power Query

Was this reply helpful? Yes No
CU22031144-0 51 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello everyone, @frago, @rzaneti

I’ve added a parameter in the final step, in the area shown in the screenshot below.

However, I’m getting an error message in the Power Automate banner :

“......with type/format “String” is not convertible to type/format “Array””.

I’ve no idea how to resolve this issue

Thanks in advance.

Kind regards

Was this reply helpful? Yes No
frago 226 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi

Step-by-step fix

In Power Automate, open the action where you run Script 2 (Paste Data):
Excel Online (Business) → Run script

Find the input parameter dataToPaste.

Do not paste the dynamic content in a way that becomes text (for example with extra quotes or line breaks like "\n @{...}\n").
That turns it into a String.

Click into the dataToPaste field and switch to the Expression tab.

Paste this expression (replace Run_script with the real name of your Script 1 action if different):

json(outputs('Run_script')?['body/result'])

------------------------------------------------------------

If this solved your issue, please mark it as ✅ Accepted Answer.
If it helped, feel free to give it a 🩷 Like!

Best regards,
Piotr

Was this reply helpful? Yes No