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().

Hire me for our next Google Workspace project.

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.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

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.

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

 

~Yagi

Leave a Reply