Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Google Apps Script: Create New Tabs

google apps script tutorial make tabs
Google Apps Script: Create New Tabs

google apps script tutorial make tabs

Create Tabs in Google Sheets

Google Apps Script allows you to customize what Google text docs and Google Sheets can do for you. It is based on JavaScript, but you need to know very little to be able to do Google Apps Script. Here is a scripting activity to help you practice your Google Apps Script skills. You can find more tutorials at alicekeeler.com/scripts; scroll all the way to the bottom.

This tutorial will help you to create additional sheets in a Google Sheets spreadsheet. I previously coded RosterTab to allow you to easily take a roster of your students and create a named tab for each of them. Use the template at alicekeeler.com/rostertab to do this without any coding. If you want to borrow some of the code or to tweak my project use the Tools menu and choose “Script editor.”

Objective

Have a list of tab names in column A. Create a sheet for each name in the list with that tab name.

Google Sheets

This coding project uses Google Sheets. Create a blank spreadsheet and use the Tools menu to choose “Script editor.”
google sheets tools script editor

SpreadsheetApp

Your first line of code is almost always to identify which Google App you are coding in Google Apps Script. In this case we are coding SpreadsheetApp.

var ss = SpreadsheetApp.getActiveSpreadsheet();

Sheet

When coding a spreadsheet you want to identify if you are applying the code to the entire spreadsheet, a particular sheet or a particular range. After identifying the variable ss to indicate the entire spreadsheet, define a variable to identify a particular sheet.

var sheet = ss.getActiveSheet();

Script editor code the sheet and spreadsheet

Get Active Sheet

Whatever sheet you are looking at when you run the code will be what the code is run on. It will be important that the active sheet is the one that has a list in column A. If you want to specifically define a sheet even if it is not the active sheet you may want to try using var sheet = ss.getSheetByName(‘Sheet1’);

List

For this script, we are assuming you have a list of tab names in column A. If you have not already, type some things into column A. For this project we are keeping it simple. To avoid an error, you can not duplicate names or have blank rows
google sheets put a list in column A

Last Row

Every time you use this code there may be a different number of items in the list. In my example screenshot above there are 7 items, but yours may have 5 or even 25!! We do not want to count this list ourselves each time, let the code do it for you. Thinking about the 3 levels of a spreadsheet (spreadsheet, sheet, or range), this is at the sheet level. The method getLastRow() will return the number of the last active row in the sheet.

var last = sheet.getLastRow();

var last equals sheet dot get last row

Loop

A loop repeats a line of code until the conditions are met. For this script we want to start at row 1 and stop when we reach the last row. Normally in JavaScript we start counting at zero! This means that row 1 starts at zero.

for(var i = 0; i < last ; i++){
}

google sheets script for loop

The code will start counting at a value of i=0 and then after running the code in the For loop, increment the value of i by one each time until the value of i is less than the last row.

Get Tab Name

The names of the tabs should be listed in column A in the spreadsheet. You need to identify the range on the spreadsheet and tell the code to get the name from column A and make a new sheet out of it.

Range

Start at the sheet level and use getRange(row, column) to identify the location of the information. In the For loop, we start counting at zero so the 1st row is one more than the variable value. Thus the row is i+1. Column A is the first column and we will not change from this. The column will always be column 1.

getValue()

Getting the range does nothing other than look at that cell. You then have to tell the script what to do with that cell. In this case, you want to get the value of what is in the cell. Use the method getValue().

var tabName = sheet.getRange(i+1,1).getValue();

Create Sheets

You create sheets in the spreadsheet so the next line of code is at the spreadsheet level. The method insertSheet() will insert a sheet into the spreadsheet. Inside of the parenthesis you can indicate what the name of the new sheet will be. In this case, you want the value from column A to be the name of the tab. Previously you defined it as tabName. Place this variable name in the parenthesis.

var create = ss.insertSheet(tabName);

google sheets create a script to insert multiple tabs

Save and Run

That is it. Click the save icon or use Control S. Run the code by pressing the run icon.save and run the script

Check the spreadsheet to see if you have a whole bunch of named tabs! While it may seem faster to create the tabs individually, depending on how many tabs you have it might actually be FASTER to code the process rather than do it manually.

The Code

function makeTabs() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var last = sheet.getLastRow();//identifies the last active row on the sheet

//loop through the code until each row creates a tab.
for(var i=0; i<last; i++){

var tabName = sheet.getRange(i+1,1).getValue();//get the range in column A and get the value.

var create = ss.insertSheet(tabName);//create a new sheet with the value
}
}

Template

Click Here to copy my spreadsheet that has the sample code.

7 thoughts on “Google Apps Script: Create New Tabs

  1. Awesome! Came in handy for a spreadsheet I want to link to our walkthroughs so each staff member has their own tab. Sadly, we have a large staff, so I’ll have to do this on 1-2 sheets because I get a warning I’ve hit the cell limit in sheets. ARG!

    1. Delete extra columns and rows. You can have up to 200 tabs and 2 million cells. There is an add on that will easily get rid of extra rows and columns

  2. I used this successfully to create the named tabs, but we need to have this create a duplicate of a specific sheet. The sheet that needs to be duplicated has a few active controls (there is a button), so I can’t easily copy the sheets, we need to use duplicate.

  3. I know that this is an older post, but I would like to use this script. I need it to ignore duplicates in the column. Any suggestions? I’m new to Google Script, and I’m struggling.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.