Bulk Hide, Show, Delete & Copy Multiple Sheets in Google Spreadsheets

Update: This script now allows you to copy multiple sheets within one spreadsheet to either the same spreadsheet or a different one. If you want copy the sheet to the same spreadsheet just input you current spreadsheet’s id when prompted. The spreadsheet id can be found in the the Spreadsheet’s URL eg: docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0

If you are looking for a way to copy sheets from a template, check out the post on GoogleAppScripting.com

Too many sheets! I came across the problem of having a large number of visible or unused sheets in cluttering up my Google Spreadsheets  often enough that I decided to solve it. This problem most often comes up when using Google Apps Scripts that auto-generate multiple sheets. If you have ever used the Google Analytics Google Spreadsheet Add-On, you know what I mean.

Too many sheets for one Google Spreadsheets

Using the Script:

Copy the code below into a script file in for the Google Spreadsheet you want to use it on, save the script file and reload the spreadsheet. A new menu will appear called “Spreadsheet Cleanup.” This menu has three options:

Hide Sheets, Show Sheets, Delete Sheets

Google Spreadsheets UI Menu

View the Google Spreadsheet Demo

Select an option and and the spreadsheet will prompt you to enter a word or phrase to match the names of the spreadsheets you would like to take action on against. In other words… enter part of the name of the sheets that you would like to take action on into the box and then it will do the job pretty quickly.  Note that you can only use letters in the name. Number will not work. Feel free to edit the code though.  (At first I was using the Javascript match method to search for sheet names but the regex would not allow for the prompt box.) Anyway, that’s all you have to do.


Hope you can find it useful too!

You can also make a copy of the script here: http://tfox.us/gscript-on-github

Do not copy from the script below. Copy from the Github Repo link.

The HTML formatting will break the Apps Script code.

The Google App Script:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetsCount = ss.getNumSheets();
var sheets = ss.getSheets();

function onOpen() { 
 // Try New Google Sheets method
  try{
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('Spreadsheet Cleanup')
    .addItem('Show Sheets', 'showSheets')
    .addItem('Hide Sheets', 'hideSheets')
    .addItem('Delete Sheets', 'deleteSheets')
    .addItem('Copy Sheets', 'copySheets') 
    .addToUi(); 
  } 
 
  // Log the error
  catch (e){Logger.log(e)}
 
  // Use old Google Spreadsheet method
  finally{
    var items = [
      {name: 'Hide Sheets', functionName: 'hideSheets'},
      {name: 'Show Sheets', functionName: 'showSheets'},
      {name: 'Delete Sheets', functionName: 'deleteSheets'},
      {name: 'Copy Sheets', functionName: 'copySheets'},
    ];
   ss.addMenu('Spreadsheet Cleanup', items);
  }
}
 
function deleteSheets() {
  var deleteSheetsContaining = Browser.inputBox("Delete sheets with names containing:"); 
  if (sheetMatch(deleteSheetsContaining)){
    for (var i = 0; i < sheetsCount; i++){
      var sheet = sheets[i]; 
      var sheetName = sheet.getName();
      Logger.log(sheetName);
      if (sheetName.indexOf(deleteSheetsContaining.toString()) !== -1){
        Logger.log("DELETE!");
        ss.deleteSheet(sheet);
      }
    } 
  } else {
     noMatchAlert();
  }
}

function hideSheets() {
  var hideSheetsContaining = Browser.inputBox("Hide sheets with names containing:");
  if (sheetMatch(hideSheetsContaining)){
    for (var i = 0; i < sheetsCount; i++){
      var sheet = sheets[i]; 
      var sheetName = sheet.getName();
      Logger.log(sheetName); 
      if (sheetName.indexOf(hideSheetsContaining.toString()) !== -1){
        Logger.log("HIDE!");
        sheet.hideSheet();
      }
    }
  } else { 
    noMatchAlert();
  }
}

function showSheets() {
  var showSheetsContaining = Browser.inputBox("Show sheets with names containing:"); 
  if (sheetMatch(showSheetsContaining)){
    for (var i = 0; i < sheetsCount; i++){
      var sheet = sheets[i]; 
      var sheetName = sheet.getName();
      Logger.log(sheetName); 
      if (sheetName.indexOf(showSheetsContaining.toString()) !== -1){
        Logger.log("SHOW!");
        sheet.showSheet();
      }
    } 
  } else {
    noMatchAlert();
  }
}

