Take it off your weekly to-do list — learn how to automatically create a new tab, clear a designated range, and delete an old tab in Google Sheets.
As an admin at a civil engineering firm, I often get tasked with small, odd jobs. Many of these tasks are simple enough but unpleasantly tedious and repetitive. In one case, I was asked to create a new Google Sheets tab in a document every week. The tab needed to be named using the date of the next Monday and should have all the previous week’s data cleared. For a while, it was simply a part of my weekly to-do list.
Eventually, however, I realized — there has to be a way to automate this!
Lo and behold, there was!
If you’d like a full explanation, continue reading. Otherwise, feel free to jump to the code!
To make this work on your Google Sheet, start by clicking on
Tools and then
This will open a new script editor that is Untitled and includes a function called
Name the project, by clicking on ‘Untitled project’ and typing — any name you find useful is fine!
Delete the pre-loaded code (the
myFunction function), including the curly braces.
Copy and paste the below code into your now-blank script editor.
Understanding the Customization
From here, you’ll need to customize this to your needs — you will need to set the following:
- The date-based naming scheme of your tabs
- The spreadsheet range of data to be cleared
- The date/time you want the script to run, also known as the trigger
Begin considering how you want this to run. Let’s say we have a spreadsheet that lists our tasks for each day of the week. The tasks list it out in a Monday- Friday table and we create a new tab for each subsequent week.
Generally, we want to start filling out the next week’s tab starting on Thursday of the current week. The tasks for each day are listed individually by row starting with the second row for each column (in cell reference lingo, that would be
In this case, the naming scheme might be the date of the next Monday, the range would be
A2:E and the trigger would be set for sometime before you start working on Thursday.
Setting the Customization
In the script editor, click on the button in the menu bar that looks like a clock.
You can add triggers by clicking on the bottom in the bottom right that says “Add Trigger.” From there, change the event-source to Time-Driven.
After this, you’ll want to change the time details depending on your particular needs. If you want a new tab each week, day, etc… you can set that here.
In the code, you’ll see a section named ‘Dashboard’ — this is where you set the customization variables for your naming scheme and range.
numberofDaysBackwardForDeleteTab are both based on the trigger date. These two numbers need to align (i.e. always land on the same day of the week), otherwise the script won't have an appropriately named tab to delete.
- Let’s say the trigger is set to run every Thursday at 5 pm.
- If I’d like the date of the newly created tab to be the date of the following Monday, I’d change that variable’s value to 4.
- If I’d like to delete the tab from two weeks ago, I’d set the value of the deleted tab variable to 17.
rangeToClear, as mentioned earlier, is simply the cell references of the data you’d like to clear for each week. The reference is surrounded by single quotes.
That’s all there is to it! You’re on your way to automating away some of your work.
Automate well, my friends!