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 you 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
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.
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.
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).Quinn, absolutely right! I think was probably trying to be too clever with js truthy/falsy. Glad you noticed and thanks for the comment.
Pingback: $10k Technical Skills Part 1 | The Front End: JavaScript, HTML and CSS
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 🙁
That was a silly javascript mistake. Because of the logic I had been using, if the sheet title began with the word that is used to match sheets to delete, the sheet would not be deleted. I changed the logic and updated the code. See the demo Google Spreadsheet here: https://docs.google.com/spreadsheets/d/1YN2DoA3YLlfwkVvIK24FBJEO4zm6eKkzbW4raR2mGU4/edit?usp=drive_web
+1
Thank you
Legend.
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!
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. :*(
Thanks for the suggestion, Mark. I’ll see if publishing add-ons has gotten any easier…
Thank you Trevor!!! Much appreciated, worked perfectly.
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.
thank you. Thank You. THANK YOU!!!
Looks nice Tovly, but what I need is to be able to move them to another spreadsheet entirely, for archiving purposes. Is that something you can add, which will do that?
You are my new best friend. You saved me about 10,000 hours of Siberian-Gulagish work.
When I cut & paste the code, I get this error message:
Missing ) after for-loop control. (line 30, file “Code”)
Also, when I view the demo, the Spreadsheet Cleanup menu doesn’t appear.
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 😉 )
I get the exact same error
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.
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.
Hi Trevor,
I am having the same issue. Is there any solution ?
Hi AL, see my reply to Edgar’s comment.
Is there a way to run this and have it work on multiple spreadsheets as well?
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!!
Is there a way to write on one google sheet and have is copy and write in the same place on multiple sheets?
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.
This script worked out perfectly for me. Had to remove all my 2016 weekly sheets. 104 in total.
Thanks!
Wow, this script worked perfectly! Thank you so much for sharing, really appreciate it. Saved me a ton of work!
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!
Thank you very much!!
You are a genius thank you!!
Awesome script, saved me heaps of time- thanks so much for sharing!
The script doesn’t seem to work for me.
The Hide/Unhide functions work, but not the delete.
My sheet names do include numbers, but I have tested by removing the numbers (ie: April 01 to just April One) to test it out, but it does not delete the sheet when I request a delete for both April and April One.
What I am doing wrong?
Hi Julia, I’ve never seen this before. Does an error message pop up? The best way to debug it would be to go to the script editor and checkout the Execution Transcript and the Logger: https://developers.google.com/apps-script/guides/support/troubleshooting
Hi Trevor – this is incredibly helpful. Is it possible to add a rename function. I tried to modify your script but was not successul
function renameSheets() {
var renameSheetsContaining = Browser.inputBox(“Rename sheets with names containing:”);
var renameSheetsTo = Browser.inputBox(“Rename to:”);
//var destinationId = Browser.inputBox(“Enter the destination spreadsheet ID:”);
if (sheetMatch(renameSheetsContaining)){
for (var i = 0; i < sheetsCount; i++){
var sheet = sheets[i];
var sheetName = sheet.getName();
Logger.log(sheetName);
if (sheetName.indexOf(renameSheetsContaining.toString(renameSheetsTo)) !=){
Logger.log("RENAME!");
//var destination = SpreadsheetApp.openById(destinationId);
sheet.rename(renameSheetsTo);
}
}
successAlert('renamed')
} else {
noMatchAlert();
}
}
Can you share the script for making multiple copies? You mention it at the top of the page, but I don’t see the actual script for that here on this post.
Try following the video instructions.
Hello Trevor, does this script, or another script that you know of, allow a person to copy a template containing multiple sheets linked to each other so that the copied sheets are linked in the same way?
I desperately need this script to work for me, as our company has a google sheet spreadsheet that is too large, but our sheet tab names are dates as in 1/1/16, 1/2/16, 1/3/16, etc. for each day of the last two years, but since your script doesn’t do numbers in the name, it doesn’t work. I would gladly modify the code if I knew what to change. Any help would be appreciated.
I hope you’ve found a solution. As far as I can tell, the add-on works for numbers as input. Maybe, if you describe your problem in detail, I can help you out.
Works like a charm (4/Jan/2019)
Thank you very much for sharing this Trevor!