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!
@advlife78198 ай бұрын
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.
@felipemoraleja52809 күн бұрын
its called "item" under Run Script (the name of the first ran script). It somehow put "wsRngArr" as "item"
@angieyang8794 Жыл бұрын
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
@Lance1988R5 ай бұрын
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(); }
@kendrayoder717111 ай бұрын
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?
@MartinPrinz-n5v Жыл бұрын
Hi Mark, great work!
@ExcelOffTheGrid Жыл бұрын
Thanks 😀
@camstephens5534 Жыл бұрын
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. : )
@sermetshabani9175 Жыл бұрын
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-zb3ky Жыл бұрын
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...
@ExcelOffTheGrid Жыл бұрын
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.
@CP-zb3ky Жыл бұрын
@@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...
@Abhishja25 күн бұрын
Great training.. I have put the same script for paste/Appened in Run Script with azure logic app, but i am getting an error mentionging "Unexpected token A in JSON at position 0". its working fine using power automate flow. where i am wrong using logic app?
@Abhishja25 күн бұрын
I got the issue fixed... its simple.. we have added @string(body('Run_script')?['result']?['wsRngArr']) in run script Logic app. Thanks
@FernandoMontes-bt1pqАй бұрын
Hi, please can you help me with an issue? I used same parameters than you and I have this error after the execution of first script: Line 5: Unexpected token u in JSON at position 0 clientRequestId: a20aaf3a-6926-4e55-9ee1-b7a8ed9e851d. Or, can you help me to know whats the content of the parameter that you use at 7:54 minute of the video? (e.g. outputs(......))
@dinudavis8609 Жыл бұрын
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.
@ExcelOffTheGrid Жыл бұрын
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.
@dinudavis8609 Жыл бұрын
@@ExcelOffTheGrid Thank You for your response. Your answer solves many of my problems. Thanks again. Have a good day :)
@robertwilliamson4400 Жыл бұрын
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.
@ExcelOffTheGrid Жыл бұрын
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.
@iankr Жыл бұрын
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?
@ExcelOffTheGrid Жыл бұрын
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.
@iankr Жыл бұрын
@@ExcelOffTheGrid Thanks, Mark. I shall sit and wait!
@frankbarendsen54688 ай бұрын
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"?
@misticoj10 ай бұрын
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 ?
@almcc22mcc Жыл бұрын
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.
@ExcelOffTheGrid Жыл бұрын
Hopefully, they will add PQ to Excel Online soon. As that would be a better solution.
@CP-zb3ky Жыл бұрын
Does Power Query work with Power Automate though?
@studyenglishwithfroggie4652 Жыл бұрын
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?
@ExcelOffTheGrid Жыл бұрын
Would you be willing to share the file with me, so I could take a look?
@studyenglishwithfroggie4652 Жыл бұрын
@@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
@BasilMustafa-n7j Жыл бұрын
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 ?
@ExcelOffTheGrid Жыл бұрын
The sheet name is one of the defined parameter passed from Power Automate. So it will only copy from one worksheet.
@nitisakphotaram6288Ай бұрын
Hi Mark , What is the most row that this script can handle ?
@JoeyAndHisGiraffe2 ай бұрын
Hi Mark - apologies for commenting on an 18 month old video, but this looks like it's almost exactly what I need. My only query is will this work if someone other than me uploads the file - will it still be able to run my Office script if, say, I'm not the one uploading the file or logged in to any services?
@ExcelOffTheGrid2 ай бұрын
The trigger is associated with the event, not with your log-in. So if somebody uploads a file into the specified folder, then it will trigger and run.
@ANASTASIO9999 Жыл бұрын
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
@ExcelOffTheGrid Жыл бұрын
You may have mis-names the worksheet in the workbook.
@amzvintazk Жыл бұрын
Hi... Can this handle large data? like 100,000 rows?
@ExcelOffTheGrid Жыл бұрын
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.
@amzvintazk Жыл бұрын
@@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?
@victoriaiwinski51477 ай бұрын
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?
@noodlebrains7 ай бұрын
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.
@chiragdabhi49294 ай бұрын
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???
@jessep8281 Жыл бұрын
Thank you for sharing your knowledge good sir!
@ExcelOffTheGrid Жыл бұрын
My pleasure! 😀
@jessep8281 Жыл бұрын
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.
@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 :)
@ExcelOffTheGrid Жыл бұрын
Glad it was helpful! We’re all going through the same learning curves in this, so just trying to share what I learn.
@marianapoli72905 ай бұрын
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?
@ExcelOffTheGrid5 ай бұрын
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.
@ManojKumar-zn2gf Жыл бұрын
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.
@zubairshimon9857 ай бұрын
Amazing!!! You are a true genius.
@ExcelOffTheGrid7 ай бұрын
Thanks - it took a bit of work to solve it. But now I know how, so happy to pass this on.
@FashionCobalt5 ай бұрын
thank you, but return wsRngArr couldn't be set as a parameter for next script in my work flow.
@thehannomeiring5 ай бұрын
I have the same issue, besides the output of get-data want's to open in a browser
@ComaGraphit3 ай бұрын
I had the same issue and had a hunch - switch to Old Designer, same as Mark used. It appears then (very weird dynamic content differs).
@sofiaibarra188311 ай бұрын
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 :)
@JessicaEricaPrudencio22 күн бұрын
Did you have answer to this? Still looking for tuitorial here but I cannot find one.
@aniljaggarwal60 Жыл бұрын
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.
@ExcelOffTheGrid Жыл бұрын
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.
@aniljaggarwal60 Жыл бұрын
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.
@CP-zb3ky Жыл бұрын
@@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.
@sannapohuja9962 Жыл бұрын
Can you paste append in a new workbook file? or does it need to be already created?
@dtttd Жыл бұрын
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.
@alicemorili1330 Жыл бұрын
Thanks for this video, I need copy and replace the data in existing workbook. Is there a code for that?
@ExcelOffTheGrid Жыл бұрын
The code could certainly be amended to do that. Try using the action recorder to find out the code.
@Trancer00611 ай бұрын
Is it not possible to do this with just power automate instead of the scripts?
@GeertDelmulle Жыл бұрын
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)?
@chriswall4795 Жыл бұрын
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
@TheBtrivedi Жыл бұрын
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.
@Trancer00611 ай бұрын
There is a app in Microsoft store that lets you click mouse buttons in set intervals, have you tried that?
@TheBtrivedi11 ай бұрын
@@Trancer006 I have never heard of such an app. Let me know the name of app. Thank you so much 🙏
@yasindukumarasiri857511 ай бұрын
Great Work. But it not works 100,000 rows. Only work like 10,000 rows. Please help
@rebekahhenken58055 ай бұрын
Why am I not seeing "wsRanArr" in dynamic content in my run script action? I can see "wsRanArr" if I use a compose action.
@geoffgriffith32382 ай бұрын
I can't see it either.
@dtttd Жыл бұрын
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.
@ExcelOffTheGrid Жыл бұрын
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.
@dtttd Жыл бұрын
@@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.
@RiskManagement-ex2nl8 ай бұрын
@@dtttd did you figure this out? I'm also getting an error on line 22 of the 'paste' script --- 10APR24
@MaryJoyBaylon-v7y Жыл бұрын
I have a question, how can I limit the range to 1k?
@SaythRenshaw7 ай бұрын
A guide on how to convert a email csv attachment to Excel would be great.
i continuously get an error in my flow that says "file format is not recognized". Followed your instructions step by step
@ExcelOffTheGrid4 ай бұрын
What are the file formats?
@sgeorg874 ай бұрын
@@ExcelOffTheGrid these are .xlsx
@RiskManagement-ex2nl8 ай бұрын
--- 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
@juanpabloalonso31226 ай бұрын
Hi, I am getting the same error, did you fix it? Could you please help me?
@RiskManagement-ex2nl6 ай бұрын
@@juanpabloalonso3122 yes, you have to update the JSON ‘paste’ script from ‘setValues’ to ‘setValue’ and now it works
@Lizzyblue66693 ай бұрын
@@juanpabloalonso3122 @ExcelOffTheGrid Hi, I'm getting the same error. Do any of you know how to fix it?