Create & Populate Excel File Fast - Office Scripts without an Apply to Each

If you are looking to export data from SharePoint Lists or DataVerse Tables using PowerAutomate and Import this into Excel fast and efficiently (6 seconds for 1000 items/rows during this video), you need to give Office Scripts a try. The "add rows to a table" action running in an "apply to each" for large data sets will timeout or be extremely slow. Let me show you how you can use OfficeScripts and load datasets with large volumes in a handful of seconds.
Can't use Office Scripts? Have you tried looking at Graph API? • Create and Populate an...
With Office Scripts you do not need to reference a table like the traditional excel actions. In my final solution, I create a brand new Excel File using a technique I have previously demo'd here • Create a new Excel Fil...
0:00 - Intro and Overview
1:22 - First Flow using Apply to Each & Add Row to Table
3:51 - Second Flow Using Office Scripts for Fast and Efficient Import
6:51 - Reviewing the Office Script in the Code Editor
9:42 - 1000 Dataverse table rows into an existing Excel file in 6 seconds flat!
10:46 - Third Flow Creating a new Excel File with the Dataverse Data
#Dataverse #SharePoint #Lists #PowerAutomate #OfficeScript
Download sample script github.com/DamoBird365/PowerA... Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕

[@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!

[@HuffDiddy24]

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!

[@adriancelis9691]

This is great! Good timing too as I need to create a flow to populate Excel with data. Thanks!

[@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 👍

[@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 👍

[@tylerkolota]

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!

[@tylerkolota]

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.

[@tarekyeh6245]

Thanks a lot, this my first use of office scripts , didn't know that its that useful

[@DamoBird365]

You're welcome! 👍👍 I've a collection of Office Script videos here kzbin.info/aero/PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM

[@yiubili1]

Wow that video looks amazing, it looks really complicated tho, have to try it out myself. Thanks very much for your sharing.

[@yiubili1]

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

[@lalatendutripathy]

Very informative!!

[@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

[@notonurnelly]

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?

[@DamoBird365]

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.

[@rohanshaw1827]

Great video! you just saved me hours and hours of time!

[@DamoBird365]

Cheers Rohan. How did this solve your problem? Would be good to understand.

[@rohanshaw1827]

@@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.

[@DamoBird365]

@@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?

[@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

[@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.

[@vernloke2391]

Hi Damien, do you have a tutorial that can append 2 lists (one to many relationship)?

[@DamoBird365]

An older video but SharePoint Lists One to Many Relationship - Power Platform kzbin.info/www/bejne/mXKrg6Wjj6etjLc

[@trentbrewer3336]

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?

[@DamoBird365]

Hi Trent, not sure. Have you modded the script?

[@sllleeepy]

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?

[@DamoBird365]

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. 👍

[@sllleeepy]

@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!

[@DamoBird365]

@@sllleeepy that’s amazing 😱🥳

[@purplestars8159]

Paramount thanks, i have a string as date in sharepoint list. can u help me how can i show in excel as date coulmn?

[@DamoBird365]

What is your experience when you try to add a date to excel? Did you get an error?

[@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 [e-mail], I will share it with you.

[@nemmail]

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?

[@hokemspitter]

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).

[@DamoBird365]

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. 🤞

[@hokemspitter]

@@DamoBird365 Thank you sir. I'll try that tomorrow at the office and let you know.

[@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

[@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

[@Abhishja]

hi... how about 1 Millions rows is loading in Run Script? Excel Limited dataset like only 5 MB as per MS office.

[@DamoBird365]

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.

[@leicesterphotos5237]

This is great, but what would the script be to just add the data to an existing table in Excel?

[@DamoBird365]

I am sure it could but I haven’t written anything similar. I will add it to my list.

[@leicesterphotos5237]

@@DamoBird365 much appreciated - thank you

[@HeatherR-vz5wo]

@@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!

[@mariopelosin8555]

Hi could you shre the script, just as and example to follow ?

[@DamoBird365]

Please try and download from here and copy to a new script github.com/DamoBird365/PowerAutomate/blob/main/OfficeScripts/CreateNewTable%20Office%20Script.txt

[@alexanderalangghya6139]

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')" ?

[@DamoBird365]

Possibly a typo in your length expression.

[@alexanderalangghya6139]

@@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? [[],[],[]]

[@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.

[@tarekyeh6245]

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

[@DamoBird365]

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

[@tarekyeh6245]

@@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

[@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.

[@krisranger]

This script could be use to add data into an existing table with data 😄🙄🙄

[@DamoBird365]

👍👍

[@HeatherR-vz5wo]

What would the script be to add data into a table?

[@erinfield1943]

4:27- his office script accepts an array 5:18- "Select" action gives you an array 7:19- "array of data"

[@stephaniemarie636]

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

[@dpkatz45]

I'm getting the same error, were you able to figure it out?

[@dpkatz45]

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);

[@DamoBird365]

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.

[@erinfield1943]

I have a column called property type.

[@erinfield1943]

Like how would you write the if else statement?