Effortlessly Split Excel Workbooks: Power Automate & Excel Office Scripts Solution

Рет қаралды 14,509

Күн бұрын

Are you tired of manually sorting through large Excel workbooks? In this video, I'll show you how to split an Excel workbook into multiple worksheets based on a key column using Power Automate and Excel Office Scripts. Not only that, but you'll also learn how to split the same workbook into multiple individual workbooks containing the unique data for the distinct key column values.
I'll take you step-by-step through the process using two sample datasets downloaded from Kaggle and show you how to implement the solution via the sample code that is available to download on my blog post. Plus, I'll show you how to bulk load any data into a new Excel file using Office Scripts. This is a great solution for those who want to automate the process of sorting through large Excel workbooks and increase productivity.
Don't miss out on this opportunity to learn how to split an Excel workbook into multiple worksheets using Power Automate and Excel Office Scripts. Make sure to check out my other Office Script demo videos at • Office Scripts , my blog post www.damobird365.com/split-a-w..., and my other videos like "Create an Excel File from Compose • Create a new Excel Fil... "
Don't forget to hit the like and subscribe button!
00:00 Intro
00:43 Checking out the example Workbooks
01:07 Run an Office Script to Split the Workbook
03:56 Reviewing the Flow Output and Excel Worksheets
07:17 Build a Cloud Flow to Create Individual Workbooks
14:57 Reviewing the Flow Output and Excel Workbooks
17:15 Lets check out the Office Script Code
21:05 Bulk load any data into a new Excel File using Office Scripts
24:40 Outtro, summary and reflection Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕

Пікірлер: 112

@Kralnor 2 жыл бұрын

Lately I've been trying to filter an existing workbook and create a new one based on the filltered values, so this was exactly what I needed. Looks like the most feasible way is to use Office Scripts and pass around a multi array. I hope Microsoft adds filtering actions in the future, but for now this will do very nicely. Thanks a lot!

@usiala Жыл бұрын

A great video. Thank you.

@sams2285 2 жыл бұрын

This is amazing!

@daxteoh27 Жыл бұрын

Hi damo, this code is insane! save so much effort compared to my split action in power automate. But I have one question, how can I get the output file to somewhere else, preferably input from power automate? Once again, great job! please keep up the effort in office script video, rarely can find a good teacher like you!

@DamoBird365 Жыл бұрын

Cheers. I’ve a few videos on Office Scripts but the more recent ones show you how to load an array into a new excel file or work with dynamic paths. The alternative is to move the file after it’s created. Here’s my office script playlist kzfaq.info/sun/PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM let me know how you get on.

@daxteoh27 Жыл бұрын

@@DamoBird365 great thanks! i realized that in that video you have already covered it. I was overlooked. :D

@Jamie.Mcgauley 5 ай бұрын

I am trying to tweak this solution but falling short, I want to do exactly as you did but the source file will have multiple tables on multiple sheets that each need to be filtered by a common key field and the outputs of all 3 tables as an array to be entered into one excel file with multiple tables in their own sheets (example below). Tried a few tweaks for running multiple scripts in the flow for each table to be filtered and added sheet parametrisation to handle where the secondary tables land but not getting expected results and often my flow goes into a circular hole and doesn't complete, I'm missing something very simple I think but not sure what it is :D Source Workbook Example Scenario: Workbook contains Sheet1 With Table1, Sheet2 with Table2 and Sheet3 with Table3. Each of the above tables share a common key field with same distinct values in each and same naming conventions. For this purpose lets imagine there are just 2 distinct key field values in each table (identical to each other) hence we expect 2 files to return. Output Expected: File1: KeyFieldName1.xlsx with Sheet1 With Table1, Sheet2 with Table2 and Sheet3 with Table3 File2: KeyFieldName2.xlsx with Sheet1 With Table1, Sheet2 with Table2 and Sheet3 with Table3

@DamoBird365 5 ай бұрын

This is one for the forum I think. 👍 but will be possible I am sure.

@Jamie.Mcgauley 5 ай бұрын

@@DamoBird365 I cracked it :) was too focused on beating power automate nested apply to each when I just needed to change the 2 scripts to handle 3 arrays and 3 tables, just need to fix the date formats flipping from UK to US locale during the process, unsure if thats power automate or the script causing this? Either way thanks for the inspiration on this as will solve a big headache I have each month!

@DamoBird365 5 ай бұрын

Fantastic 🎉

@waganikki1932 11 ай бұрын

