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.

Using AutoCrat in the Classroom

At a training I attended a couple of months ago, I was shown how to use the add-on autoCrat for Google Sheets. It takes information from a Google Sheet and disseminates it into specified places on a Google Doc based on a template that includes <<merge tags>>. Once in the Doc, it can be emailed automatically to specific email addresses as a Doc or PDF, shared with specific people, or just kept in your Google Drive.

The possibilities are endless for how to use this, but here are a few ideas. Feel free to check out my AutoCrat Ideas folder in Google Drive. All of the files there are set up for you to have viewing access, but File>Make a copy will add a copy of it to your drive to edit for your own use.

  • At Meet the Teacher night, instead of trying to read information scrawled on the papers you left around the room, have parents completed a Google Form. From there, use an autocrat job to: send a personalized welcome email, and populate a student information sheet that you can print for your sub folder and teacher planner.
  • Alice Keeler had the brilliant idea to use AutoCrat to create personalized newsletters for each student. I have a sample in my folder. My sample was made very lazily, so there are options such as, “Student is doing social studies,” but it’s enough to give you the idea of how it works. For the newsletter, I have mine set up to not run the AutoCrat job until I manually go to the spreadsheet and push the run button. That way, no one gets their newsletter until I’m ready to send it to everyone. I made a Google Form for it, but some sections will be the same for every student (although, not for every newsletter), such as general information and important dates. I plan on completing that part of the form for the first student, then copying and pasting for the rest of the class in the spreadsheet. Another alternative would be to update the template before running the AutoCrat job for each edition of the newsletter, and just eliminating that question from the Form.
  • Filling out award certificates.
  • Sending emails to parents to inform them about an event regarding their child (positive message, missing work, etc.)
  • Sharing the questions and a student’s answers to a Google Forms quiz with their family in a neat document.
  • Awarding badges or certificates for completing specific tasks.

Do you already use autoCrat in your classroom? What are some other ideas you have for how to use it to make your life easier?

 

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.

Playlists and Pathways: How Do I Use Them?

Think of your playlist or pathway as a road map. Before you go on a long trip, you know the route you plan to take to get to your destination, even if you just know which interstates to take. Sometimes, even though you have a map, you take an unexpected detour and need to backtrack, other times you start off in a different place than someone else does, but you end up in the same destination. Using playlists and pathways in your classroom is a lot like this. As the teacher, it’s your job to create the “map;” in this case, the playlist or pathway for this skill or unit. Then, based on data from pretests or other resources, you mark the starting point for each student, and teach.

Some teachers use instructional videos as some of the activities on their playlists and pathways, and while I’m not opposed to making a video one of the tasks, I don’t feel comfortable completely replacing my instruction with a video. There’s just something to be said for the ability to gauge your audience, and adjust based on conversations students are having and questions they are asking as you check for understanding at different points throughout the lesson.

Let’s use an example to illustrate how this might look. Keep in mind that each day, you will look at how your students are progressing on their independent work. Anytime I have scheduled a reteaching lesson on the document below, that means that the data from that lesson’s independent work would show that group needed additional instruction and practice on that aspect of the skill before being ready to move on. Sometimes you will need to reteach for more than one day, other times not at all.

NFPathway1 (This is the pathway the embedded document below is teaching.)

Playlists and Pathways: What Are They?

The short answer is, playlists and pathways are the road map to your personalized learning unit for you and your students.

A playlist is a single-track path of activities that students will complete, in the order they are written. Based on information such as pretests, students may start at different points of the pathway, but the pathway for that skill or unit has all of the same activities and assignments on it for each student.

A pathway is much like a playlist, but it offers students choice. Instead of one activity or assignment that the student must do, they have 2 or 3 options of which task(s) to complete for that portion of the pathway. Just like with a playlist, the starting point for each student may depend upon information from a pretest or other data. Again, there is a progressive order in which the tasks must be completed, but the options can help to keep students engaged and interested. Sometimes the options are sorted by multiple intelligences, learning styles, levels of Bloom’s Taxonomy, or even the difficulty level of the material (below, at, and above grade level).

4.OA.14.OA.24.OA.34.OA.44.OA.5Pathway

NFPathway1

Both linked files are written as pathways, which means that they offer at least some level of student choice. To turn them  into playlists, all that would have to change is that students would be required to complete all activities in order, or you would remove some of the activities and students would be required to complete each task in order.

