Get Hidden or Visible Google Sheet Tabs with Google Apps Script

Get Hidden or Visible Google Sheet Tabs with Google Apps Script

Based on a viewer’s recent question, here is how you can get a list of all hidden or visible Google Sheet tabs with Google Apps Script.

You can integrate these scripts into your own projects.

The Video

The starter sheet for you to play along:

Get Hidden or Visible Google Sheets Tabs with Google Apps Script – STARTER

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

The Code

In the script above, we use the getSheets() method of the SpreadsheetApp Class. This returns an array of all the sheets in the spreadsheet. From here we need to iterate through each sheet and check to see if the sheet is hidden with the isSheetHidden() method. If it is, we can store the name by accessing the getName() or store the id with getSheetId().

Using Reduce to find the Hidden Sheet

There are a number of ways of iterating through the sheets list but I chose to use the JavaScript reduce method. It allows us to cleanly iterate through each sheet, and store the relevant data in only 4 lines of code.

Reduce takes a callback function that we have generated using the arrow syntax.

For our parameters we have:

  • acc – The accumulator. This starts off with the previous value in the list. For us, this is an array ([]) and if the value is hidden, then we append our accumulator with the name or id of the hidden sheet. Otherwise, we just return our accumulator.
  • sheet – The currently iterated sheet. This is the current sheet that we are iterating on. This will contain the constructor for the sheet an all of its associated methods.

Note at the end of the reduce method you see an empty array before the closure. This is where you can put an initial value for the reduce function to check on its first iteration through the array of sheets. We put an empty array here because we intend to concatenate or join our found hidden sheet data to this empty array.

Why list hidden Google Sheets Tabs?

Finding hidden or visible Google Sheets allow you to, in turn, display or hide these tabs based on any condition that you set.

For example, in my tutorial, Duplicate Filter Views in Selected Google Sheet Tabs with Google Apps Script you could set only the visible sheet tabs to receive filter views and leave all your hidden sheets alone.

Likewise in my tutorial on One Approach to Encourage Users to Run Google Sheet-Bound Apps Script When They First Make a Copy of the File we can simply list all of the hidden filter views and show them once the user has run through the authorisation process of their code.

So, what are you going to use the script for? I’d be interested to hear in the comments below. Besides, you might just inspire someone else.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro.

Got a more specific problem you need help with, but don’t have the time to develop the skills? Make an enquiry on my 'Hire me!' page. I occasionally pick up projects. Alternatively, Fiverr’s your best bet to find a skilled Google Apps Script developer to solve your problem quickly and professionally. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

 

~Yagi

Leave a Reply

%d bloggers like this: