Effortlessly Split Excel Workbooks: Power Automate & Excel Office Scripts Solution
HTML-код
- Опубликовано: 23 июл 2024
- 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 ☕ 
Наука
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!
A great video. Thank you.
This is amazing!
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
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?
Strange one. Presumably you’ve created the script in excel ok?
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!
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 ruclips.net/p/PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM let me know how you get on.
@@DamoBird365 great thanks! i realized that in that video you have already covered it. I was overlooked. :D
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.
Thanks for sharing Constantin. I was having this conversation today and you’ve reconfirmed their experience too. They went with a licensed service account.
Hi, great video. Please How obtain the code in FileContent? How convert blank XlFile in base64?
I have demo'd it in another video but there is an easier way using SharePoint api - check this out here ruclips.net/video/gtlklzi6MDg/видео.html
Is there a way we could keep the formating (colors, fonts, etc) of the main sheet into the copies we're creating?
You could try something like: How to split a workbook into multiple files with PowerAutomate & Office Scripts #PowerAutomate
ruclips.net/video/4ZiMjjV1c3Q/видео.html
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?
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.
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?
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.
@@DamoBird365 I am not sure how to change to array mode
Thank you for your help
@@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.
@@DamoBird365 Working now, Thank you
@@Halaweeg what was the fix?
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
This is one for the forum I think. 👍 but will be possible I am sure.
@@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!
Fantastic 🎉
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?
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?
@@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 😥
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.
@@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.
In the following video Create & Populate Excel File Fast - Office Scripts without an Apply to Each #PowerAutomate #Excel
ruclips.net/video/4g8Lh0gzEnc/видео.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.
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!
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?
@@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?
I see that you've addressed this with another person - I'll review that thread. Thank you!
@@sleepyrick I’m guessing you’ve not setup the action correctly, assuming you’ve not made changes to the original flow or script?
Hi
Can you please also share how to get i.e fetch data from D365 and then start the flow
Am unable to run the script
Runtime error :line 26(canot read properties of undefined(reading 'getRangebetween HeaderAndTotal'
I would recommend you reach out to the community powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums 👍
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?
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.
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!
@@user-ky1vj1ib5p amazing 🤩 thank you for letting me know.
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"
@DamoBird -- can you respond, I have a similar issue.
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
Hi Qina, I’ve replied to you on my blog post.
how to get that file content??? pls help
How can we sent these files through after splitting sheets
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.
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
@@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.
What’s the overall requirement / use case?
@@pimpc142k I am facing the same issue. Can you please explain how you ran in parallel in detail? Thank you
@@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?
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.
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
ruclips.net/video/p26sL3qHmfs/видео.html let me know how you get on.
@@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?
@@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?
@@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 :).
Do you have any videos for power automate management connect?
I don’t I’m afraid. What are you looking to achieve.
@@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
@@pragadeshvaranduraisamy6457 I know the ones you mean, haven’t used them extensively yet. What is it you want to do?
@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
I don’t know to be honest. Look at the history, are you sending anything to the script?
@@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[][];
}
@@DamoBird365 i have data in different sheets, do you have any other solution/script to split the each sheets into individual workbooks?
@@manjupulsar I’ve quite a few videos now, apologies. Do you have a sheet1? Does your data start from a1?
@@DamoBird365 I've Sheet1 and Data start from a1, first row is the header.
This is what I’ve been looking for. Can i download the script. Your site is not working.
Good shout 👍 site is back up - been migrating.
Hi Damo, under apply to each, I do not have as many dynamic values as you do for run script option
You could maybe write an expression? 🤞
@@DamoBird365 could you give an example? I cannot find the run script" result" dynamic value
@@DamoBird365 godsent, it's 3.52am over here, really didn't expect your prompt response
@@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.
@@DamoBird365 @9:44, there are many options for your run script, but for mine, there is only a "body" option
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
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?
@@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
Damo, I have a column in date format, how do I convert it back to date format after i split?
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.
@@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
ruclips.net/video/G3Q1WuZTWuY/видео.html
@@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
@@jamie0610 thanks for sharing 👍
Will it be possible to paste the data from Excel into an existing table with data? 🤔🤔🤔
Do you mean merging sheets or merging workbooks? Both of which would be possible.
What I mean is that when pasting the data, it can be pasted into files that already have previous data instead of new files.
I tried, but all the data ended up stuck in one cell, meaning the data didn't align into the corresponding columns.
In which programming language this script written
Typescript, a subset of JavaScript, known as office scripts 😉
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: ***
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.
@@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.
@@chidi-bernard amazing, thank you for sharing.
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?
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.
@@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