How to split a workbook into multiple files with PowerAutomate & Office Scripts
HTML-код
- Опубликовано: 28 июл 2021
- Learn how to use Office Scripts and Power Automate to split an Excel workbook with multiple worksheets, into multiple workbooks with an individual worksheet.
Do you maintain a workbook with multiple worksheets and want to share an individual worksheet with a colleague? Then try out this solution to automate your process.
Check out pnp.github.io/blog/post/excel...
Download solution from my GitHub github.com/DamoBird365/PowerA... as SplitWorkbookIntoMultipleWorksheets.zip
#PowerAutomate #SplitExcel #Worksheets # Workbooks #OfficeScripts Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕ 
Наука
I built this original solution on OneDrive, the dynamic content for SharePoint is slightly different. Take a look here damobird365.com/images/deletesheetsscriptsharepoint.png for a screenshot of the flow.
Hi, why are the other sheets not deleting? i have separate files created but all the worksheets are still in them. i have double-checked the script and i have it exactly as you show it :(
@@carinaslim2378 have a look at the filter array history. You should have the sheet names as output. If that looks good, check that you haven’t put the array in square brackets twice. Check the history input for the script.
@@DamoBird365 It seems my Filter Array keeps changing the condition. I enter Item is not eq to Current item. but after saving. when i go back into the flow, Filter Array shows the condition as Current Item is not eq to Current Item. I don't know why it keeps changing the condition. i double-checked that I use the File ID and File identifier in all the correct places. any ideas? this is why no worksheets are getting deleted :(
@@carinaslim2378 delete the filter action and re-insert? Very odd behaviour.
@@DamoBird365 i did. i even rewrote a new flow and it's still the same. really odd. thanks for your time! i know this will work if my filter condition stays as i write it!
Just to drop in again to thanks the author for his patience to explain the script with me. Here is what i encountered and the solution that worked for me (Thanks Damien again)
In the "Apply to each" step, the filter array section always changed from "item" to "Current Item"
Solution is to click the Edit in advance mode at bottom left of this step, and paste this "@not(equals(item(), items('Apply_to_each')))"
That may solve your problem, if you are like me and still have another problem on the Run Script to Delete Sheets section, please look further.
If you have problem on the Run script to delete sheets step, try to insert a new step right before that step, and pick the same setup, but right after you pick the Delete Sheets scripts, dont pick the "body" yet, instead, press the little [T] at the top right once, then pick "Body" again.
Dont forget to remove the old step (click the top right of [...] of that step and pick delete)
Hope that helps.
All credits go to Damien :)
Hi This solution helped me to fix the issue, however when i run the script first time flow generate both excel and pdf file and second time it go to infinite loop generates 1000 of same files, is there any solution to fix this problem?
Thank you very much, you really solved my problem. Now I'll finally be able to finish my workflow."
After so much distress over splitting Excel (and then producing PDFs of the splits), it brought tears to my eyes seeing this solution! Thank you so much for your work and sharing the flow with everyone. Cheers!
Thank you so much 😊
While I'm importing the flow I had a thought - how does your flow handle hidden worksheets?
No idea, you’ll have to let us know 👍 you will be able to look at the flow run history.
@@DamoBird365 It fails with hidden worksheets and hidden excel objects, I had to introduce a condition to filter the names of those sheets out of the worksheet array.
That being said, I'm having a lot of issues with getting this solution to work. The excel files that I am using are generated from an ORACLE report, and the delete script works on my smaller test file but fails on my larger file (500+) sheets with various errors.
@@katrinacarlton827 there are definitely limitations within office scripts. 5MB and 5 million cells. docs.microsoft.com/en-us/office/dev/scripts/testing/platform-limits I don’t have access to files of your size but if you want to share them with me and can cleanse them, I could try look for another solution once I know your use case.
Thanks for your great explenation ☺
I am new to the scripts in excel, but could I bend this solution to only take list 6 (bascially a dashboard which has connections to different sheets for graphs etc.- no idea if it would break the data).
Its called Dashboard and I would need to print it into PDF and there are 16 sheets total....
I’m note sure. Worth exploring 👍
Filter arrayed copied exactly but getting "flow save failed with code 'invalidtemplate' and message 'the template validation failed: 'The inputs of template action 'filter_array' at line q and column '2939' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Filter_array' to be referenced by 'repeatitems' or 'items' functions. " also cant find Getworksheeta via script and Deletesheets via script. Only see Runscript. I figured you probably used this and just renamed the steps. However when i use Runscript for the delete sheets part i dont get the 5th field "sheetstodelete for me to enter "body"
The two scripts need to be created by you in excel. I suspect you’ve maybe got an additional apply to each also. Worth discussing on the forum powerusers.microsoft.com/
@DamoBird365 scripts were created prior to creating power automate : GetSheetNames
function main(workbook: ExcelScript.Workbook) {
// Get all the worksheets in the workbook.
let sheets = workbook.getWorksheets();
// Get a list of all the worksheet names.
let names = sheets.map((sheet) => sheet.getName());
return (names)
}
DeleteSheets
function main(workbook: ExcelScript.Workbook,
SheetsToDelete: Array = [],
)
{
// for each of the worksheets in the array
for (let worksheet of SheetsToDelete) {
//delete the worksheet
workbook.getWorksheet(worksheet).delete();
}
//get current worksheets in workbook
let sheets = workbook.getWorksheets();
let names = sheets.map((sheet) => sheet.getName());
//return the number of sheets left
return(names.legth)
}
Thanks it works 🙂 How to get only the sheet names as file name and to have the suffix/prefix of the original file name ?
You’ll need to update the create file action. It uses the sheet names from the office script.
Hello,
thanks for sharing this flow. I have just re-created it and is working but I have got two issues:
1. The PDF files have been created but I can't open them, I've got an error message saying "something went wrong, we can't open this file" while the excel files are ok. Any ideas why?
2. This is more a question than an issue, is there a way to put a condition for filtering only some of the worksheets instead of all?
Thanks a lot!
Hi Claudia, awesome that you’ve got this setup. What is your use case? For 1. Have you got .pdf in the file name for create file? 2. You could use filter array and exclude the sheets by name?
@@DamoBird365 Thank you for your quick reply. 😀
I would like to be able to export some excel worksheet to pdf for my manager at work, but I only need the worksheet with reports, not all of them.
1. yes I can see the file extension is .pdf so I really don't understand what's wrong here. I've also created a very simple excel file just for testing so it should be working ok.
2. I was thinking of using a filer array but I am not quite sure how to do that, I am not an expert. Where should I insert this new filter array?
Thanks
Claudia
@@Claudia-ib5kc I’ve started up a forum on my blog if you want to send me more details or you could try powerusers.microsoft.com/ I would need to know how many sheets and which sheets you want to keep.
hi, i have tried the first script i.e. Split Workbook into Multiple Worksheets Based on Key Column. and my key column is in number format. when i try to run the script, there is error at step no 29 as follows : Line 29: Filter applyValuesFilter: The argument is invalid or missing or has an incorrect format. do you know why?
Hi Bella, try updating the script as follows: TableName.getColumnByName(KeyColumn).getFilter().applyValuesFilter([key.toString()]);
I believe this is because a number is treated as a number and it needs to be a string to search on.
This is great, thank you! Do you happen to have an idea on how to do the opposite of this: insert a new worksheet into an excel workbook using office scripts and power automate (essentially copy and paste between 2 different workbooks)?
It would be possible for sure. But I don’t have a video. Try using the recorder in excel to capture the code for a new sheet and let me know how you get on.
@@DamoBird365 so far I've been able to transfer the data from a source workbook into the target workbook using Power Automate's "add row into a table" action within an "apply to each" loop. As you can imagine, this process can take a long time for worksheets with 700+ rows. Is it possible (either through excelscript or power automate) to import the contents of the source file as a table in the target workbook?
@@demodahm take a look at learn.microsoft.com/en-us/office/dev/scripts/resources/samples/combine-worksheets-into-single-workbook
Thanks Damien!
Is there way to split the excel without deleting sheet.
If there are 10 sheets, then delete script will work 10*10.
This is one way of doing it. Another possibility might be to retrieve the number of sheets with its data and then create 10 new workbooks. One advantage of the delete route is that you maintain the format of the sheets. What are you trying to achieve?
Thanks for the reply@@DamoBird365
In my case there are several files getting created in a SharePoint folder from Power BI through another flow with 100's of tab in each file. Created a conditional trigger for each file using power automate which will use these two office scripts.
Most of my flows were getting failed after running for hours stating, "Out of call volume quota", "Create file failed bcz of Bad Gateway", " run script failed with status 504, for delete worksheet script" few were getting passed.
Please your suggestions
@@nandhukishore6064 that’s massive 😱 you only get 400 api calls per day to office scripts. Will try and think of another way. Are your excel files just data or are their formulas, formatting etc? If you’re able to share a sample, ideas@damobird365.com and I can play along.
@@DamoBird365
Oops... Thanks for sheding some light.
I'm new to office scripts and power automate 😬
There's no Formula but there are some pictorial representations and formatting
is there a way to run this and only return the first sheet or first on one pass? so I only create one xlsx file and only one pdf? I dare say return only the first two sheets... but dont wanna push my luck lol...
I’m sure you could but what are you trying to achieve? Delete everything but the first sheet? Rather than looping through all sheets, just run it on the first() or use take() to take the first 2?
@@DamoBird365 thanks for the quick reply... there are 6 sheets in the workbook 4 are hidden. The other 2 are reports. I want to take the report on Sheet 1 and pdf it and put it in email so it is easy for them to print. And I do not want to have to make 12 files in the process... The 4 files 2 xlsx and 2 pdf I would keep in the split folder as backups so I would not have to delete
I don't see DeleteSheets via Script. The only choice I have is Run Script but I don't see the SheetsToDelete field in Run Script. Any advise you could give would be greatly appreciated.
Hi Emily, you would need to setup those scripts in excel before they are visible in Power Automate. Have you done that?
@@DamoBird365 Hi Damien, thanks for the quick reply. Yes, I did set up GetSheetNames and DeleteSheets scripts in Excel. They are part of the selection under *Script when I selected Run Script action after Create File step. When I ran the flow on a workbook, no tabs were deleted; the same workbook repeated itself many times. I must have done something wrong. Too bad, I could not past my print screen here to show you.
@@emilylee6804 same thing with me. Just run script is available. I actually thought Damien only renamed the step because you can actually rename "run script' and replace with custom name. So I thought Damien actually used run Script 2x and just renamed them.
I have one SharePoint document library where every month one excel file with multiple worksheets get dropped. I want to have those worksheets converted to workbooks inside power automate flow. Is there a way to do that without using scripts?
I’m pretty certain that you could do similar with graph api. My latest video demos graph to populate an excel file
ruclips.net/video/mNwEk7hLdfE/видео.html you could create duplicates of the file and then delete the sheets in each as a loop.
@@DamoBird365 Can I do it without the use of graph Api? The requirement is to have multiple workbooks created from single excel file.
@@panjarimehta5096 you could use native actions but if you’re dealing with large amounts of data and writing it row by row, I would expect it to be unreliable.
this is a great tutorial and i want to apply this with my work. followed it step by step but it seems my delete sheets wont work because the filter is not working properly. When I check the result input, i can see all the worksheets but the output is blank thus probably causing the failure. When I was trying to isolate the filter, i observe that "Item" keeps becoming "current item" so it becomes "current item" is not equal to "current item". Any idea where is my mistake? thanks
Hi Joseph, not 100% sure to be honest. Try re-creating the filter? I've just uploaded the flow and scripts to my GitHub. Take a look here github.com/DamoBird365/PowerAutomate/blob/main/RUclipsDemos/SplitWorkbookIntoMultipleWorksheets.zip
@@DamoBird365 it's working now. thank you so much
@@DamoBird365 Thanks for the content, great video, a new subscriber here. Happy to have your video recommended to me, unfortunately, i am stuck at the same step, everything looks the same, but the "item" keep changing to "Current item", i dont know how to import the scripts (although i downloaded it and input it manually, and looks identical), any thought would be appreciated.
Some info to add, i could spilt the file, it is not deleting the sheets. so i got a duplicated file with different name ("Current item" + "File name"), but the content are the same.
@@yiubili1 feel free to email me where you are at with a screenshot of the flow and error. Ideas@damobird365.com
Did you solve this? I also have the same problem
Edit : nvm solved it. I just write the @not(equals(item(), items('Apply_to_each'))) in advance mode. It works like intended
hey don't know but I am trying all for two days and I am not sure what the issue with script I am getting error can u please help me for this "The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Get_worksheet_via_script')' is of type 'Object'. The result must be a valid array." please suggest me way out.
The input value you are providing to the apply to each is an object{} and not and Array[]. Check the output of the run script and then write an expression to get the array[].
@@DamoBird365 thank you for the replay but as i can check i am getting array valuese from script at the filter i am not getting Body/result value any suggetion for that
Great idea! The flow works but all my data is linked to other sheets and gives me #REF for invalid data link
were you able to find a solution for this?
Hey, I tried creating this flow and am stuck on the "delete sheets" script. I get an error that the "Delete" operation on row 9 cannot be completed. "This operation is not permitted for the current object when running office script"
Any ideas on how I can fix? I have access to the files, I've tried creating them on Onedrive and on Sharepoint and run into the same barrier. Tried googling the error and could not find any helpful posts or solutions.
Check the history of a flow run, specifically the filter array. Are you getting the sheet names, with one removed for each loop?
@@DamoBird365 That was extremely helpful. thank you. problem solved
@@jonsandler2878 what was the eventual problem?
@@DamoBird365 That was it, the filter array was not filtering properly, so deleting all sheets is not a valid action, therefore action was not permitted. After fixing the filter array, that solved the problem.
Hi, after splitting workbooks into separate file, can we convert that files into CSV files?
Definitely possible. But you possibly don't need office scripts for that. You just want to get the data back from excel, split the array and then write the data to csv(s). If your excel file has a table, you can use the native active to get rows, otherwise graph api or office scripts will let you get the data back into Power Automate. Here's an example of creating filtered HTML tables from Excel ruclips.net/video/pwJ73jFEtDQ/видео.html
@@DamoBird365 Thanks. But you mentioned to use split the array. Kindly help me with that?
You would use select and filter actions to do this, as seen in the video I linked you to.
@@DamoBird365 My excel file don't have tables can i convert it into csv?
@@srinivasmanohar7146 you would need to use office scripts of graph api to access the data if the excel file doesn’t have a table. I’ve videos on both 👍
Thanks for the solution.
Any idea about splitting the Excel file with row count. Just to brief it if I have excel containing 4 lakh records and i need to split excel data into multiple file with rows of 20k in each file. Can you help me with office script for this scenario
Why not split the data in Power Automate using Chunk() and then create new files for each?
@@DamoBird365 I am stuck with this from past two days to get the entire excel file data into array format which is accecible by power Automate.
I am trying office script and failing to do it
Can you please help me with this scenario.
@@DamoBird365 If I try to read excel data with List rows present in a table with 4 lakhs rows then it is taking long time n sometimes getting timeout error.
Any way to read excel large data and make it available for power Automate cloud.
@@DamoBird365 To be honest I didn't expect reply from you as you will be busy at your work. I m glad that you replied. I really hope I get the solution for my usecase. 😊
@@user-ed2qp2ur1g 400,000 rows? I was going to suggest office scripts to read the data. It does have limits too. learn.microsoft.com/en-us/office/dev/scripts/testing/platform-limits#data-limits
Given the popularity - why is this not an available Template in PA
Do you mean the official templates with Microsoft? I don’t believe that I can publish office scripts, so only the flow would be available. I guess you like the idea? The solution is available on my GitHub via a link in description.
Thank you for this incredible solution! My flow is almost up and running, but the DeleteWorksheets script keep giving me the following error.
"We were unable to run the script. Please try again.
The script couldn't create a connection with Excel. Please try again."
Any ideas?
Might be the file id? Are you using a dynamic value?
@@DamoBird365 I am using the dynamic value Id (from the Create File within the Apply to each container). I tweaked my input values based on some of the comments here and am now getting the same code error as Mike Dodds below. (We were unable to run the script. Please try again.
Runtime error: Line 8: Cannot read property 'delete' of undefined). Do you know if he ever found a solution to that error?
It’s a tricky one to debug without seeing your flow. One common error I’ve seen is extra [] on the array supplied to the script. You can see that by looking at the output of the error history. Might be worth dropping me a screenshot of the output. Ideas@damobird365.com
@@DamoBird365 I have sent the info to the email address above. Thank you!
@@trentbrewer3336 try file identifier for the script action instead of id. I think the difference between OneDrive and SharePoint catches folk out.
I have had 2 issues with this flow. 1) Filter Array switching to current Item instead of item. This resulted in giving me a blank array as the output and the flow did not run. The solution I found for this is using this code in quotes - "@not(equals(item(), items('Apply_to_each')))" in the edit in advanced mode. Now that my array is being filtered out correctly, every time, like it does in the video, the next issue I am running into is this error: "Runtime error: Line 6: Cannot read properties of undefined (reading 'delete')
clientRequestId: 28e848a9-1cf0-47e5-9bf3-28d6264fef8b" ... This is the error that is haunting me for 2 days now. I have tried ChatGPT to help me with the error. but I am reaching nowhere. I would appreciate your help here Damo.
Have you posted it on the forum with a screenshot? Ultimately the history will give the game away but maybe there is a problem with the script?
@@DamoBird365 Thanks for the prompt reply. looking at the error it looks like the issue is with the office script, but I am not sure why. I have the same code copied over.
@@DamoBird365 I am not sure which forum I need to post the screenshot to, or what do you mean by history will give the game away.
@@devanshuchauhan4246 here’s the forum powerusers.microsoft.com/. Sharing images will help diagnose as it’s not always easy to diagnose via comments alone.
@@DamoBird365 While my flow is running well, I am now facing a new challenge. When I split the files, I am not able to retain the values that come by using formulas/data from other sheets. How do I change the data in the sheets as values instead of formulas, to avoid the REF! error?
This is amazing, but I've spent my whole day trying this from a million different perspectives, and on the "Delete Sheets Script" at the end, I get this:
We were unable to run the script. Please try again.
Runtime error: Line 9: Cannot read property 'delete' of undefined clientRequestId: 9c05691e-1c13-4213-83d5-df60600aaff6
I'm sure I have copied you script properly. It all works perfectly, right up to that point. Any ideas? I'm very new to the Excel scripts, so my troubleshooting is limited.
Have you tried downloading the code from github.com/DamoBird365/PowerAutomate/blob/main/RUclipsDemos/SplitWorkbookIntoMultipleWorksheets.zip ? I guess line 9 of the script references something that hasn't been defined but it is hard without seeing the code. Maybe a typo if you created it yourself? Maybe not?
@@DamoBird365 Wow, quick reply, thank you. I have downloaded the files, but I'm not sure how to open or use an 'osts' file? I have copied it off the screen and looked at it 100 times, so I might be blind to the error now.
@@DamoBird365 and sorry... the code for "DeleteSheets" as I have it:
function main(workbook: ExcelScript.Workbook,
SheetsToDelete: Array = [],
)
{
//for each of the worksheets in the array
for (let worksheet of SheetsToDelete) {
//delete the worksheet
workbook.getWorksheet(worksheet).delete();
}
//get current worksheets in workbook
let sheets = workbook.getWorksheets();
let names = sheets.map((sheet) => sheet.getName());
//return the number of sheets left
return (names.length)
}
I have copied your code into a new script and it runs fine. For example, create a 2nd Sheet, called Sheet2 and run the function with the default value of:
SheetsToDelete: Array = ["Sheet2"]
You can test from Excel.
When you get an error, is it from PowerAutomate? How are you passing the array? Can you look at the history for that action and check the input?
@@DamoBird365 Thanks again for the fast reply. The "Sheet2" worked for me also, so it appears the script is fine.
I'm calling from and to SharePoint files, instead of OneDrive, but I don't think that will make a difference, especially since the Get Sheets script runs with no issues.
Trying to run it through OneDrive, but need to wait over 2 hours to test again: "Out of call volume quota. Quota will be replenished in 02:15:04."
The error is in the last step of Power Automate (I didn't need the PDF part, so I've removed the last 2 steps).
There are about 25 tabs on the spreadsheet, but even with a small test document it fails. The array is inputting all tabs and outputting all but one, which cycles as I flick through. I've made sure all actions match your all the way through.
I guess I can wait 2 hours and see if OneDrive works then - if so, I'll make minor adjustments until it breaks again so I can find out why.