Move data between workbooks automatically with Office Scripts & Power Automate | Excel Off The Grid
HTML-код
- Опубликовано: 23 июл 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post★
exceloffthegrid.com/move-data...
★ 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 ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel
Amazing!!! You are a true genius.
Thanks - it took a bit of work to solve it. But now I know how, so happy to pass this on.
Hi Mark, great work!
Thanks 😀
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 :)
Glad it was helpful!
We’re all going through the same learning curves in this, so just trying to share what I learn.
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!
Thank you for sharing your knowledge good sir!
My pleasure! 😀
I got this working on my project but let's say I'm pasting the results into a blank tab, what changes do I make within the script? It's giving me an error about getSurroundingRegion() since obviously the tab it was pasting to was blank.
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?
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"?
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
Hi Mark, Is it possible to get a used range (basically count) of any particular column from a sheet in the Excel workbook using office scripts? Though we have a getusedrange method it always gives us a used range of the entire sheet, but I need it only for a particular column.
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.
Hopefully, they will add PQ to Excel Online soon. As that would be a better solution.
Does Power Query work with Power Automate though?
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.
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 ?
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. : )
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...
I suggest you break it down
(1) copy all the data between workbooks
(2) a separate script to remove the columns you don’t need (you can probably use the scripts recorder for that)
(3) a separate script to convert a column A from values to text
Then in Power Automate you run the scripts separately. Once you’re more comfortable with Office Scripts you can consider combining (2) and (3) into a single script.
@@ExcelOffTheGrid Thank you for the suggestion! I had a feeling that I might need 3 scripts, first script to copy cells A3 (first two rows are headings) to I1000 source file, second script to paste to A3 to I1000 destination file, third script to convert from text to value A3 to A1000 destination file (column A has all the general ledger account numbers). Most of our companies have between a few rows to about 700 rows data so I figure cutting off at row 1000 should be sufficient. But I have like 20 companies/BUs all in the same source file, going into 20 separate destination files. So I'll have to give this some thought, may need to set up 20 PA flows😅I just noticed you're an accountant too, not sure how you got so good at this 👍Envy...
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?
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
Assuming your target Excel file has a table of data--formatted as a table, and with the exact same fields as the source data table--you can simply have the script clear existing data, then add in the strArr data to the blank table. Here's a script you can use that should work with the video example:
function main(workbook: ExcelScript.Workbook, strArr: string) {
// Get the table
let tbl = workbook.getTable("YOUR TABLE NAME");
//Delete data in the table
if(tbl.getRowCount() >=1 ) {
tbl.getRangeBetweenHeaderAndTotal().delete(ExcelScript.DeleteShiftDirection.up);
}
//Convery strAr to Array
let newArr: string[][] = JSON.parse(strArr);
//Add rows into table
tbl.addRows(null,newArr)
tbl.getRangeBetweenHeaderAndTotal().getFormat().getFill().clear();
}
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 :)
Hi Mark,
Do you know of a way to extract the data from a data model inside an Excel file into another Excel file (using a Power Query connector, preferably)?
Hi Geert
Think we do something like this all the time: we have data report files (CSV or XLSX) from corporate systems that we save to a DATA folder on SharePoint/Teams and have an Excel file containing PowerQuery process that references the source data file(s) to process the data in standard ways (joining other data, cleaning data, removing columns/rows we don't need). Reporting of the output can then either be reported natively in the Excel or picked up by Power BI for visualisation. We have tended to move heavy data processing without visualisation to Excel PowerQuery and point Power BI at the resultant output for efficiency. (Our organisation doesn't allow easy implementation of SQL, otherwise our approach would differ)
Hope that helps
Chris
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
You may have mis-names the worksheet in the workbook.
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.
Yes, you’ve got it. You can automate the saving of the attachment with Power Automate. If there are multiple attachments in one email, it can get a little tricky to work out which one to save automatically.
Hi Mark
Thank you for this. I've seen the Automate tab on the ribbon demonstrated by Leila Gharani in one of her recent videos. But it seems that it is only available under commercial licences? I have Office 365 and am on the beta channel. But I don’t have one of the commercial 365 subscriptions. So it seems that I can't access Office Scripts?
It is only specific licences which have Office Scripts - they are listed here:
learn.microsoft.com/en-us/office/dev/scripts/overview/excel#requirements
Hopefully they will come to all licences soon. But I have no idea if that is in Microsoft’s plan.
@@ExcelOffTheGrid Thanks, Mark. I shall sit and wait!
Hi Mark, thank you for the workflow. I have a sheet with 10 Macros to be run sequentially but each Macro gives a dialog box “Ok”. Doing this manually eats 3 minutes or so and is unproductive. My Excel is locked so I can’t get away by editing the Macro in VBA. Is it possible to tell Automate to run those 10 Macros sequentially and also to take care of those 10 “Ok” messages automatically without me clicking those one by one? Would be grateful if you can give some pointers.
There is a app in Microsoft store that lets you click mouse buttons in set intervals, have you tried that?
@@Trancer006 I have never heard of such an app. Let me know the name of app. Thank you so much 🙏
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.
Power Automate can run Office Scripts when your computer is turned off. It all happens in the cloud.
It’s just a matter of picking the right trigger inside Power Automate.
@@ExcelOffTheGrid Thank You for your response. Your answer solves many of my problems. Thanks again. Have a good day :)
Thanks for this video, I need copy and replace the data in existing workbook. Is there a code for that?
The code could certainly be amended to do that.
Try using the action recorder to find out the code.
I have a question, how can I limit the range to 1k?
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?
You could do it with PAD. But you would need to write a Macro and use the Run Macro action inside PAD.
So, Yes. It's very achievable.
Hi, such a great video. I'm sadly facing an error when I run both scripts - Office JS error: Line 22: Range setValues: The number of rows or columns in the input array doesn't match the size or dimensions of the range.
Could you spare an idea why this could be happening?
Would you be willing to share the file with me, so I could take a look?
@@ExcelOffTheGrid thank you for the fast reply! I found the issue - the number of columns in my data sheet was different than the number of columns in the data I wanted to paste :) once they are the same everything works
I followed all steps but cannot find "wsRngArr" Dynamic Content once create script_2 action. Only "item" dynamic content from first script run action. Any idea to fix? I create flow on Web.
Great Work. But it not works 100,000 rows. Only work like 10,000 rows. Please help
Is it not possible to do this with just power automate instead of the scripts?
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 ?
The sheet name is one of the defined parameter passed from Power Automate. So it will only copy from one worksheet.
thank you, but return wsRngArr couldn't be set as a parameter for next script in my work flow.
I have the same issue, besides the output of get-data want's to open in a browser
Can you paste append in a new workbook file? or does it need to be already created?
You could paste append in a new workbook file but whats the point? Might as well just automate move/copy sheet? Shouldn't need Office Scripts for that step. You would benefit from a script that creates the workbook for you possibly.
A guide on how to convert a email csv attachment to Excel would be great.
Try this… exceloffthegrid.com/csv-to-excel-online/
This work is also done by Power Query. On pasting the new excel workbook in the folder, running append query gives the same result. Then why use power automate. Please explain.
The use case is very different.
Power Query does not move data, it aggregates it from many different sources. PQ is great for data analysis but rubbish for process management as it has no write-back functionality.
Also, unless you're using Power BI / Data Flows (which most Excel users are not) with PQ you need to to refresh the query manually. However Power Automate operates in the background automatically. And moving data is just one step of a larger flow.
This isn't an alternative to PQ's append, it exists in a different part of the workflow.
Thank you
Reports are updated only after refreshing in Power Query which takes time, you have explained well. But learning to power automate is a bit difficult. As soon we will learn well from your youtube video.
@@ExcelOffTheGrid Thanks! You just answered my question on Power Query. I definitely need Power Automate so the copying and pasting between workbooks can be done in the background automatically without me having to open the files.
Hi... Can this handle large data? like 100,000 rows?
I wouldn’t have thought it would go that big. Power Automate has limits for amount of data and also the time to execute.
But I’ve not experimented to find out.
@@ExcelOffTheGrid Can you please add a code in your get data to where you can limit to how many cell it will get? You have STARTCELL, can you put also ENDCELL?
When pasting to a workbook with formulas depending on the pasted data it times out half of the time on line 22 set values. Any work around for this you think? Racking my brain over this. Turning off auto calculation does not fix the problem.
Actually, I don’t have a solution for that. How many rows of data is it?
As its pasted as an array, I wouldn’t have expected manual calc to make any difference, but I would it expected it to work.
@@ExcelOffTheGridit's super strange as it only occurs when there are formulas referencing the range, but the formulas do not update as I've adjusted the data calculation method. Is there a way to append to the bottom of the already existing table? That way it's not joining the data together and pasting the entire worksheet? It's 16k rows but only adding 2-3k max each time it runs this script. I've tried splitting it up into a max of 1k rows added, I've tried clearing out most of the rows... It works and then doesn't. I've also added a step in the flow to 'do until' so it will run until it succeeds but then it just runs indefinitely. Not sure what is wrong but this code is not optimized it seems, problem is I'm not fluent enough in office scripts object orientations to fix it.
@@dtttd did you figure this out? I'm also getting an error on line 22 of the 'paste' script --- 10APR24
--- I tried this flow but I get an error message at the last line of code for the 'paste' script: "Office JS error: Line 22: Range setValues: The argument is invalid or missing or has an incorrect format." Does anyone else getting the same error? --- 12APR24
Hi, I am getting the same error, did you fix it? Could you please help me?
@@juanpabloalonso3122 yes, you have to update the JSON ‘paste’ script from ‘setValues’ to ‘setValue’ and now it works