function copySheets() {
  var copySheetsContaining = Browser.inputBox("Copy sheets with names containing:");
  var destinationId = Browser.inputBox("Enter the destination spreadsheet ID:");
  if (sheetMatch(copySheetsContaining)){
    for (var i = 0; i < sheetsCount; i++){
    var sheet = sheets[i]; 
    var sheetName = sheet.getName();
    Logger.log(sheetName); 
    if (sheetName.indexOf(copySheetsContaining.toString()) !== -1){
      Logger.log("COPY!");
      var destination = SpreadsheetApp.openById(destinationId);
      sheet.copyTo(destination);
    }
  }
   successAlert('copied')
  } else {
    noMatchAlert();
  }
}

// determine if any sheets match the user input
function sheetMatch(sheetMatch){
  for (var i = 0; i < sheetsCount; i++){
    var sheetName = sheets[i].getName(); 
    if (sheetName.indexOf(sheetMatch.toString()) !== -1){
      return true
    }
  }
  return false
}

// alert if no sheets matched the user input
function noMatchAlert() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
   'No Sheets Matched Your Input',
   "Try again and make sure you aren't using quotes.",
   ui.ButtonSet.OK);
}

// alert after succesful action (only used in copy)
function successAlert(action) {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
   'Success!',
   "You're sheets were " + action + " successfully.",
   ui.ButtonSet.OK);
}

If you love Google Spreadsheets, level up your data visualizations with Sparklines. If you are learning how to use Google Apps Script code., checkout out some useful resources on how to learn javascript for Google Apps Script.

31 Comments

  1. Quinn

    Hi Trevor. Thanks for the start of a handy script. I tend to start a new spreadsheet file for collections of activity sheets each year. They are formatted and have several linked scripts, so duplicating last years’ files and deleting the old sheets is my best method of reusing them for the new year. Bulk removing the old sheets is really handy.

    I noticed a minor line in your script you may want to change. indexOf() returns the character number for which the substring is matched, starting at 0. Your condition (!sheetName.indexOf(showSheetsContaining)) is the same as (sheetName.indexOf(showSheetsContaining) == 0), which means that it is true only when matching at the start of the string. This is true whether the user input is a number, character, or even a space. If you want to match the user substring anywhere in the sheet name, use (sheetName.indexOf(showSheetsContaining) >= 0).

    1. trevor

      Quinn, absolutely right! I think was probably trying to be too clever with js truthy/falsy. Glad you noticed and thanks for the comment.

  2. Pingback: $10k Technical Skills Part 1 | The Front End: JavaScript, HTML and CSS

  3. Adip Nayak

    Hey,

    thank you firstly, but could you help me out. the script doesn’t seem to work. I have sheets with prefix “XX”. Hence when i try to show sheets and type in “XX” nothing happens.

    Help pls 🙁

  4. Joel

    Great. Thanks for this. It’s not for everyone but when I accidentally made a script that created 1800+ copies of a single tab, this was a lifesaver for me!

  5. Mark

    I encourage … no, implore you to publish this as an add-on. With multiple sheets in multiple spreadsheets to remove, adding the script manually for each spreadsheet is still a time sink. :*(

  6. Tovly Deutsch

    I’ve developed a Google Add-on called Bulk Sheet Manager that allows you to perform actions on many sheets quickly. It provides you with a neat list of all your sheets where you can easily select many sheets at once using shift click and the “select all” button. Then just hit one of the action buttons to delete, protect, or hide all of those sheets at once. It’s very fast and easy to use.

  7. Muzz

    When I cut & paste the code, I get this error message:

    Missing ) after for-loop control. (line 30, file “Code”)

    1. Trevor Fox

      Hi Muzz, this is strange. I cannot replicate the error and don’t notice any missing parenthesis. Please let me know if this problem persists.

      (You would have that I copied that out of the IT Person’s Guide to Customer Service 😉 )

        1. Edgar

          I’m really going through the same process as Muzz,
          in the demo, there is no Cleanup button and I dont have permissions to duplicate the sheets.

          1. Trevor Fox

            Hi Edgar,

            Make sure you are copying the code from this Github repo: http://tfox.us/gscript-on-github. I think that the problem you may be facing is due to copying the code from the page which likely leaves you with a bunch additional HTML code from the code display window in the post. Less than (<) symbols are turned into (& lt;) which will break the code. Hope that helps.

  8. Jason

    This worked beautifully!! So glad I found this, we make our online schedule for multiple crews with sheets and the pages went back a full year and so tedious to delete each one!!

    1. Trevor Fox

      Hi Simona,

      It seems like copying the sheet would be the simplest way to do this but I am assuming you would want to copy a few cells to several existing sheets. Copying cells is possible but it would be a little bit more case-specific. If I have some time I can set that up. If it’s urgent, email me at t@tfox.us.

    1. Trevor Fox

      I’m glad to hear it! Jeosen, I am planning to start a site that is all about Google Apps Script. Could you tell me what you use Google Spreadsheets/ Google Apps Script for and if you haven anything you want to learn? Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *