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