Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Google Apps Script: Code Insert a Sheet

Google Apps Script: Code Insert a Sheet
code insert sheet

coffeeEDU is a one hour unconference style get together of educators. At a coffeeEDU I attended in Connecticut this week, the conversation turned to Google Apps Script. We did a really simple script to add a sheet to a Google Sheets spreadsheet. I used this technique to create https://alicekeeler.com/rostertab. RosterTab takes your list of students and creates a tab (inserts a sheet) for each student with their name on it. Here is the script we tried at coffeeEDU, give it a try!

Spreadsheet

You will need a Google Sheets spreadsheet to apply this code. Go to http://sheets.google.com and make a new spreadsheet. You will want to go to the Tools menu and choose “Script editor.”
Script Editor

Code

This just gets you started with making a successful code. If you want to dive deeper into Google Apps Script I recommend you learn a tiny bit of JavaScript and check out some of the tutorials on the Google Apps Script page.

The code you are going to write needs to go in between the curly braces.
code goes here

First Line of Code

If you are using Google Apps Script with Google Sheets this first line of code is almost always your first line of code.

var ss = SpreadsheetApp.getActiveSpreadsheet();

var stands for variable. You are naming this line of code ss. 

Note that after you type SpreadsheetApp (capitalization matters) and you put the period that a multiple choice list should pop up. SELECT the getActiveSpreadsheet() instead of typing it.

Semicolon

At the end of each row of code you need a semicolon (;). Make sure you end with a semicolon.
first line of code

Second Line of Code

ss.insertSheet(‘chupacabra’);

You are building on the previous line of code. The first line of code uses the variable ss. Type ss and a period to build on the variable. After pressing the period, a multiple choice list should appear where you can click on insertSheet().

Inside of the parenthesis you can name the tab whatever you want.

Rule: All text strings go in single quotations. 
Put single quotes around the name of your tab. I called mine chupacabra.
insert sheet

Remember the semicolon at the end of the line of code.

Run the Code

Click the save disk in the toolbar. This will prompt you to name your script. Name it whatever you like. Once the script is saved, you will want to click on the run icon (looks like a sideways triangle in the toolbar.)
Run

Check the Spreadsheet

Go back to the spreadsheet, a new tab should have been inserted.
New sheet inserted

Kick it Up a Notch

Repeat the line of code to create several tabs at once. Create a new line of code for each tab you wish to create. Change the name of the tab within the parenthesis.
Note: you can not have 2 tabs with the same name. If you run the code a 2nd time with the same tab names, you will get an error.

Sample Code

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.insertSheet(‘budget’);
ss.insertSheet(‘roster’);
ss.insertSheet(‘purchased’);
ss.insertSheet(‘wish list’);
ss.insertSheet(‘parent volunteers’);
}

2 thoughts on “Google Apps Script: Code Insert a Sheet

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.