Hello may I know how do I check the numbers of sheets available in the workbook and continue the flow depend on the number of it? The sheets in the workbook I am getting is dynamic so I am not sure how to check the numbers of sheet and work on it in the office script

@user-gz2ck2tv4w Жыл бұрын

Hi, I appreciate your videos a lot and it had helped me tremendously as I'm trying to learn this useful tool. I have a problem however, that when I run your second script onto one of my files(splitting workbook into mutiple files), it gives me error with a status code of 404, which after some google research it tells me that maybe the data array is too big and the function used is unable to create a range for it. Or maybe it's some other underlying issue. Could you suggest any ways to mitigate this problem?

@DamoBird365 Жыл бұрын

What you’ve suggested is possibly true. How big is the file? How long does the script run for each run script action, it has 2 minutes to complete. Alternatively have you run the solution on a smaller dataset to test?

@user-gz2ck2tv4w Жыл бұрын

@@DamoBird365 Hi! Thanks for the quick reply haha. The file that I run the script on is about 2,331 kb and it has about 11k rows. There are like forty columns and some of the columns have a lot of data in it. The first script consistently runs about less than a minute, while the second one runs about 6-7 minutes before it announces the error. Weirdly enough, each file that is created is populated properly, except for the one key data that has over 4000 rows. I have removed that particular key data before and everything runs perfectly fine. But if I try to run it individually, it shows the same error again. I'm at a loss at what to do 😥

@DamoBird365 Жыл бұрын

Are you creating multiple worksheets in the same workbook or new workbooks? If the latter, is it populating the workbook that fails for the larger dataset, which still doesn’t sound that large to be honest.

@user-gz2ck2tv4w Жыл бұрын

@@DamoBird365 I'm creating multiple workbooks. And yes, the script fails to populate the workbook where the key column has 4000 rows. It just shows the error Line 9: worksheet get rangeByindexes: the request failed with the status code of 404, error code resourceNotFound.

@DamoBird365 Жыл бұрын

In the following video Create & Populate Excel File Fast - Office Scripts without an Apply to Each #PowerAutomate #Excel kzfaq.info/get/bejne/as1of8thzOCpnpc.html I populate a new file with 1000 rows in 6 seconds. It’s possible that there is something in the 4000 rows that cause the script to fail. It would need debugging/ trial and error to work out why. Have a look back at the history input of the flow.

@sleepyrick Жыл бұрын

Hey Damien, I've nearly got your flow running, but the final product for each workbook ends up being a table with 1 header, and one body cell, the workbook table header being the raw array itself. example: [["Site_Name","Site_Number"],[2,"b"],[2,"b"],[2,"b"],[2,"b"],[2,"b"],[2,"b"],[2,"b"],[2,"b"],[2,"b"]] This appears to be related to the 'range' value within 'Create Worksheet based on Data Array' Script: let newTable = workbook.addTable(range, true); Do you happen to know what may be causing this issue? Thank you!

@DamoBird365 Жыл бұрын

It might be a case of removing the value for the run script action, clicking the icon right of the cell to turn on array mode and re-inserting the dynamic value?

@sleepyrick Жыл бұрын

@@DamoBird365 Thanks for the swift reply. I'm not following. Would this be a change in the script itself, or would this mean working within the Excel file itself, after the flow has completed?

@sleepyrick Жыл бұрын

I see that you've addressed this with another person - I'll review that thread. Thank you!

@DamoBird365 Жыл бұрын

@@sleepyrick I’m guessing you’ve not setup the action correctly, assuming you’ve not made changes to the original flow or script?

@user-wr6gj5rl5p 5 ай бұрын

Hi, great video. Please How obtain the code in FileContent? How convert blank XlFile in base64?

@DamoBird365 5 ай бұрын

I have demo'd it in another video but there is an easier way using SharePoint api - check this out here kzfaq.info/get/bejne/ndqcns-rzpyxdJs.html

@otanzion9584 2 жыл бұрын

Thanks for the amazing work. I tried to follow the steps but at the Run script step the script dropdown is blank. comment on the textbox is "Select the office script you want to run from the dropdown". Can you assist?

@DamoBird365 2 жыл бұрын

Strange one. Presumably you’ve created the script in excel ok?

@margaritafloresmed5470 Жыл бұрын

Is there a way we could keep the formating (colors, fonts, etc) of the main sheet into the copies we're creating?

@DamoBird365 Жыл бұрын

