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.

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 which may be useful to you are:

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: