Office Scripts – Hide all sheets except one

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
• Adapting the script
• Change the worksheet
• Change between hidden and very hidden
• Reusable Office Script for Power Automate
• Notes

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:

• Office Scripts worksheets guide
• VBA to hide all sheets except one

---

What next?

Discover how you can automate your work with our Excel courses and tools.