You could try something like: How to split a workbook into multiple files with PowerAutomate & Office Scripts #PowerAutomate kzfaq.info/get/bejne/asCZgM2bu5fHY4U.html

@Halaweeg Жыл бұрын

Thank you for the video I really enjoyed it and it worked with me with a small issue that the array comes all in the new excel as one cell not split into columns and rows. Any guidance regarding this?

@DamoBird365 Жыл бұрын

If you are using the same scripts, could you be passing the data as a text string? Check the script action, remove the dynamic value an change to array mode, reinsert the dynamic value.

@Halaweeg Жыл бұрын

@@DamoBird365 I am not sure how to change to array mode Thank you for your help

@DamoBird365 Жыл бұрын

@@Halaweeg there is a small icon, T, next to the input parameter. Remove your dynamic value and press the button. Then reinsert the dynamic value and try again.

@Halaweeg Жыл бұрын

@@DamoBird365 Working now, Thank you

@DamoBird365 Жыл бұрын

@@Halaweeg what was the fix?

@CelesteGrinage 2 ай бұрын

This is what I’ve been looking for. Can i download the script. Your site is not working.

@DamoBird365 2 ай бұрын

Good shout 👍 site is back up - been migrating.

@joannablack 2 жыл бұрын

Is there a way to run this on data that is the result of a query? I'm getting an error "We were unable to run the script. Please try again. Office JS error: Line 14: Workbook addTable: A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table. clientRequestId: 57ad3807-4ae3-4870-bf06-3fee5e91fe12"

@rebekahhenken5805 Ай бұрын

@DamoBird -- can you respond, I have a similar issue.

@nevans8231 Жыл бұрын

Let's say my file being split also has an identical copy file but without the data (template). The table and formatting still exists in the copy, and I merely want to paste the data from the file being split into the file template - and then save each split workbook using that same template. How do I add that action into this flow?

@DamoBird365 Жыл бұрын

You’ve got a few possible options. Based on the amount of data, you could use native actions to list rows and add a row, or office scripts to return data as array and then populate via script or graph api. I’ve examples of graph api and office scripts.

@pimpc142k Жыл бұрын

this is a great video. Have you had any issues where the script takes longer than 2 mins and getting a bad gateway error? i have about 15k rows that is being broken up into 1,000 separate files.

@DamoBird365 Жыл бұрын

2 minutes would be a known limitation of the connector. Might have to rethink how the solution could work for you? learn.microsoft.com/en-us/office/dev/scripts/testing/platform-limits#power-automate

@pimpc142k Жыл бұрын

@@DamoBird365 for now I broke it up to 3 files about 6k rows each and ran them in parallel to hit a deadline but open to other options.

@DamoBird365 Жыл бұрын

What’s the overall requirement / use case?

@saniyaali870 Жыл бұрын

@@pimpc142k I am facing the same issue. Can you please explain how you ran in parallel in detail? Thank you

@saniyaali870 Жыл бұрын

@@DamoBird365 I am getting the same error. My use case is to split 30,000 row excel file into 800 files. Can you please help with this?

@constantinrusencu3534 Жыл бұрын

Hi Damien, I noticed when the flow is shared with run only users, the scripts don't work. I googled and it seems the Run Script action in Power Automate only works with scripts created by the user running the connection. The fix is for run-only user to use the Excel connection of the flow owner if the flow owner created the script, which was my case.

@DamoBird365 Жыл бұрын

Thanks for sharing Constantin. I was having this conversation today and you’ve reconfirmed their experience too. They went with a licensed service account.

@qina Жыл бұрын

what should I do if I need to add a row from bulk worksheet into multiple existing worksheets? e.g.; I have a worksheet containing 1000 rows, i need to distribute the data based on key column into 10 existing worksheets already available in a Sharepoint

@DamoBird365 Жыл бұрын

Hi Qina, I’ve replied to you on my blog post.

@cindymitchell0614 Жыл бұрын

What changes would I need to make for the file to be dynamic, but in the same folder? The initial file will be generated monthly and this flow will break that workbook out into multiples files so the file names will always be dynamic.

@DamoBird365 Жыл бұрын

Hey Cindy, if you’ve tried and getting an error, this might help? Run Excel Office Script on Dynamic Path using Power Automate: Resolve Unexpected Response Error kzfaq.info/get/bejne/pphmpq9k1q7Rlqc.html let me know how you get on.

@cindymitchell0614 10 ай бұрын

