To Google Sheets! The cloud-based cause of, and solution to, all my work-related problems.
It’s time again to bask in the glory of the macro. In my last post, How Excel Macros Save Digital Marketers Time, we discussed the various purposes and implementation methods of macros in Excel. In this post, we’ll be talking about the macro’s cooler, more modern brother, the Google Sheets macro, hosted by Google Apps Script.
I’ll preface this post by saying that Google Apps Script is a beast of a service that covers almost any automated process you’d want to complete in G-Suite. There are a near-infinite number of uses and processes that one can dig into in the software, making holistically understanding it a herculean feat. I don’t claim to be an expert on App Script or it’s uncountable uses, but if you want to learn more about what it can do for you, it’s probably best to hear it straight from the horse’s mouth, Overview of Google Apps Script.
Before we get started if you’re not exactly sure what I’m talking about when I say “macro”, a post by Rachael Law on Excel Macro basics will easily get you up to speed. With that said, those of you still here are ready to start your journey into the closest thing to coding I’ve ever attempted to understand.
The way I’ve used macros in the past is to sort data, format sheets, and clean up reports so they are ready for a client. The App Scripts can do all that and more, from writing and using custom functions to interacting with Google Analytics and Google Ads. Let’s walk through an example of how to interact with App Script’s macro interface. For this visual aid, I’ll be using a set of data from a debt relief company.
Setting Up A Macro In Google Sheets
First, open your Google Sheet. Right off the bat, we’re going to encounter a difference between Sheets and Excel. In Excel, you’d have to enable Developer Mode in the “Preferences” and access the macro through the newly created “Data” tab, but not in Sheets. Here, the macro and its controls are already in the “Tools” tab without us having to edit any settings.
There are three ways we can create a macro from here:
- Record a new macro with the “Record” feature
- Copy/Paste a macro’s script from “Microsoft Visual Basics” (Excel’s Apps Script)
- Import a script previously saved to your Google account
We’re going to focus on the first method, but I’m toying with the idea of including a walkthrough of the latter two in a future post.
So, press “Record” and we’re off. Right away, we’re faced with a choice between absolute reference and relative reference. An absolute reference is fixed to the exact location of recording (a specific cell) while relative reference begins the sequence from the cell currently highlighted by the user. Each has its place, but for the example, we’re going to use absolute reference.
The macro is now recording, so all interactions you make with the sheet will be remembered and added to the list of executable actions. Not to worry though, because if you make a mistake, you can always go in and edit the script.
Let’s begin our on-sheet actions by highlighting and bolding the top line of our data. As we highlight and bold the top row, we see that “Recording new Macro” momentarily becomes “Action 1: Set format style”. This means the action has been recorded and added to our list of executable items.
Now that we have a script to examine, let’s run through what editing it in the “Script Editor” looks like.
We can see a very simple list of what we just recorded on our sheet. Let’s go through it line by line.
- function Macro1 () – what I named the macro
- Var spreadsheet = SpreadsheetAPP.getActive () – indicating that this macro will run whenever selected on the active sheet
- getRange (‘A1:C1’) – the active cells in the sheet we executed the macro in
- setFontWeight (‘bold’) – the action of “bolding” the cells the macro selected
With this information, we can edit what happens to the cells inside the script editor without creating a new macro. For example, we could change line three to select only cells A1-B1 and we could change the setFontWeight to “italics” or “strikethrough”. This way, we don’t need to recreate a large macro, we can just edit the script itself.
That’s all the information I have for you today, but stay tuned for future updates on the exciting world of making your job easier, more efficient, and as automated as possible with the use of scripts and macros.
