Effortlessly Unhide and Delete Hidden Sheets in Google Sheets All at Once!

In managing weekly reports and other data in Google Sheets, do you find that the number of hidden sheets keeps growing?

Hiding sheets can be done with multiple selections, but what is troublesome is when you want to re-hide or delete sheets that have been hidden together.

In the case of Google Sheets, it is time-consuming to manually redisplay or delete them one by one.

Google Apps Script is useful in such cases.

Google Apps Script allows you to re-display or delete hidden sheets at once.

Google Apps Script may sound difficult, but you can use it by copying the script, so you do not have to write it yourself.

This article will show you how to take advantage of Google Apps Script to re-display hidden sheets together and how to delete them.

Script to reｰdisplay hidden sheets in bulk

Script to redisplay hidden sheets in bulk

function showAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();  // Get the currently open spreadsheet
  var sheets = spreadsheet.getSheets();  // Get the list of sheets
  
  // Check all sheets
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    if (sheet.isSheetHidden()) {  // If the sheet is hidden
      sheet.showSheet();  // Unhide the sheet
    }
  }
}

There is a copy button in the upper right corner of the code block (black screen).

After script execution

All sheets that were hidden will be re-displayed.

If there are only a few hidden sheets, it will not take long to re-display them one by one, but if there are a large number of sheets, Google Apps Script can save time.

Script to delete all hidden sheets in bulk

Script to delete all hidden sheets in bulk

function deleteHiddenSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();  // Get the currently open spreadsheet
  var sheets = spreadsheet.getSheets();  // Get the list of sheets
  
  for (var i = sheets.length - 1; i >= 0; i--) {  // Loop through all sheets from the last to the first
    var sheet = sheets[i];
    if (sheet.isSheetHidden()) {  // If the sheet is hidden
      spreadsheet.deleteSheet(sheet);  // Delete the hidden sheet
    }
  }
}

There is a copy button in the upper right corner of the code block (black screen).

It is recommended that you back up your files before deleting them.

A script to delete hidden sheets is useful in this situation.

• You no longer need the sheets that have been hidden
• You want to organize because the file size is full and you can no longer copy sheets

How to use the script

STEP

Open the Apps Script Editor

Open Google Sheets, then click on “Extensions” in the menu and select “Apps Script” to open the editor.

STEP

Create the Script

function myFunction(){

}

is included from the beginning, so delete it and paste the previous script.

STEP

Save the Script

STEP

Run the Script

STEP

Completion

When the execution is completed, it is OK.

Verify that hidden sheets have been re-displayed or deleted.

If you’re running the script for the first time, you need to authorize it.

Therefore, press “Review Permissions.“

Detailed Authorization Steps

Press “Advanced.”

Press “Go to Untitled project (Unsafe).”

After that, press “Allow.”

Conclusion

Script to redisplay hidden sheets in bulk

function showAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();  // Get the currently open spreadsheet
  var sheets = spreadsheet.getSheets();  // Get the list of sheets
  
  // Check all sheets
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    if (sheet.isSheetHidden()) {  // If the sheet is hidden
      sheet.showSheet();  // Unhide the sheet
    }
  }
}

Script to delete all hidden sheets in bulk

function deleteHiddenSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();  // Get the currently open spreadsheet
  var sheets = spreadsheet.getSheets();  // Get the list of sheets
  
  for (var i = sheets.length - 1; i >= 0; i--) {  // Loop through all sheets from the last to the first
    var sheet = sheets[i];
    if (sheet.isSheetHidden()) {  // If the sheet is hidden
      spreadsheet.deleteSheet(sheet);  // Delete the hidden sheet
    }
  }
}

The script introduced here is useful when you have a large number of hidden sheets.

Save time by letting Google Apps Script take care of the tedious work.

Our company offers support for improving work efficiency through the use of Google Apps Script.
If you need assistance with Google Apps Script customization or error resolution, please feel free to contact us.
We are fully committed to supporting your business improvements.

Contact us here