@@DamoBird365 Thanks for the info! Still haven't been able to solve this. I run into a lot of errors using the scripts, saying Graph Item not found. I have the scripts shared from a SP library and using the Run Script from SP action. Any thoughts or other ways to do this without scripts?

@DamoBird365 10 ай бұрын

@@cindymitchell0614 is your data in tables in the excel file? You could try and get each table of data and then create new excel files for each. Graph API can be used to populate files efficiently too. Sorry that office scripts isn’t working out for you. Have you posted on the Ms forum?

@cindymitchell0614 10 ай бұрын

@@DamoBird365 Yes, the data is in a table and always with the same table and sheet name. I have an initial flow that exports filtered items to excel from a SP List at a company level. This second flow is to break that report out by region into their own workbooks. No, I haven't posted there yet. I was hoping to get your way to work :).

@kiransunka1 2 ай бұрын

How can we sent these files through after splitting sheets

@krisranger 3 ай бұрын

Will it be possible to paste the data from Excel into an existing table with data? 🤔🤔🤔

@DamoBird365 3 ай бұрын

Do you mean merging sheets or merging workbooks? Both of which would be possible.

@krisranger 3 ай бұрын

What I mean is that when pasting the data, it can be pasted into files that already have previous data instead of new files.

@krisranger 3 ай бұрын

I tried, but all the data ended up stuck in one cell, meaning the data didn't align into the corresponding columns.

@azzajkhan873 Жыл бұрын

Hi Can you please also share how to get i.e fetch data from D365 and then start the flow

@azzajkhan873 Жыл бұрын

