In this post, we’ll cover the Office Script you can use to hide all sheets except one. I’ll share the basic script, along with the options to change the code to your requirements.
Since Power Automate can use Office Scripts, I will also share the reusable code block to use with dynamic flows.
Table of Contents
Example Office Script
Enter the following code into the script editor:
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 = workbook.getActiveWorksheet().getName(); //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.setVisibility(ExcelScript.SheetVisibility.hidden); }; }); }
In the sections below, I will show how to modify this script to meet your specific scenario.
Adapting the script
The example above is the Office Script to hide all sheets except the active sheet. There are 2 different settings that we can change:
- The worksheet to keep visible
- Making the worksheet hidden or very hidden
Let’s take a look at these two options.
Change the worksheet
To refer to a specific worksheet, we can use one of the following lines of code. The example above uses the active sheet method, but that line of code can be replaced to refer to use the named sheet or by sheet position methods.
Active sheet:
let wsKeepName = workbook.getActiveWorksheet().getName();
Named sheet:
let wsKeepName = "MySheetName"
Replace MySheetName with the name of your sheet.
Nth sheet position:
let wsKeepName = workbook.getWorksheets()[1].getName();
The number in the square brackets refers to the sheet position. Since Office Scripts have a zero base, 0 is the first sheet, 1 is the second sheet, etc.
Change between hidden and very hidden
The example script hides worksheets, which can be made visible by the user with the standard Excel user interface. Instead, we can make sheets very hidden. These are not displayed in the hidden sheet list; they require more advanced methods to be visible again.
Make sheets hidden
ws.setVisibility(ExcelScript.SheetVisibility.hidden);
Make sheets very hidden
ws.setVisibility(ExcelScript.SheetVisibility.veryHidden);
Reusable Office Script for Power Automate
If running an Office Script within Power Automate, we can either write a specific script similar to that shown above or have a reusable dynamic code.
For a dynamic script, the parameters are passed into the script during the flow. This means we only need one code block, which can be called over and over again, but with different parameters.
In the script below, wsKeepName and visibilityType are the parameters which must be passed during the flow.
function main(workbook: ExcelScript.Workbook, wsKeepName: string, visibilityType: string) { //Assign worksheet collection to a variable let wsArr = workbook.getWorksheets(); //Create variable to hold visibility type let visibilityTypeScript: ExcelScript.SheetVisibility; //Loop through all worksheets in the WSArr worksheet collection wsArr.forEach(ws => { //if the worksheet in the loop is not the worksheet to retain if (ws.getName() != wsKeepName) { //Use switch statement to select the visibility type switch(visibilityType) { case "hidden": visibilityTypeScript = ExcelScript.SheetVisibility.hidden; case "veryHidden": visibilityTypeScript = ExcelScript.SheetVisibility.veryHidden; } //Make the worksheet hidden ws.setVisibility(visibilityTypeScript); }; }); }
To use the script above with Power Automate, the step will look similar to the following:
The Script Parameters passed during the flow are:
- wsKeepName: The name of the worksheet to be retained
- visibilityType: The text string hidden or veryHidden.
Notes
A few things to take into consideration are:
- There needs to be at least one visible sheet. If we try to hide the last visible sheet it will cause an error in both the script editor and Power Automate.
- Before running this script, we may need to make all sheets visible with another script. This ensures the sheet to be hidden is visible before executing this script.
- From an Office Scripts perspective, getWorksheets() does not include chart sheets. Therefore, after running the code in this post, chart sheets will remain visible.
Related pages:
Discover how you can automate your work with our Excel courses and tools.
The Excel Academy
Make working late a thing of the past.
The Excel Academy is Excel training for professionals who want to save time.
Incredibly helpful. Thank you!
You’re welcome. 😀