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 to copy the sheet to the same spreadsheet, just input your current spreadsheet’s ID when prompted. The spreadsheet id can be found in 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.

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

View the Google Spreadsheet Demo

[youtube https://www.youtube.com/watch?v=_bHih4qKk5Y]

[ultx]

Select an option 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. Numbers 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.

[/ultx]

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