Am unable to run the script Runtime error :line 26(canot read properties of undefined(reading 'getRangebetween HeaderAndTotal'

@DamoBird365 Жыл бұрын

I would recommend you reach out to the community powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums 👍

@user-ky1vj1ib5p 4 ай бұрын

This is great, and I'm so close to getting the correct output. However, the individual workbooks that get created are incorrect. They have correct file name, the correct headers, but then the data rows within the tables are blank. I can't figure out why....any insight on this?

@DamoBird365 4 ай бұрын

Best thing to suggest if you look back at your flow history. Work your way down the actions and check to see at what point it goes wrong. Failing that you could try sharing your flow on the forum. It is more likely to be a small error in the build.

@user-ky1vj1ib5p 4 ай бұрын

I figured it out! In the script for converting the data into an array, I accidentally omitted the $ in the second occurrence of 'key'. The output is correct now. Thank you!

@DamoBird365 4 ай бұрын

@@user-ky1vj1ib5p amazing 🤩 thank you for letting me know.

@user-mf1kt7km8p 4 ай бұрын

how to get that file content??? pls help

@manjupulsar Жыл бұрын

@damoBird365 If i run the script create workbook based on data array, I'm getting error on Line 9: Cannot read properties of undefined (reading 'data'), Code on line 9 is let range = sheet.getRangeByIndexes(0, 0, worksheetInformation.data.length, worksheetInformation.data[0].length); Could help me to fix this? Thanks

@DamoBird365 Жыл бұрын

I don’t know to be honest. Look at the history, are you sending anything to the script?

@manjupulsar Жыл бұрын

@@DamoBird365 I'm not sending anything to script just followed what the video, copied the script from the blog function main(workbook: ExcelScript.Workbook, MainTable: string = "Table1", //new table name worksheetInformation: WorksheetData) { // Get default worksheet Sheet1 let sheet = workbook.getWorksheet(`Sheet1`); // Create range based on the size of data let range = sheet.getRangeByIndexes(0, 0, worksheetInformation.data.length, worksheetInformation.data[0].length); //Populate sheet with data range.setValues(worksheetInformation.data) //Autofit column width range.getFormat().autofitColumns(); //Create New Table let newTable = workbook.addTable(range, true); newTable.setName(MainTable); } // An interface to pass the worksheet name and cell values through a flow. interface WorksheetData { data: string[][]; }

@manjupulsar Жыл бұрын

@@DamoBird365 i have data in different sheets, do you have any other solution/script to split the each sheets into individual workbooks?

@DamoBird365 Жыл бұрын

@@manjupulsar I’ve quite a few videos now, apologies. Do you have a sheet1? Does your data start from a1?

@manjupulsar Жыл бұрын

@@DamoBird365 I've Sheet1 and Data start from a1, first row is the header.

@pragadeshvaranduraisamy6457 2 жыл бұрын

Do you have any videos for power automate management connect?

@DamoBird365 2 жыл бұрын

I don’t I’m afraid. What are you looking to achieve.

@pragadeshvaranduraisamy6457 2 жыл бұрын

@@DamoBird365 there is a connector called power automate management, through that we can manage all the power automates. Just checking if you have used this comnector

@DamoBird365 2 жыл бұрын

@@pragadeshvaranduraisamy6457 I know the ones you mean, haven’t used them extensively yet. What is it you want to do?

@user-md7jh5uj5e 6 ай бұрын

Hi Damo, under apply to each, I do not have as many dynamic values as you do for run script option

@DamoBird365 6 ай бұрын

You could maybe write an expression? 🤞

@user-md7jh5uj5e 6 ай бұрын

@@DamoBird365 could you give an example? I cannot find the run script" result" dynamic value

@user-md7jh5uj5e 6 ай бұрын

@@DamoBird365 godsent, it's 3.52am over here, really didn't expect your prompt response

@DamoBird365 6 ай бұрын

@@user-md7jh5uj5e 😱 time for some sleep 💤. If you let me know where this is in the video with a time, I will take a look when I get a moment.

@user-md7jh5uj5e 6 ай бұрын

@@DamoBird365 @9:44, there are many options for your run script, but for mine, there is only a "body" option

@behar450 Жыл бұрын

I am trying to split a column of states and create a new file for each state. But this is not showing any of the rows in any of the files

@DamoBird365 Жыл бұрын

You’ll need to explore the flow history to understand what’s gone wrong. Do you see any data in output. Maybe your filter array is not working?

@behar450 Жыл бұрын

@@DamoBird365 Can we setup a teams call or something lol. I have been contacting trying to get around so many problems I have with this one project

@daxteoh27 Жыл бұрын

Damo, I have a column in date format, how do I convert it back to date format after i split?

@daxteoh27 Жыл бұрын

my solution now is use apply to each output file and format them to date and it works but I find it inefficient. Not sure if you have any other better way.

@DamoBird365 Жыл бұрын

@@daxteoh27 you could try it in the script? learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.datetimeformatinfo?view=office-scripts otherwise a select is far more efficient that an apply to each kzfaq.info/get/bejne/fZmBZLqmv7q7pY0.html

@jamie0610 Жыл бұрын

@@DamoBird365 Hi Damo, I ended up achieving this by formatting the required columns in the blank excel file before copying the encoding to the flow

@DamoBird365 Жыл бұрын

@@jamie0610 thanks for sharing 👍

@swarpranalimusic6651 9 ай бұрын

In which programming language this script written

@DamoBird365 9 ай бұрын

Typescript, a subset of JavaScript, known as office scripts 😉

@user-pr7bh9sl8n 8 ай бұрын

Damo, this is going to be a game-changer for me. Thank you. I'm running into one error at the second Run Script. The error is.. "We were unable to run the script. Please try again. The script couldn't create a connection with Excel. Please try again. clientRequestId: eceb48d4-ccb5-4bc1-b29a-34b20effe634" Do you have any ideas on what to look for to resolve this?

@DamoBird365 8 ай бұрын

Not sure if I’m honest. Maybe an idea to check the values you are providing as input. I think spaces in the sheet name upsets this example script.

@user-pr7bh9sl8n 8 ай бұрын

@@DamoBird365 I think I resolved that issue. Now I getting "We were unable to run the script. Please try again. Office JS error: Line 16: Workbook addTable: Some header row values have more than 255 characters and will be truncated. Do you want to continue? clientRequestId: 6bbb5693-7496-4ab5-8c91-990bb6af2bfb" Sounds a bit strange because I checked my header row and that's not the case

@chidi-bernard 2 жыл бұрын

I keep getting this error when trying to use the script, kindly assist. . . We were unable to run the script. Please try again. Office JS error: Line 36: Workbook addWorksheet: A resource with the same name or identifier already exists. clientRequestId: ***

@DamoBird365 2 жыл бұрын

Is it possible that you have something like a symbol or maybe even a space in the data? There are rules for sheet names and my script doesn’t validate I’m afraid, so will throw an error.

@chidi-bernard 2 жыл бұрын

@@DamoBird365 Thank you. I sorted it out. Apparently, the script is case sensitive and I had multiple cases in the column I was accessing. It has been fixed.

@DamoBird365 2 жыл бұрын

@@chidi-bernard amazing, thank you for sharing.