how to delete multiple sheets in google sheets

2 min read 08-10-2024

How to Delete Multiple Sheets in Google Sheets: A Step-by-Step Guide

Working with large Google Sheets can sometimes mean managing too many sheets. Deleting multiple sheets at once can save you time and keep your spreadsheet organized. Here's a breakdown of how to do it, along with some tips and tricks:

The Easy Way: Using the Right-Click Menu

Select the Sheets: Click on the tab name of the first sheet you want to delete, hold down the Shift key, and then click on the last sheet you want to delete. This will select all sheets between the first and last sheet.
Right-Click and Delete: Right-click on any of the selected tabs and choose "Delete sheet" from the context menu.

Note: This method allows you to select and delete sheets in any order, not just consecutive ones.

The Powerful Way: Using Script Editor

For more advanced users and when you need to delete sheets based on specific conditions, using Google Apps Script offers more flexibility:

Open Script Editor: Go to Tools > Script editor in your Google Sheet.
Paste the Code: Copy and paste the following code into the script editor:

function deleteMultipleSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetsToDelete = ["Sheet1", "Sheet2"]; // Replace with the actual sheet names you want to delete
  
  for (var i = 0; i < sheetsToDelete.length; i++) {
    var sheet = ss.getSheetByName(sheetsToDelete[i]);
    if (sheet != null) {
      ss.deleteSheet(sheet);
    }
  }
}

Edit the Sheet Names: Replace "Sheet1" and "Sheet2" with the actual names of the sheets you want to delete.
Run the Script: Click on the "Run" button and select deleteMultipleSheets from the dropdown.
Authorize Access: Google Sheets may ask you to authorize the script to access your spreadsheet. Click "Allow" to proceed.

Example:

Let's say you have several sheets with names ending in "Draft". This script will delete all sheets containing "Draft" in their name:

function deleteDraftSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  
  for (var i = 0; i < sheets.length; i++) {
    var sheetName = sheets[i].getName();
    if (sheetName.includes("Draft")) {
      ss.deleteSheet(sheets[i]);
    }
  }
}

Important Note: Always backup your spreadsheet before running any scripts to avoid accidental data loss.

Beyond the Basics: Advanced Techniques

Conditional Deletion: Using the script editor, you can create more complex deletion logic based on sheet content, date, or other criteria. For example, you can delete sheets based on specific values in a certain cell or sheets that haven't been edited in a certain time period.
Bulk Deletion: Using Google Sheets API, you can programmatically delete multiple sheets across multiple spreadsheets. This is useful for managing a large number of spreadsheets within an organization.

Source:

Right-Click Deletion: This technique is commonly shared in Google Sheets forums and help articles, with no specific author attribution.
Script Editor Deletion: This technique is also widely documented in Google Sheets help resources.

By using these methods, you can streamline your spreadsheet management, deleting unnecessary sheets and maintaining a clean and organized workspace.

how to delete multiple sheets in google sheets