Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Google Sheets: Code Adding Content to Multiple Tabs

code google sheets to add info to multiple tabs
Google Sheets: Code Adding Content to Multiple Tabs

code google sheets to add info to multiple tabs

You Can Code Google Sheets

Coding Google Apps Script is easier than you think it is. Learning to code can increase your productivity significantly. One thing I frequently code is adding the same piece of information to multiple tabs in a spreadsheet. I like to have all students in one spreadsheet, their name on a tab along the bottom. Try using TemplateTab to duplicate a graphic organizer per student within the same spreadsheet. It isn’t so uncommon that I left something off the graphic organizer and would need to copy and paste onto each sheet. Coding makes this quicker.

Script Editor

In a spreadsheet use the Tools menu and choose “Script editor.”
Script editor in Google Sheets

Function

If in Algebra class you ever wondered “When am I ever going to use this?” Today is the day. You will create functions using function notation! Remember f(x) = 3x + 1? You will see a default function
function myFunction(){   }
function myFunction

Between the curly braces is where you will write your code.

What App Are You Using?

My first line of code is almost always identifying which Google Apps I am using. In this case, it is SpreadsheetApp. You want to name the code with a variable so that you can use the line of code repeatedly.

var ss = SpreadsheetApp.getActiveSpreadsheet();

Spreadsheet App code

How many Sheets?

When coding you do not count, you let the computer do the counting for you. To put a piece of information on every sheet we need to know how many sheets there are. You start with the variable you defined in the previous line of code, ss and press period. This will bring up a multiple choice list of methods. You will choose “getNumSheets()” and put a semicolon at the end of your line of code.

var len = ss.getNumSheets();

How many sheets are there?

For Loop is the Magic Sauce of Teaching

As teachers, we need to do everything 30-150 times. Coding does the tedious repetitions for you. You LOOP your code to run for as many students as you have (for example.)

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

This code says that you will create a counter of i that starts counting at zero. FOR AS LONG AS your counter i is less than the length of the number of sheets it will run the code. The i++ means it will increment (add one) the value of your count i each time the code runs.
End of function curly brace vs end of for loop curly brace

Which Sheet?

You want to call up each sheet separately. In JavaScript you start counting at zero. So Sheet 1 is really the 0 sheet. Type the variable ss to let the code know you’re talking about THIS spreadsheet. Press period to get a multiple choice list. Choose “getSheets().” This is a list (called an array) of all the sheets in your spreadsheet. If you put brackets next to that with a number in it is says to get that specific sheet. We will put an i in the brackets because we start at looking at sheet 0 and then when i increments we will look at sheet 1 (which is really Sheet2… I know, confusing) etc… until it looks at every sheet.

var sheet = ss.getSheets()[i];

Set Value

Let’s pretend I forgot to put the words “First name” in the cell A1 on each of the sheets. I want to tell the code to go to cell A1 on each sheet and put the words “First name.”

sheet.getRange(‘A1’).setValue(‘First name’);

Get range of sheets

Use the variable sheet that we defined in the previous line to say we are talking about sheet i. (Remember i starts at zero and then increments.) Press period to get a multiple choice list and choose “getRange(). In the parenthesis enter the cell you wish to copy to. Note: this is considered a text string and thus must be in single quotes. You will know you entered it correctly if it turns red. Then press period again to tell it what to do in that cell. From the multiple choice list choose “setValue().” Within those parenthesis put what you want to add to that cell. Again, this is a text string and needs to be in single quotes and it will turn red. Don’t forget the semicolon at the end of the line of code!

Save and Run

Hit the save icon in the toolbar (you’ll be prompted to name your code.) Then click the run icon (sideways triangle).
Save and Run

Authorize

You will need to authorize the code. It looks scary because Google is making sure you understand that they did not review the code and they are not vouching it is safe. You are only authorizing yourself access so say yes to any of the statements. You’ll possibly see a big blue button that says “Back to safety.” Do not click on that, that is just Google covering their behind.  Click on the small words “Advanced.”
click on advanced

Then more text will expand out and Google will once again warn you that this is “Unsafe” only in the sense that zero people at Google have looked at the code to verify it and they are not vouching for the code. Click on the words “unsafe.”
Click on unsafe

Check your Spreadsheet

Boom, that should do it. (Note, you may need to run it again after you authorize.) Look at all the tabs to make sure that your value was copied onto every sheet! It’s fun!!
look at all the tabs

Keep Using It

You do not have to reinvent the wheel. Copy and paste that code into a Google Keep note and then in any spreadsheet use the Tools menu to access the Script editor. Then paste the function and run it.

Edit the code to add content to whatever cell you want. In my sample I used A1, but edit it to any cell reference you want. Run it again and now a different cell will show the value you want it to.

Try A Spreadsheet A Week




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