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.