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

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