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

 

Advertisements

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.

Create Fluid Quizzes on Canvas

Do you have access to Canvas? There are some amazing features, aren’t there? So many, that it can be a bit overwhelming. The other side of that is the temptation to keep everything extremely basic, but that means you aren’t taking advantage of all of the bells and whistles at your disposal.

Why might fluid quizzes be helpful to you?

Do you:

  • teach multiple sections of the same class?
  • have students show mastery as they are ready, rather than all on the same day?
  • allow students multiple opportunities to show mastery?
  • want to safeguard against potential cheating?

If you answered yes to any of the above, fluid quizzes are for you.

What are fluid quizzes?

They are online quizzes that change within the parameters you have set so it is different each time it loads. Students are not guaranteed to have any of the same questions as their friends, and even if their question is the same, the order of the answer choices are scrambled.

How Do I Create a Fluid Quiz?

When you first create your quiz, scroll down on the details tab and check the box for “Shuffle Answers.” I also like to let students see their correct answers at a certain date, my standard is to make their answers visible the day after the quiz closes for submissions. You can also click “Allow Multiple Attempts.” Each attempt will potentially load different questions, and answer choices for questions that were on the original quiz will be shuffled.

detail options

Now it’s time to add the questions. Go to the “Questions” tab. Choose “New Question Group.”

new question group button

Click “Link to a Question Bank.” If you have made quizzes before, but not played with question banks, you probably have a whole lot of unfiled questions.

link to question bank

If you already have a question bank ready to go that only includes questions you would be happy to have on this quiz, select that title and click “Select Banks” at the bottom of the pop up window. If not, choose “View Course Question Banks.”

question bank list

On the far left, choose the button “Add Question Bank.” Give it a title and press enter.

add question banks

If you have questions you have used before that you would like to add to this question bank, open the “Unfiled questions” bank. If you have just one or two questions you want to move, you can just click “move/copy question to another bank” for those particular questions.

move question

If you have a lot of questions you want to move, you’re better off using the “Move Multiple Questions” tool on the far right.

move multiple questions

Clicking this will open a pop up window that lets you quickly click check boxes for all the questions you want to move, and send them all to the same question bank. You can send them to an existing bank, or create a new one right there.

move questions pop up

Keep in mind that you need to have more questions in your question bank than you intend to include on your quiz. The closer the number of questions are in your bank to the number of questions you plan to include on the quiz, the higher the probability that students will have the same question on their quizzes. I like to keep the number of questions in my bank at about double what I plan to include on my quiz.

To add questions, go to the question bank you want to edit, and click “Add question” on the right hand side. Edit your question bank until you are satisfied with it. If you work with a team, perhaps each person could be a teacher in a shared sandbox, and you can all contribute a certain number of questions to each question bank.

One word of caution

You want all of the questions in a question bank to be of a similar difficulty level. If your bank of questions has a range of skill levels, it is entirely possible that some students will luck out with all easy questions, some will have a mixture, and others will have all difficult questions. If you have a range of difficulty levels for questions on the same skill, you can make a different question bank for each level, and are able to allot more points to the more difficult questions if you want.

add a question

You can also include multiple question banks on the same quiz. For instance, I created a fluid quiz on rounding that includes 3 questions on rounding to the nearest ten, 3 questions on rounding to the nearest hundred, 1 question rounding to the nearest dollar, and 1 vocabulary question. I have four different question banks for that quiz.

multiple question banks

This quiz will load 8 questions for each student. I have 6 questions in the Round to the Nearest Ten bank, 1 question in the Round to the Nearest Dollar bank, 6 questions in the Round to the Nearest Hundred bank, and 2 questions in the Rounding Vocabulary bank. I know everyone will get the same question for rounding to the nearest dollar, and there is a 50/50 chance of students having the same question regarding vocabulary. The actual rounding practice questions will be fairly varied, so I’m happy with it as it is.

Results

I previewed the quiz and this is what loaded for the first 3 questions.

first two questions

When I closed the quiz and made no other changes except pressing the preview button again, this is what loaded for the first 3 questions.

first two questions - second try

Out of the 3 questions that loaded for each time I previewed quiz, there was only one repeat, and the order of the answers was scrambled. This means that even if a student has multiple attempts on the quiz, they are likely to get different questions for most of the quiz on their subsequent attempts than they did on their first.

Can I Do This On Google?

As far as I know, there isn’t a way to do something quite like this on Google Forms at this point. You can scramble the answer choices, and even scramble the order of the questions, but there isn’t a question bank feature. You can have the form set up to move to specific pages based on how certain questions are answered, which has its own benefits. For example, you can use a Google Form quiz to send students to easier or harder questions as they answer each question correctly or incorrectly. I can see that being really helpful when you give a pretest. It would allow you to find the upper limits of your students who are already knowledgeable on that topic without frustrating your students who have less prior knowledge.

 

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.