Copy Paste data between SharePoint Excel files using Power Automate in bulk instead of one by one

(2) Share

Report

Posted on by CU06081659-0

134

Hi Experts,

I am searching for a flow that'll Copy & Paste data between SharePoint Excel files using Power Automate in bulk instead of one by one. I want to avoid 'Apply to each' step which is not advisable for a bigger data.

Basically I have 2 files - Master file and Current day file. Everyday this Current day file gets generated and I want the flow to copy whatever data we have in the Current day file on to the Master file. I have seen a video in YouTube which is doing what I need but it is not sustainable as it started throwing errors after few days even though nothing changed i.e. both the files were same and no columns were added/deleted. It was using Office Scripts.

The issue is Power Query gives me flexibility but no Flow can run a Power Query internally without opening the file. The flow would say successful but the PQ never runs actually.

I am looking for a solution in Power Automate Web and not in Desktop.

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

Sort by

Suggested answer

Haque 3,653 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @CU06081659-0,

Can you please mention the data volume? Yes you are correct - Power Query gives you flexibility but no Flow can run a Power Query internally without opening the file, is a known limitation.

If you want to avoid "Apply to each", I see only the option have is office script, if Excel mandatory - though SP list/Datavserse could be your data-source somehow.

One of the options you could explore: Power Automate Desktop: Use desktop automation to open Excel, refresh Power Query, and save files if complex transformations are needed.

Was this reply helpful? Yes No
CU06081659-0 134 on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @Haque,

I cannot use Power Automate Desktop as the Flow needs to run even on weekends and I don't have a dedicated system to use it. The data volume is less than 3000 rows everyday.

Was this reply helpful? Yes No
Suggested answer

Haque 3,653 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @CU06081659-0,

I understand the PAD limitations - a dedicated resource oriented system is needed!

Then what I suggest is let's scrutiny your Office Script PA flow again. If possible let's go back to office script (that you have tried to implement seeing a video) and try to address if the following items below have been covered thoroughly or not.

The errors you encountered with Office Scripts in Power Automate copying data between SharePoint Excel files after a few days, despite no changes in the files, are known challenge considering the following factors:

An independent Excel session is needed to run office script in PA, which can cause intermittent failures or session conflicts.

Concurrent access by users may disrupt the flow operation.

With large dataset and complex operations - Office Scripts may face platform limitations.

Updates in Power Platform or Office Scripts environments can affect script reliability.

Lack of error handling or retry logic in the flow can cause persistent failures.

To improve reliability and sustainability of your flow, consider these best practices:

Use absolute references in your Office Scripts to avoid issues with relative references.

Ensure Excel files are closed and not edited during flow runs.

Add error handling and retry policies in your flow and scripts.

Simplify and optimise your Office Scripts for performance.

Monitor Microsoft documentation on Office Scripts and Power Automate limits.

For very large datasets, consider alternative data stores like SharePoint lists or Dataverse tables.

Finally,

To narrow down - can you please replicate the same operation with minimum data rows in both files? Let's say current tab has 5 rows, and master tab has 10 rows, if successful, master tab shows 15 rows, any error otherwise when fails?

I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!

Was this reply helpful? Yes No
Haque 3,653 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @CU06081659-0,

I was folloiwng up on what decision you took?

Was this reply helpful? Yes No
lbendlin 8,716 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

Learn about the Microsoft Graph API and specifically about the Batch API calls. They allow you to run operations in bulk. Requires some preparatory work, and appropriate Graph access, but it is fast and reliable when you have it set up correctly.

Was this reply helpful? Yes No
CU06081659-0 134 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Haque,

As of now I am referring the below 2 videos that I could find on YT.

https://www.youtube.com/watch?v=_14U7zjRw1o&t=1s

https://www.youtube.com/watch?v=Gpa0lI5cAt8&t=189s

The 1st video was working fine for few weeks and then it suddenly started throwing errors even though files and contents didn't change.

Hi lbendlin,

Can you guide me to any video which may help me in this scenario?

Was this reply helpful? Yes No