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.
Table of Contents
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
/** * @OnlyCurrentDoc */ /** * Get a list of hidden or visible sheets. * [This is an example]{@link https://yagisanatode.com} * @author Scott (Yagi) <yagisanatode@gmail.com> * @license Attribution-NonCommercial 4.0 International (CC BY-NC 4.0) */ function getHiddenSheets() { // Variables. const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheets = ss.getSheets(); // Hidden Sheet list const hiddenSheets_byName = sheets.reduce((acc, sheet) => { return (sheet.isSheetHidden())? acc.concat(sheet.getSheetName()) : acc },[]); console.log(hiddenSheets_byName) const hiddenSheets_byId = sheets.reduce((acc, sheet) => { return (sheet.isSheetHidden())? acc.concat(sheet.getSheetId()) : acc },[]); console.log(hiddenSheets_byId) const hiddenSheets_byNameAndId = sheets.reduce((acc, sheet) => { return (sheet.isSheetHidden())? acc.concat([[sheet.getName(), sheet.getSheetId()]]) : acc },[]); console.log(hiddenSheets_byNameAndId) }; function getVisibleSheets() { // Variables. const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheets = ss.getSheets(); // Visible sheet list. const visibleSheets_byName = sheets.reduce((acc, sheet) => { return (!sheet.isSheetHidden())? acc.concat(sheet.getSheetName()) : acc },[]); console.log(visibleSheets_byName) const visibleSheets_byId = sheets.reduce((acc, sheet) => { return (!sheet.isSheetHidden())? acc.concat(sheet.getSheetId()) : acc },[]); console.log(visibleSheets_byId) const visibleSheets_byNameAndId = sheets.reduce((acc, sheet) => { return (!sheet.isSheetHidden())? acc.concat([[sheet.getName(), sheet.getSheetId()]]) : acc },[]); console.log(visibleSheets_byNameAndId) }; |
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.
Need help with Google Workspace development?
Go something to solve bigger than Chat GPT?
I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I 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