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!
