A 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.”
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.
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.
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.
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.)
Check the Spreadsheet
Go back to the spreadsheet, a new tab should have been 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”
I get an invalid sign line 3….
Simply copy pasting from your site….
The code works fine and helped me. Really great explanation. Keep up the good works.