Google Script Automation: Create New Sheets Tabs Automatically

Mindy Zwanziger
Sep 26 · 4 min read

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.

“To Do” written in Scrabble-like letters above a yellow, blank post-it note.
“To Do” written in Scrabble-like letters above a yellow, blank post-it note.
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 screenshot of the tabs in a Google Sheet. Each named with a date in the MM-DD-YY format.
A screenshot of the tabs in a Google Sheet. Each named with a date in the MM-DD-YY format.
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 and then .

A screenshot of the Tools menu in Google Sheets, with the script editor option highlighted.
A screenshot of the Tools menu in Google Sheets, with the script editor option highlighted.
Opening the script editor in Google Sheets

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

Screenshot of the newly created Google Scripts project.
Screenshot of the newly created Google Scripts project.
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 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 ).

In this case, the naming scheme might be the date of the next Monday, the range would be 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.

Screenshot of the button that looks like a clock. Alt text of button is ‘Current project’s triggers.
Screenshot of the button that looks like a clock. Alt text of button is ‘Current project’s triggers.
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.

Screenshot showing the select event source options.
Screenshot showing the select event source options.
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.

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

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

The Startup

Medium's largest active publication, followed by +527K people. Follow to join our community.

Mindy Zwanziger

Written by

A teacher turned developer on the hunt for simple explanations to complex ideas. mindyzwan.com

The Startup

Medium's largest active publication, followed by +527K people. Follow to join our community.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade