Wow! I had never considered excel scripts and it's a huge life saver. Spent way too long trying to get api calls or concurrency settings with arrays splits to add rows from larger data sets and this knocked it out in seconds with no issues. Thank you!
@mrzeitgeist Жыл бұрын
Damien sent me here from the comment section of another video that he uploaded. This video was very helpful and the flow/Excel script works like a charm. Great content!
@AdiCristea Жыл бұрын
Damien, I've watched this video before, very helpful in writing arrays to Excel, however I had a script where I needed dynamic range concatenation. Remembered something was in this video and I was right, that const rowRange formula, wow, priceless!! Thank you!
@DamoBird365 Жыл бұрын
Oh wow Adi, I am chuffed this has helped. Thank you for sharing with me 👍
@ethiopiancoffee5874 Жыл бұрын
Thank you so much for this simple explanation. I was searching for 2 days on how to do this. Your process was the simplest and most straightforward (no need to create a string of the data, reshape it, etc). Your process is also easy to use in numerous situations with minimal changes.
@DamoBird365 Жыл бұрын
So glad to hear it’s been of use 👍
@dmstar101 Жыл бұрын
This works fantastic. I was trying "apply to each" as per your earlier tube, but was timing out. this takes 30 second or less for 1300 lines of data.
@DamoBird365 Жыл бұрын
Wow, that’s brilliant 🤩 thanks for sharing. Glad this has helped. There is also graph api, just as fast kzbin.info/www/bejne/o3_adp5tnbGXnKc
@tylerkolota2 жыл бұрын
Hey Damien! I’m working on something special… something similar to the batch update & batch delete methods for SharePoint, but for Excel using Office Scripts. I think it’s my best template yet. The showcase of the script code in this video probably helped save me a few hours of searching & reading. Thanks for the examples!
@tylerkolota2 жыл бұрын
My initial test comparing it to 500 rows updated in a max concurrency Apply to each loop is showing at least a 10x speed boost (likely because several rows are timing out & failing to load at all!) & it’s using only 3% of the usual 500 actions.
@陳俐勻-y3s5 ай бұрын
Thanks!
@DamoBird3655 ай бұрын
Thank you for your kindness.
@bigworldparty Жыл бұрын
This was a very useful tutorial. The Excel script method of loading data works well, providing a huge reduction in run time compared to a power automate loop. But I ran into an issue trying to process json having over 6,500 items. A GatewayTimeout error occurs as the power automate is trying to execute the "run script" node. Power automate will repeatedly try to rerun the step with same error, until the user manually stops the execution. Apparently there is a limit to how many items can be processed, and to use this method with very large data sets, the sets would need to be split into batches.
@DamoBird365 Жыл бұрын
I've been working on a new simplified version. If you drop me an email to damien@damobird365.com, I will share it with you.
@nemmail11 ай бұрын
Hi @@DamoBird365. I also tried your method here and it works great with a small test dataset, but the full dataset i have is over 12000 rows and I get the same error as @bigworldparty. Any tips to get beyond this issue?
@tarekyeh624510 ай бұрын
Thanks a lot, this my first use of office scripts , didn't know that its that useful
@DamoBird36510 ай бұрын
You're welcome! 👍👍 I've a collection of Office Script videos here kzbin.info/aero/PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM
@adriancelis96913 жыл бұрын
This is great! Good timing too as I need to create a flow to populate Excel with data. Thanks!
@BenjaminDietz3 ай бұрын
Great video! Is there an option to control the column order dynamically? I would like to have 2 inputs, an array with data and an array specifying the column order.
@rohanshaw18272 жыл бұрын
Great video! you just saved me hours and hours of time!
@DamoBird3652 жыл бұрын
Cheers Rohan. How did this solve your problem? Would be good to understand.
@rohanshaw18272 жыл бұрын
@@DamoBird365 I used it to create an excel price list of our products. I took it further once I mastered getting the arrays into excel. after your tutorial everything just fell into place. still struggling with gettings images into the worksheet.
@DamoBird3652 жыл бұрын
@@rohanshaw1827 saving images to excel is an interesting use case. What’s the purpose there? Will they go onto the same sheet? Are you expecting to position or resize them?
@yiubili13 жыл бұрын
Wow that video looks amazing, it looks really complicated tho, have to try it out myself. Thanks very much for your sharing.
@yiubili13 жыл бұрын
meanwhile, would you please share the scripts that you are using please? As it is a bit worrying that we make any typo mistake on the scripts while mimic what you are doing in the video
@lalatendutripathy10 ай бұрын
Very informative!!
@XL-Mtg Жыл бұрын
Great videos Damien! I am trying to figure out a way to merge multiple excel files, and was hoping this would point me in the right direction...but I'm not able to quite make it work. The problem I'm trying to solve is this: I receive reports in my email daily containing 3k+ rows each, and I'm trying to use Power Automate to save the files to a Sharepoint folder, then merge them into one master file. The problems that I'm running into is that when I do a list / add rows it is limited to 256 rows by default...and even beyond that, it is extremely slow. I tried to use Excel scripts - and was able to use a script to retrieve the full set of rows from the source file...but when I try to then add the data into the new excel file, it keeps saying that it timed out (even though the flow only ran for 1 minute) or that it aborted. Any suggestions would be appreciated!
@DamoBird365 Жыл бұрын
There are limits, but quite reasonable, learn.microsoft.com/en-us/office/dev/scripts/testing/platform-limits#data-limits you could try a smaller file to make sure your solution is working. Then if it’s timing out on larger data, try graph or batching the requests.kzbin.info/www/bejne/o3_adp5tnbGXnKc
@XL-Mtg Жыл бұрын
@@DamoBird365 That's the odd part to me - the entire flow is taking less than 1 minute, and the Run Script portion only 30 seconds, yet it's returning either a "timeout" or "aborted" error: "We were unable to run the script. Please try again. Office JS error: Line 6: Range setValues: The request is aborted. clientRequestId: 648f40f2-2981-42a5-a29e-b0a1a3b2b3b4" "We were unable to run the script. Please try again. Office JS error: Line 6: Range setValues: Timeout clientRequestId: ef2a06ce-4c98-40f5-b69c-20bfa2777422" I did see your video on using Graph, and may try that...just confused why this script that I thought would be pretty straightforward isn't working! I did post the full script I'm using on the PA forum if that helps: powerusers.microsoft.com/t5/Building-Flows/Merging-Excel-Files-Office-Scripts-keep-aborting-or-timing-out/td-p/2247404
@leicesterphotos52372 жыл бұрын
This is great, but what would the script be to just add the data to an existing table in Excel?
@DamoBird3652 жыл бұрын
I am sure it could but I haven’t written anything similar. I will add it to my list.
@leicesterphotos52372 жыл бұрын
@@DamoBird365 much appreciated - thank you
@HeatherR-vz5wo7 ай бұрын
@@DamoBird365 Hello! Do you have a video on what the script would be to add the data from an array into an existing Excel table? Thanks!
@notonurnelly3 жыл бұрын
Another great video that will be very useful on an upcoming project. Bit of a stupid question around file content... you used it create a blank file if we have an existing quite complex template that we wish to copy to another SP doc library and then populate with table data will the File Content Function and the PA Flow still work?
@DamoBird3653 жыл бұрын
In theory, yes it will work. The max file size supported is 25MB docs.microsoft.com/en-us/connectors/excelonlinebusiness/ but proof is in the pudding.
@hokemspitter8 ай бұрын
I work on a GCCH tenant and the admin has disabled scripts. My use case is that a user periodically will upload a spreadsheet and I need to be able create the table for the new sheet with it's table range. Even though we first delete the previous version, when I attempt to create the table on the new file with it's range, the action fails stating that the table there already (seemingly ignoring that I deleted it and that the file is a new file).
@DamoBird3658 ай бұрын
Because you delete the file, I think you’ll need to get the file Id dynamically. A bit like this video: Run Excel Office Script on Dynamic Path using Power Automate: Resolve Unexpected Response Error kzbin.info/www/bejne/pmOZpH9ppq2gnNU but appreciate this is another script video. You could try inserting the path into a compose and the compose output into the excel action. This will mean that the flow won’t be trying to use the same fileid, which gets updated when you delete the file. 🤞
@hokemspitter8 ай бұрын
@@DamoBird365 Thank you sir. I'll try that tomorrow at the office and let you know.
@bryansebeck Жыл бұрын
Alright, I'm super grateful for finding this video. I've got a flow that automatically adds data to a table in bulk and the last time it ran, it took 6 hours to work through the Apply to Each, and it only had 197 rows to add! I'm having a bit of an issue, though, implementing this solution. When I attempt to set up the Office Script in excel, using exactly the same inputs as you've got (just attempting a proof of concept), I get a few errors. The name arrayfromflow errors with "Office Scripts cannot infer the data type for this variable. Please declare a type for the variable". Then, the arrayofdata[] tells me "[Power Automate Incompatibility Error] Parameter type error for parameter 'arrayfromflow: Array': 'TypeReference' is not a valid array type." Any idea what the heck is happening?
@DamoBird365 Жыл бұрын
I’m not familiar with that error to be honest. You’re alternative, if you don’t fix, is to try graph api kzbin.info/www/bejne/o3_adp5tnbGXnKc
@sllleeepy10 ай бұрын
Hey Dam*ien, thank you for the video, very useful. One question. I want to use the second script to populate an existing excel which already has data inside (and atable), will this script overwrite the existing data? If so, maybe i should make the range from which it fill the rows dynamic?
@DamoBird36510 ай бұрын
Check out kzbin.info/www/bejne/naXPnJ-wnpuAesksi=e1j_OW2R2cEMLxzv the script here should deal with existing data or at least show how you can. 👍
@sllleeepy9 ай бұрын
@DamoBird365 super! Twicked the script a bit to make it work for me and now something which was taking more than 2h, takes around 20 seconds. Many thanks!
@DamoBird3659 ай бұрын
@@sllleeepy that’s amazing 😱🥳
@AbenSabualunkal Жыл бұрын
Hi Damien, Thanks for the tutorial. it really helped me. I have a quick question. In this video you have mentioned the header and the range of the table right. Is it possible to get the header and the range of the table dynamically from the array(content)
@DamoBird365 Жыл бұрын
Hi, I show how to in my latest video using graph api kzbin.info/www/bejne/o3_adp5tnbGXnKc
@erinfield19435 ай бұрын
4:27- his office script accepts an array 5:18- "Select" action gives you an array 7:19- "array of data"
@nekosikaleido502920 күн бұрын
Hi I keep getting an error once I try to open the new file 'file format may not be matching with the file extension' Any ideas ?
@DamoBird36520 күн бұрын
It’s most likely the file content isn’t right. But, try this method, it’s easier for creating an excel file. Create Excel File in SharePoint via Power Automate Cloud Flow kzbin.info/www/bejne/naOZY6aKZ76qmpo
@vernloke23912 жыл бұрын
Hi Damien, do you have a tutorial that can append 2 lists (one to many relationship)?
@DamoBird3652 жыл бұрын
An older video but SharePoint Lists One to Many Relationship - Power Platform kzbin.info/www/bejne/mXKrg6Wjj6etjLc
@AbhishekVerma-kj6us25 күн бұрын
Did you write the excel script yourself or it was created by Power Automate?
@DamoBird36525 күн бұрын
I learned by recording the scripts manually in excel and editing them. Now gpt can support you if you are prepared to troubleshoot and test.
@mrli83 Жыл бұрын
Great vid. I dont see any scripts in my dropdown. Is this a standard script? Or where can I implement the script to use this option?
@DamoBird365 Жыл бұрын
It's a script that I have written and you can download from the description link.
@trentbrewer33362 жыл бұрын
I can’t wait to get this one working! I am getting a error on the arrayfromflow variable. It says office scripts cannot infer the data type of the variable. Any ideas?
@DamoBird3652 жыл бұрын
Hi Trent, not sure. Have you modded the script?
@purplestars81592 жыл бұрын
Paramount thanks, i have a string as date in sharepoint list. can u help me how can i show in excel as date coulmn?
@DamoBird3652 жыл бұрын
What is your experience when you try to add a date to excel? Did you get an error?
@mariopelosin85552 жыл бұрын
Hi could you shre the script, just as and example to follow ?
@DamoBird3652 жыл бұрын
Please try and download from here and copy to a new script github.com/DamoBird365/PowerAutomate/blob/main/OfficeScripts/CreateNewTable%20Office%20Script.txt
@Abhishja2 жыл бұрын
hi... how about 1 Millions rows is loading in Run Script? Excel Limited dataset like only 5 MB as per MS office.
@DamoBird3652 жыл бұрын
I don't have a solution for beyond the limits I am afraid. You could maybe try batching the data? Graph API also lets you load data to Excel but I am not sure of those limitations.
@alexanderalangghya61392 жыл бұрын
Hello , thank you for the video sir, but may I ask why did I get an error "Line 11: Cannot read properties of undefined (reading 'length')" ?
@DamoBird3652 жыл бұрын
Possibly a typo in your length expression.
@alexanderalangghya61392 жыл бұрын
@@DamoBird365 Thank you for the response sir, but I am sorry how to check length expression? And if you don't mind I will put the code below . function main(workbook: ExcelScript.Workbook,Name:string, NRP:string, Department:string, content: Content[]) { let daily_Logbook = workbook.getWorksheet("Daily Logbook"); // Set range C6:H8 on daily_Logbook daily_Logbook.getRange("C6:F6").setValue(Name); daily_Logbook.getRange("C7:F7").setValue(NRP); daily_Logbook.getRange("C8:F8").setValue(Department); const contentOffset = 12; for (let i = 0; i < content.length; i++) { const currentContent = content[i]; const formattedContent = [[currentContent.day_date, currentContent.start_time, currentContent.end_time, currentContent.duration, currentContent.category, currentContent.sub_category, currentContent.job, currentContent.description, currentContent.title, currentContent.link, currentContent.en ]]; const contentCell = `B${contentOffset + i}:N${contentOffset + i}`; daily_Logbook.getRange(contentCell).setValues(formattedContent); } } interface Content { day_date: string, start_time: string, end_time: string, duration:string, category:string, sub_category:string, job:string, description:string, title:string, link:string, en:string }
@TraderZo1 Жыл бұрын
For some reason I keep getting row 3 be a "total" column when I run this script. Any ideas?
@DamoBird365 Жыл бұрын
Can you copy the error word for word? Never seen this one. Is your array, an array of arrays? [[],[],[]]
@tarekyeh62455 ай бұрын
this method don't work while trying to send 5000 rows , I always get bad gateway error after 15 min, if I limit the data to 100 it works
@DamoBird3655 ай бұрын
I’ve since demonstrated more efficient methods here: Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate kzbin.info/www/bejne/naXPnJ-wnpuAesk
@tarekyeh62455 ай бұрын
@@DamoBird365 thanks will try it , I think the issue is due appending to rows which contains formulas , for some reason it makes the flow very slow
@koncorporation Жыл бұрын
Hi Great Channel! I tried to run the script and are getting this error "Office JS error: Line 5: Workbook getActiveWorksheet: The request failed with status code of 404, error code ResourceNotFound and the following error message: Invalid version: error clientRequestId: abd88804-1630-4e1f-9acb-34b7c27c9ec9" it only seems to happen when I turn on pagination and try to get more than 5000 rows. I need to create an excel from a dataverse table with about 6000-11000 rows. Thanks again for the videos!
@DamoBird365 Жыл бұрын
Hi there. Check out the following solution learn.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset there are limits, 120 seconds runtime on a flow action, so it might be that limit you are seeing.
@jl13993 ай бұрын
Have you ran into a 2 minute time out limit issue?
@DamoBird3653 ай бұрын
You’ll need to work with that limit and batch or improve the performance. Here’s a more recent example: Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate kzbin.info/www/bejne/naXPnJ-wnpuAesk
@krisranger8 ай бұрын
This script could be use to add data into an existing table with data 😄🙄🙄
@DamoBird3658 ай бұрын
👍👍
@HeatherR-vz5wo7 ай бұрын
What would the script be to add data into a table?
@davefiorante3104 Жыл бұрын
Hi Damien, I recently came across your video and you helped me solve an issue I've been dealing with for awhile now! I sent you an email with a few questions - any help you can provide on this is greatly appreciated!
@DamoBird365 Жыл бұрын
I’ve replied to your email with suggestions. Please let me know how you get on.
@stephaniemarie6362 жыл бұрын
Hey Damien- I've followed your video, but for some reason I can't get the script to run properly. I get this error... "We were unable to run the script. Please try again. Office JS error: Line 22: Worksheet getRange: The argument is invalid or missing or has an incorrect format. clientRequestId: c2acdf8a-25fa-4ce2-b7aa-5dcbe236c41e" Here's my code at line 22... const rowRange = 'A${starterrow + i}:T${starterrow + i}'; selectedSheet.getRange(rowRange).setValues(formattedrow); Do you have any idea of what my problem is? Here's my full code... function main(workbook: ExcelScript.Workbook, arrayfromflow: arrayofdata[],sheetname: string ) { let selectedSheet = workbook.getAvtiveWorksheet(); selectedSheet.getRange("A1:T1").setValues([["Title", "Mon", "Tues", "Wed", "Thurs", "Fri", "Sat", "Sun", "Total", "Employee", "Manager", "Comments", "WeekStart", "Status", "BillTo", "Created", "CreatedBy", "ID","Modified", "ModifiedBy"]]); let newTable = workbook.addTable(selectedSheet.getRange("A1:T1"), true); newTable.setName('MyTableName'); selectedSheet.setName(sheetname); const starterrow = 2; for(let i = 0; i < arrayfromflow.length; i++){ const currentObject = arrayfromflow[i]; const formattedrow = [[currentObject.Title, currentObject.Mon, currentObject.Tues, currentObject.Wed, currentObject.Thurs, currentObject.Fri, currentObject.Sat, currentObject.Sun, currentObject.Total, currentObject.Employee, currentObject.Manager, currentObject.Comments, currentObject.WeekStart, currentObject.Status, currentObject.BillTo, currentObject.Created, currentObject.CreatedBy, currentObject.ID, currentObject.Modified, currentObject.ModifiedBy]]; const rowRange = 'A${starterrow + i}:T${starterrow + i}'; selectedSheet.getRange(rowRange).setValues (formattedrow); } } interface arrayofdata { Title: string, Mon: string, Tues: string, Wed: string, Thurs: string, Fri: string, Sat: string, Sun: string, Total: string, Employee: string, Manager: string, Comments: string, WeekStart: string, Status: string, BillTo: string, Created: string, CreatedBy: string, ID: string, Modified: string, ModifiedBy: string } Thanks for your help, Stephanie
@dpkatz452 жыл бұрын
I'm getting the same error, were you able to figure it out?
@dpkatz452 жыл бұрын
Someone explained the issue to me: const rowRange = 'A${starterrow + i}:T${starterrow + i}'; selectedSheet.getRange(rowRange).setValues (formattedrow); You have (as I did) single quotes ' but you should have had backticks (the key before 1) ` it should be: const rowRange = `A${starterrow + i}:T${starterrow + i}`; selectedSheet.getRange(rowRange).setValues (formattedrow);
@DamoBird3652 жыл бұрын
Thanks for sharing David.
@EnneagramTarot-zu8kp Жыл бұрын
8:41 - Hi Damo, what if you have to say, "if Property type is Multifamily, populate this field. If Property type is anything else, populate this other field"?
@DamoBird365 Жыл бұрын
I don’t understand. Is your requirement based on datatype? Do you want to populate different columns in excel depending how n datatype? By multifamily do you mean array?
@erinfield1943 Жыл бұрын
Oh sorry. I'm working in real estate in different types of buildings.