Teachers Using Google Apps Scripts

The training I’ve been to (so far) this summer where I’ve learned the most was one on using Google Apps Scripts. There was a pretest, which I completely bombed, and I wondered whether I was in so far over my head that I should just go home. It was very much out of my comfort zone, as the most sophisticated programming I’ve done before was Lego NXT robots with drag and drop programming, when I taught summer camps at the local science museum.

Why should a teacher learn to code Google Apps Scripts? Well, if you have repetitive tasks you do on any Google products (within the same product or moving information from one to another, such as from a spreadsheet to a document), creating a script to complete that task at the touch of a button (or automatically at a specific time) could be a real time-saver! It may take awhile to code the script in the beginning, but once you have it, it saves a ton of time later. If you’ve never coded before, the learning curve is a bit steep, but there are resources free on the internet to help you!

Helpful Resources

Codecademy – Use the JavaScript tutorial. Google Apps Scripts is sort of like a dialect of JavaScript. If you get the basics from this, you’ll have a good foundation.

Google Apps Scripts Developers Page – You can click on the product you’re coding a script to use with, or use the guides near the top to help learn how to use the apps scripts for different purposes. I recommend using CRTL+F in order to search for specific terms on the page.

Alice Keeler – One of the things that really helped me to make some of my scripts work was to look at one that is functional, and tweak it to do what I want that may differ just a little from that initial author’s intention. Alice Keeler has a ton of premade scripts and add-ons that she shares on her website for free. She also has tutorials for writing your own.

Example Scripts

To run a script:

  1. Open the file the script is attached to.
  2. Go to Tools>Script Editor
  3. Choose the script you want to run (if more than one) and press the play button OR click Run>Name of the script. All of the scripts below will only work on the tab of the spreadsheet you are currently open to.

Format a Spreadsheet – This will format a spreadsheet to get it ready to analyze data. It changes the column widths, deletes extra columns and rows, freezes the first column and the first two rows, changes the color of the background and font of the header rows, and set up to average the columns. To use it, open the file, then go to File>Make a copy. The copy is yours to edit and use as needed. Feel free to copy my script and change it for your needs.

Words Their Way Spelling Inventories – Semi-Automatic Analysis

I made a file for each version of the WTW spelling inventories. I call them semi-automatic analysis is that it doesn’t analyze correctly spelled features for incorrectly spelled words. If you type in how each student spelled each word, the script will mark the word as spelled correctly or incorrectly, and will give feature points for all correctly spelled words. You will still need to manually score feature points for words that have been misspelled. You’ll also need to transfer the data from each student’s tab to the class scoresheet. Once there, it will automatically color code each spelling feature as mastery or “could benefit from instruction” based on the recommendations from the Words Their Way book. I have the fourth edition of the book, so please double check whether there are huge changes that need to be accounted for before using my files as they are. I hope to update the code (once I figure out how to do it) to automatically transfer information from each tab to the class scoresheet, so keep a lookout for updates if you’re interested in using these. These files would be great to use with Alice Keeler’s TemplateTab script! Just make sure you run her script first.

You’ll need to use the link below for the file(s) you need, then File>Make a copy in order to actually use them. I have instructions for using the script on the first tab of each file to help you out.

Primary Spelling Inventory

Elementary Spelling Inventory

Upper Level Spelling Inventory


Creating Responsive Quizzes With Google Forms

A responsive quiz is one that responds based on a student’s answer. If you’ve ever used Google Forms to create “choose your own adventure” stories, this has a very similar setup. The idea is that when a student answers a question correctly, they get another question of equal or greater difficulty, and when a question is answered incorrectly, their next question is easier. This is a great way to determine a student’s upper limits on a particular skill when pretesting. This can really help when planning small groups, seeing which skills need to be reviewed from previous years in order for students to grasp the current skills, and knowing which skills students are already nearly proficient in to help pace your unit.

The first step (once you have determined the skill you’ll be pretesting) is to vertically align standards. I would recommend starting off with standards for the grade level below yours, or with standards from your grade level that students will need to have already mastered in order to be successful with this skill. My sample quiz is intended for 3rd graders to determine their prior knowledge for rounding. I found this math vertical alignment document that aligns Common Core standards from kindergarten all the way through eighth grade. The standards for place value and notation are located on page 19, so that’s where I’m looking for guidance on which standards to put together.

place value and notation vertical alignment

