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

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();

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

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();

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++){
}

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);

Save and Run

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

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.