Recently, Google released what might be THE multi-purpose automation tool in Sheets: The Macro. To anyone who's been an Excel power user for a while, Macros are old news; they've been around in since the very first version of Excel and became widely accessible in 1993. But now, the news is out that they've arrived for Sheets users as well.
So What's a Macro?
A Macro is a pre-recorded set of actions that you can train Sheets to carry out on your behalf, e.g. "Highlight column C, turn the text to Lobster typeface, turn the cell backgrounds red, and then rename the column "Lobster Catch". It's like writing down the recipe for a favorite dish: "Stir the gummyberries slowly to the left, then stir them slowly to the right, then smack the pot with your spoon to get out the bubbles..."
Sheets has allowed you to write recipes like this for a while through a programming language called Google Apps Script. You open up the Script Editor, then type in your commands and save them. Once your 'recipe' is finished, you can order Sheets to carry out that pre-written batch of orders (also called a script) whenever you want. Of course, there's a catch: you don't get to issue your commands in plain English, you need to write them in the language of Apps Script.
This isn't just like computer programming; it is computer programming. Now, it's certainly possible to learn to program in Apps Script, but there is a learning curve and it takes some time to get proficient.
Google Sheets' new Macro Recorder makes this process dramatically easier. Instead of programming your commands in Apps Script, you demonstrate to Sheets what you want done and it learns how to do it. This is analogous to inviting your nephew into the kitchen to learn the secret family recipe and having him observe you, rather than writing down all the steps on a recipe card.
When Would I Use This?
Whenever you have a repetitive set of tasks that you need to apply to several sheets in a workbook, Macros are going to be a great help to you. The most likely example is with applying formatting changes.
Let's say you've exported an accounting-related spreadsheet to Sheets. It's boring and hard to read:
So you want to make some changes. You add subtotals. You add some bold type to make your headings stand out:
You add some borders. You use currency formatting.
You add alternating color formatting. You add another border.
Much Improved! Except, now you need to repeat all those tasks to each of the unformatted sheets in the workbook! Oh No!
The Macro Recorder in Sheets lets you record all of these actions as a script, which you can then apply each of the other spreadsheets in seconds. Here's a 5- minute video demonstration of how this works.
How to Use the Macro Recorder
The Macro Recorder can be found under the 'Tools' menu: Tools -> Macros -> Record Macro.
Once you've started recording, this dialog box will appear at the bottom of the page.
Go through and perform the actions that you're going to want to repeat later. The columns of totals, the bold type, the cell colors...do it all, one step at a time. Take your time...it doesn't matter how long it takes you to complete the tasks, Sheets will execute the macro as quickly as it possibly can, regardless. Once you're finished, click the 'Save' button in the macro recording dialog box. You'll be prompted to give it a name and you can even give it a keyboard shortcut, if you really want to:
Now you've got that batch of tasks saved as a macro. To actually apply them to another sheet, you'll want to navigate to your target sheet (it needs to be in the same workbook), then navigate to Tools -> Macros -> Your Macro's name:
The first time you run your macro, you may need to give it some permissions to run. This is fine.
Once you run the macro, Sheets will carry out your list of instructions as quickly as possible, usually taking only a few seconds:
Boom! Several minutes work, condensed down into less than 10 seconds? A Spreadsheet Sorcerer's Dream!
If You Want To Get More Intense...
One thing I really like about macros is that they can be a 'gateway drug' for learning to program in Apps Script. See, when you record a macro, the instructions are recorded as an Apps Script function, which you can view by going to Tools -> Script editor:
This will open up a new tab where you can see what your macro looks like in Apps Script code. Note that my script editor may look a little different because I use an extension called AppsScript Color that changes the color styling of the AppsScript window. HT to Mani for showing me this extension.
This may look like gibberish, but it's gibberish that you can learn to read. For instance, here's the command that bolds the text in the first column:
And here's where I turned on the alternating-color formatting to the different rows, then changed them to orange:
If you want to get started with Apps Script programming, you can record a macro, open up the script editor, then start editing the code. Then maybe adding a few new lines of your own code. You might run into a roadblock, something you don't know how to do, so you use the reference documentation to figure out how to get Sheets to bend to your will. And, before you know it, you're an AppsScript programmer!
Now, keep in mind that Apps Script is a powerful tool. While it can be used responsibly to automate tasks and ease your workload, Apps Script can also magnify mistakes and create huge headaches if you're not careful. Mani and I have both written tools in Apps Script (like FolderTron!) and I'll speak from experience that it takes a lot of patience to get a script working exactly the way you expect. So take your time and test your work. I'm reminded of this sign from another school's robotics lab:
Exactly
Even if you never touch the Script editor, though, I do encourage everyone to at least try recording a macro and then playing it back. It really can be a huge time-saver, especially if you spend a lot of your life in spreadsheets.