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.

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.