If your district uses Canvas as an LMS, there is a feature that allows you to use tasks and pages on Canvas as you would a playlist or pathway. To use it, you need to go to your settings and click the slider bar for mastery paths to green. On a graded task (teacher graded or automatically scored) that has been assigned to everyone, go to the Mastery Paths tab to choose which task(s) students complete next depending upon how they score on that assignment. If you assign multiple tasks for a score range, the default is for students to do both tasks, making it a playlist. When you are setting it up, you can click the “&” between tasks to change it to “or,” which allows students to choose one of the two (or more) tasks you have attached to that scoring range. This subtle detail effectively changes your playlist to a pathway.

I’m using a Mastery Path module in Canvas LMS for my students as we are reviewing for our end of year standardized testing. For each specific standard, I have a pretest in Canvas. Depending upon how they score on that pretest, they are either directed to another pretest, or to a page with review instruction on the skill and short quiz to practice. Students who do not show mastery on either quiz will be pulled for small group instruction and practice, then given another chance on the quiz. This allows students who have mastered the skills move on instead of sitting in on yet another review session for something they can already do proficiently, and lets those who need more instruction and practice have that in a smaller setting.

What is Personalized Learning?

According to the training I attended for several days when my school joined the personalized learning cohort in my district, it can be whatever it needs to be at your school. I was reminded through an article I read recently that personalized learning, at its core, is starting off where your students are, and taking them to the next logical step, preferably in a way that offers students choice and encourages student buy-in for the need to know that information.

Personalized learning is essentially differentiation on steroids.

How do you start where students are? Well, one way is to pretest them. Here are a few things I learned from using pretests in the past:

  • I really should have included questions that were 1 and 2 years below grade level at the beginning of the pretest. There were times when I assumed students had mastered these foundational skills, only to realize that we needed to take a few steps back when my lesson started with on-grade level material. My pretests have typically been identical to my end of unit test. I teach 4th grade. Ideally, my future pretests will include 2nd and 3rd grade level questions from skills that lead to 4th grade standards, then I’ll include 4th, 5th, and potentially even 6th grade skills that align to give me a clear path of “where to go next” with those students who have already mastered grade-level material. Google Forms has the potential to be really good for this, because you can make the next question conditional on whether students answer each question correctly. For instance, I could have a second grade level question as the first item on the test. A student who answers correctly would advance to a third grade level question, while a student who answers incorrectly would see a different second grade question next.
  • Multiple choice questions, while easiest to score, are not necessarily the best indicator of what a student knows on a pretest (even if most standardized test use this method). I once had a student score 100% on a multiple choice pretest on multiplying and dividing multi-digit numbers. When I handed him his pathway where he was to skip the instruction and initial practice and go straight to using those skills in constructed response tasks, he looked at me and told me he had no idea how to multiply and divide more than his times tables. He wasn’t joking, he was just a really skilled test-taker. I think that either having a performance task as the pretest or as a first activity for those who have aced the pretest would work well to prevent good test-taking skills from creating holes in students’ knowledge.
  • “Go deeper with your grade level standards, not up to the next grade level” is the mantra I’ve heard, but let’s think about it for a minute. Have you ever read the Common Core standards? If you attempt to take a standard at your grade level to the next level, you’re probably really teaching a standard that is 1-2 grade levels higher. Fourth grade standards require students to add and subtract fractions with like denominators, so bumping that up a notch is adding and subtracting fractions with unlike denominators, right? Well that’s a 5th grade standard. If you really look at the MAP test Learning Continuum, many of my students score in a range where what they should be learning next is a combination of 4th, 5th, 6th, and 7th grade standards. I have a colleague who looked at the learning continuum for her gifted 4th graders in reading, and found that she needed to teach them some 11th grade skills in order for them to show growth. All right 4th graders, let’s read and analyze Shakespeare’s sonnets today. (I actually did teach sonnets this year, but found one in Caesar’s English by Michael Clay Thompson that was about Constantine, leader of ancient Rome, rather than describing Shakespeare’s mistress. I appreciate when I can use appropriate content.)

How do you offer choice and yet still make sure students take the next logical step for them as you are teaching? The short answer is playlists and pathways. The long answer is worthy of a separate post for another day.