Solved: Copy one excel sheet to a new file

[skanord]

I was searching for a way to copy just one excel sheet from one sharepoint folder, and save it in another, but did not find any solutions. I have now solved it, and thought i would share my solutions to other in the same situation. 

I have an excel file saved in a sharepoint folder, and i want to make the "Main sheet" available to other teams, but not the rest of the sheets. To do this I save a copy of the excel file in the desired destination folder, and then run a script to delete the unwanted sheets. (Bear in mind that these sheets don't contain any sensitive information, they just need to be removed because they are irrelevant to the recipients.) The file is then overwritten every night to keep the data up to date. 

Here is my flow:

The script i run is an Office Script made in the destination excel file. Here is the script:

function main(workbook: ExcelScript.Workbook) {

	//Assign worksheet collection to a variable
	let wsArr = workbook.getWorksheets();

	//Create a variable to hold the worksheet name to retain
	let wsKeepName = "Main sheet"

	//Loop through all worksheets in the WSArr worksheet collection
	wsArr.forEach(ws => {

		//if the worksheet in the loop is not the worksheet to retain then
		if (ws.getName() != wsKeepName) {

			//Make the worksheet hidden
			ws.delete();

		};

	});

}

I created the script using this article:

https://exceloffthegrid.com/office-scripts-hide-all-sheets-except-one/ 

I hope this helps someone!

[Bobstar]

Hey, did you manage to solve this? I have the exact same issue.

I want to be able to get 1 worksheet from an excel file that lives on sharepoint, and copy that into a new excel workbook (and into a different sharepoint folder).

[theflyingjapman]

Hi skanor, any chances you can help me with my query?

Link: https://powerusers.microsoft.com/t5/Building-Flows/Save-One-Worksheet-Only-to-PFD-w-o-API-Premium/m-...

I was looking for other options to convert single sheet to PDF w/o using Cloudmersive. Stumbling to your post, I was thinking if it is possible to do the flow you made, then convert that to PDF and save to OneDrive, and delete the excel sheet created (just for the sake of converting that sheet to PDF).

The sheet is an excel dashboard with real time values and want to capture that dashboard on a scheduled trigger.

[parkepa]

Thanks for sharing! Unfortunately my company disabled Office Script citing security concerns, so the only options I am left with are VBA and Power Automate Desktop. Your post reinforces my thought of using a macro to achieve this. Power Automate Desktop does not offer triggering events such as scheduling or when a file is modified, so I'll have to also use Power Automate to start the Power Automate Desktop flow.

[NickMerrick]

just hit record macro, select all cells in the sheet, copy and paste as values. Then hit end recording, rename the macro to flatten and run it from the flow. should flatten all formulas into results

[Createit31]

hi @skanord , i know its been a while since this was posted but wanted to know if i used this flow but i had sensitive information i wanted to delete, what stops someone from clicking 'version history' on the file and seeing what i deleted?

[NickMerrick]

Copy the file and delete the original.