Learn to Code With Google Sheets Macros

Last week, G Suite made an update to Sheets that made their product a little more efficient: macros. Perhaps you’ve noticed them in Microsoft Excel, and now they’re available in G Suite.

What IS a macro, anyway? It’s a way to automate tasks you have to do repeatedly, without writing code. To use them, go to Tools>Macros, record yourself completing the series of tasks you want to automate, save and name the recording, and then you can use it over and over again. If you’re not into coding, this can be a game-changer to save you some time later on repetitive tasks. Macros can be recorded so that they are performed over the exact area recorded on (think cells B2 through B7), or on a relative location (the active selection), so think about how you plan to use the macro before you start recording. (If Macros aren’t showing up in your Tools menu yet, check back in a few days. The nice people at Google told me that it could take up to 15 days from their new features roll out on 4/11/18 for everyone to have access to the shiny new features.)

But I AM a (novice) coder, and I’m excited about what this can do to expand my knowledge base. You see, G Suite Macros automatically transform the macro into code in Google Apps Scripts. Once you save the macro, click “Edit Script” to see what your actions look like in Google Apps Scripts code, which is a variation of JavaScript. You can access the script later by choosing “Manage Macros” and clicking the three dots to show the “Edit Script” option.

So, if there’s a task I know how do to manually, but haven’t the faintest idea how to code, theoretically, I can work backwards! I can create a macro for the task, and look at the code the macro created, instead of writing the code, hoping it will do what I want, and banging my head against the desk when it doesn’t for the 934th time. Once the script is created, I can analyze it to learn to write the code for the task I know how to do manually. Since I’ll know the end result of the code, I can use it to learn the commands and formatting that  created the desired result.

Typically, I put lines of code that I’ve found tricky to remember, but I know I’m likely to use again as notes into Google Keep. I make all of my coding notes the same color and give them the label “Code” to easily find what I’m looking for when I need it.

 

Advertisements

Sharing Hyperdocs: Canvas vs. Google Classroom

One tool I love for its versatility is the hyperdoc. They can be made using Google Docs or Google Slides, and it’s essentially a combination of content resources and a place for students to create their own work, all in one file.

I’ve shared them with students both by the force copy trick on a link in Canvas, and by sharing in Google Classroom so that each student gets a file. Both methods have their benefits; when sharing in Canvas (or another LMS), I can circulate the room while students are working, asking questions and giving feedback as they go, but students don’t submit their work until they are finished. In Canvas, Google files that are submitted are available to the teacher in a format almost like a screenshot, but it’s the whole file, not just one screen worth. Using Speedgrader, the teacher can quickly and easily scroll through a class’s work, scoring it on a rubric and making comments.

In Google Classroom, I can still circulate the room and give students feedback as they work, but in addition to that, the teacher has access to each student’s file from the moment it is created. My students recently worked on a hyperdoc when I was out of town for several days, and opting to share it with them via Google Classroom meant that I was able to provide them with comments containing encouragement or suggestions for improvement even though I wasn’t able to be present in class. Having to open each student’s file separately is a small inconvenience, unless using an add-on like Doctopus, or *gasp* paper and pencil, a rubric isn’t available, and for better or worse, the teacher sees student work whether it has been submitted or not. The positive to that is I was able to leave comments on work in progress, but the drawback is that it’s possible to score work that a student hadn’t quite finished.

Overall, I prefer the workflow available for scoring student work that’s available in Canvas, but the capabilities of viewing work in progress provided by Google Classroom. Assigning the work in both places and submitting the final product in Canvas is an option, but it sounds like it will be unnecessarily clunky. Students would need to look for comments in both places, and I imagine that a lot of kids will click “Turn it in” in Google Classroom and it will be missing in Canvas. No, it’s definitely best if I choose one place to disseminate my hyperdocs, and which one I pick may change depending on whether having access to student files throughout the process or having an easy way to score the work is more important for that task.

Hyperdocs: Content and Creation All In One

What is a hyperdoc? It’s a file that contains necessary content and a place for students to create and add their own work. For an easier workflow, sharing a hyperdoc in Google Classroom, Canvas, or another LMS will allow your students to access a copy of the file (so they don’t change your original and each student or group can do their own work) and give them a place to submit their finished product.

I find that with elementary students, it helps to have a finite area in which their work is supposed to go. Papers that have a question, but not a line on which to write an answer typically results in a student not realizing they were supposed to do something in that space. The same often works in digital formats. I like to use tables for this purpose. Sometimes it’s a series of 1×2 tables with the question in a colored cell and a blank cell beneath it for students to put their answer. Other times I’ll put tables so questions are on the left and answers go on the right, but I color each row of cells so the question and answer that belong together are in the same color cell. While students are able to change that, I think it gives a good visual cue of “these belong together” to help students be confident that they are on the right track.

Below are two very different hyperdocs I’ve used with my class recently. The RI Pathway has 3 versions. I only shared one version, but leave me a comment or shoot me an email to see the other versions. Each iteration is identical except for the linked texts being below, at, or above grade level, and the image students need to analyze is from the text at their respective reading level. I did remove the links to the texts for copyright reasons since they were accessed from Reading A-Z and ReadWorks, but the titles accurate, so you can search for them on those resources if you wish.

Pi Day Challenge Hyperdoc

RI.3.5 RI.3.7 Pathway Hyperdoc

Check out The Hyperdoc Girls’ website for more information and resources.

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.

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.