Google Script Automation: Create New Sheets Tabs Automatically

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.

Photo by Breakingpic from Pexels

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.

A tab for each week

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!

Getting Started

To make this work on your Google Sheet, start by clicking on Tools and then Script Editor.

Opening the script editor in Google Sheets

This will open a new script editor that is Untitled and includes a function called myFunction.

A new Google Scripts project

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.

The Code

Github Gist of the Google Scripts code

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 A2:E).

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

The Trigger

In the script editor, click on the button in the menu bar that looks like a clock.

Button for ‘Current project’s triggers’

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.

One of the trigger customization options

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.

The Variables

In the code, you’ll see a section named ‘Dashboard’ — this is where you set the customization variables for your naming scheme and range.

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

Example:

• 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!