Google-sheets – Delete multiple sheet tabs in Google Sheets

google sheetsgoogle-apps-script

In a Google Sheets spreadsheet (Excel terminology = workbook) is it possible to delete multiple tabs (Excel terminology = worksheet) in one go without having to right click each one and delete it.

Best Answer

this deletes all sheets except Sheet1, Sheet2 and Sheet3

function DELETESHEETS() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (i = 0; i < sheets.length; i++) {
     switch(sheets[i].getSheetName()) {
     case "Sheet1":
     case "Sheet2":
     case "Sheet3":
     break;
     default:
     ss.deleteSheet(sheets[i]);}}}

Related Solutions

Google-sheets – Does Google Sheets have sheet-level named ranges

Google Sheets does indeed have Sheet-level named ranges internally, and they are almost fully functional. :-)

The challenge is that as of 16/2/16 they can only be created by Copying a Sheet with an existing NamedRange and then Google Sheets create the parallel sheet-level named Ranges (in a similar way that excel does when you copy a worksheet containing Named Ranges).

Unfortunately you cannot create them through the Named Ranges window or corresponding Script function.

However, once you have created the sheet-level named ranges, they perform exactly as you would expect, being local to the page, or you can use the Global (Workbook) version (where a specific sheet does not have an overriding sheet-level range).

I have created a Google Sheet that shows how you can use Sheet-level named-Ranges both in formulas and Google Script. You can see it here.

I have also posted a more detailed answer in Google Forums.

Google Sheets – Adding Cells from Multiple Sheets into a Master Sheet

you need to use =IMPORTRANGE for that... 20 times and wrap it in =SUM

this is how =IMPORTRANGE works:

=IMPORTRANGE("1W8yfNmNBBZuEKqk56k-ZxaKrLOiGOBASrkRCQNVxOSA"; "Sheet1!A1")

where the "code" from first quotes is ID of the spreadsheet (which can be found in URL of the particular spreadsheet) and 2nd phrase in the quotes is a combination of sheet name and cell reference/range.

the ID 1W8yfNmNBBZuEKqk56k-ZxaKrLOiGOBASrkRCQNVxOSA is from: https://docs.google.com/spreadsheets/d/1W8yfNmNBBZuEKqk56k-ZxaKrLOiGOBASrkRCQNVxOSA/edit

so basically you need to modify this formula by adding ID and sheet name:

=SUM(IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41"))

NOTE: you may be prompted to allow connection between those spreadsheets of yours...

Best Answer

Related Solutions

Google-sheets – Does Google Sheets have sheet-level named ranges

Google Sheets – Adding Cells from Multiple Sheets into a Master Sheet

Related Topic