As you can see, there are multiple standards that students should have mastered in kindergarten, first, and second grade in order to successfully round numbers to the nearest ten and the nearest hundred in third grade. If students show mastery of the third grade standard, their quiz will also ask questions regarding the fourth and fifth grade standards in the skills similar to rounding.

For this quiz to work, you’ll need to click “add a section” on the right side after creating each question. After creating your questions, click on the stack of 3 dots in the bottom right corner of each question to reveal a menu. I like to shuffle my answer choices, but you’ll definitely need to use the option “Go to section based on answer.” You’ll need to set up each question to send students to answer correctly to a question of equal or greater difficulty, and all wrong answers to take a step back in difficulty level.

On mine, I’m including at least one question for each related standard from first through fifth grades. I started with a second grade question in an effort to build confidence with something familiar before moving forward. If they answer a second grade question right, they move to a third grade question, if not, they get a first grade question. If a first grade question if answered right, they move to a second grade question, if not, they get another first grade question. When I ran out of first grade questions, students who answered a second grade question wrong got another second grade question. I set it up to submit the quiz after any incorrect answer from 4th and 5th grade questions.

You can see my sample responsive quiz below. In order to avoid having changes made, I can only share it like this. One of the drawbacks of Google Forms is that you can either share with editing rights, or you can share the completed form. Feel free to take the quiz to see how it works, purposely answering questions correctly or incorrectly to get a feel for how it would play out for your students. The number at the top of each page represents the grade level of the standard the question covers, the letter beside it is just a numbering system to keep the questions straight.

How 1:1 Technology Changed My Classroom

This year, I was fortunate enough for my district to provide every student in my grade level a Chromebook that they could access throughout their school day.

First quarter, there were some hiccups. Not every student was allowed access for several weeks, as each student and their family attended the training regarding the expectations and acceptable usage of these devices, as well as the consequences for noncompliance. (Our technology lab is entirely comprised of Chromebooks, so students were already proficient on how to operate them.) Between this and all of the normal first quarter hullabaloo, I hardly used our Chromebooks, and I felt really guilty about it.

Around the beginning of second quarter, when things in the school year get a little less crazy, I began having students use their Chromebooks for pretty much everything. This was over the top, and not exactly best practices either.

For the remainder of the year, I’ve made it a point to strike a better balance, although it is still my preference for graded work to be turned in online, especially for tasks the take more than one class period.

Some of the benefits I’ve found of having 1:1 access to technology are:

  • Instant feedback for students – Whether students complete a self-checking quiz on Canvas/Google Forms, or work on a website that gives immediate feedback, students are able to realize their errors and correct their far more quickly than if they completed a worksheet and wait until I get around to grading it and hand them back.
  • Differentiation – A Google Forms quiz can be set up for students to be sent to a particular section depending upon how previous questions are answered, Canvas modules can be set up as Mastery Paths to give students “just right” work depending on their performance on the initial assignment. Obviously you don’t need technology to differentiate, but setting it up to run automatically is definitely a time-saver during implementation. I also like that it’s a little less obvious to students who is getting the easier work than it is when everyone has their paper on the table.
  • Save paper and copies – I have definitely used less paper than last year, and spent much less time standing at the copier waiting for it to print out the work for my class.
  • No lost papers – When you use less paper, there are fewer papers that can be misplaced! There is not a concern about whether you remembered to give a copy to the student who was absent the day a task was assigned, no making sure that you put that late paper in the same stack as the rest of the work from that task, and no one needing a second copy because they accidentally forgot their backpack at home that day. Students who are absent, sometimes return to school with the work they were absent for already completed. There is never a question about whether a student really did turn in an assignment, and work that has been turned in is marked with a time-stamp so you know exactly when it was submitted.
  • Paper planners are no longer the only way to keep track of assignments. – While some people function better with the paper planner, having work posted in Google Classroom or Canvas helps to keep track of assignments. You might only use it as a backup plan when the planner is forgotten, or as another layer of communication to keep families connected with what work students are doing in the classroom. Both Google Classroom and Canvas have a calendar function that show the due date for assignments. In addition to the calendar function, I have set up my Canvas classroom so that the landing page includes an embedded Google Slides file showing students what they need to work on that day, what work they can do to get ahead, and what students may do to keep learning when they have already completed all of the currently assigned work. I update that slideshow every morning before my class arrives so that I don’t need to embed new files each day.