Move data between workbooks automatically with Office Scripts & Power Automate | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Check out the blog post★

★ About this video ★
Normally in Excel online, data cannot be passed from one workbook to another. In fact, Excel online doesn't even know that other workbooks exist! However, using Power Automate and Office Scripts, we can create flows that can transfer data between workbooks automatically. And... we can make it happen automatically without needing to open workbooks, or copy and paste #AMAZING!!!!

0:00 Introduction
0:25 Example
1:18 Get data from Excel
3:33 Paste data to Excel
5:02 PA flow to move data
8:03 Running the flow
9:08 Conclusion

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[blatz]

This is AMAZING! I have one question - I need to copy a large data set with ~150 columns and 1500 rows, and with the 2nd script I get Gateway Timeout issues in Power Automate. Sometimes it will run through and the data will paste correctly, but more often it fails. Is there a way to optimize the script in any way to reduce the timeout issue, or does the sheer number of data cells to paste cause issues regardless? Thanks!

[angieyang]

Dear Mark. Your video is a life saver. Instead of appending to a sheet with existing data though, I would like to simply paste the data into a fresh sheet. Would you kindly share the office script code that can achieve this? Thank you

[sermetshabani]

Hi Mark and everyone.
Great video and nicely explained.

Almost all videos I watch state that I can copy the code from the description below.
Can anyone please show me how I can get to the description where the code is stored?!
I looked for it but cant seem to find it.

Thank you all

[CP-zbky]

This is awesome! I haven't found any others addressing the copying and pasting between two workbooks using Office Scripts. The coding is above my head though as I don't have any programming background. I am trying to copy and paste "value" certain columns (source files have formulas only) into another workbook but will need to also convert destination file column A text to value. Not sure how best to go about it. I probably have to watch this video a couple dozen times to see if I can figure out...

[jessep]

Thank you for sharing your knowledge good sir!

[LtSerch]

Wow! I was looking for something like this because I need to move some info between excel shard in one drove and dint know the way to do this... Thabks for this Flow and script idea :)

[frankbarendsen]

Thanks for this script and tutorial really great. What if i want only the surrounding Region depended on a specific value ("Yes") in column "X"?

[almccmcc]

Thanks for this very clear explanation. I have several client files that need to be updated with data from a master file. Power Query works fine in desktop Excel, but it refuses to update in Excel Online because the tables are loaded into the workbook data model. This approach is a lot slower than Power Query, but it lets my users work in Excel Online as they prefer.

[kendrayoder]

Thank you for this video and Script demo! I have a scenario where, instead of appending to an existing table, I just want to copy the worksheet data into a new worksheet in a different Excel file. When I attempted this using the 2nd script, it caused an error. Does anyone know how the script could be tweaked to accomplish this?

[noodlebrains]

Great video. How would I use this to extract data from only specific columns. Also data from each column is populated in a different tab on the output workbook. This action is then repeated. But it’s important the data 100% matches the heading where its from and the heading where it goes and the tab it goes.

[camstephens]

I am also getting the Line 11 Can not get range errors. Is this in reference to the misnamed Work Sheets or am I missing something small. : )

[dinudavis]

Thanks for the video. Really helpful. Quick query: Is there a way to copy data between SharePoint excels even computer is switched off? I assume office Script will not work when computer is off.

[victoriaiwinski]

Hi Mark!

Thanks for the video. Question. I want to add data from the same excel file over and over again as it populates new data or any data is modified. How can I manipulate the flow to make sure that it's not copying and pasting ALL of the data each time the file is updated?

[chiragdabhi]

After getting file in folder we do work flow to open power query excel and in that file we do setting auto refersh when open file.

Thats way we can getting desire output when ever file update in folder.

This is possible???

[ANASTASIO]

Hello, thanks for this great video. The first script has worked perfectly and data is copied. When the PA runs the second script there is the following error "We were unable to run the script. Please try again. Runtime error: Line 11: Cannot read properties of undefined (reading 'getRange')". However, I can see the strArr has the copied data. Any help on why I have this and how to solve it? Thanks

[robertwilliamson]

Can I assume once I get over the learning curve, there is functionality in power automate to pick up a file attachment from outlook without having to manually copy the file out of outlook and paste and attach or overwrite what is in a specific table in an excel file.

[marianapoli]

Hi, Mark! Thank you for the video! I'm new to PAD. Is there a way to do the same thing, but using Power Automate Desktop? I cannot find materials about this. Would I do pratically the same thing but using VBA?

[sofiaibarra]

Hi Mark, I have a question. How can I copy a range of columns instead of all the columns. I need to copy the first 4 columns then paste it and then copy the following 2 and paste it. (This is beacause I have a different order of columns beteween the sheets ). Thanks for the video btw :)

[BasilMustafa-nj]

Hi Mark, in my case my workbook has multiple sheets and I want to copy data from only one sheet specifically, how can I do that ?

[misticoj]

Great video 😊 thanks a lot. I have a small question. I tried the method and it work perfectly when running the 1st script (get data) when using “start cell” as A1, “headers rows to remove” = 1 and “footers rows to remove” = 0. However, as soon as I tried something different like “headers row to remove” = 4, nothing is transferred from the first excel file to the second one. As soon as I revert back to “headers to remove” = 1, it works again. Should I also change something in the code if I want remove more than 1 headers ?