Google Sheets is quite powerful on its own already (with all those amazing formulas and functionalities).
But what makes it to the next level is that it now allows you can record and use macros in Google Sheets.
In this tutorial, I will cover all you need to know about recording and using macros in Google Sheets, as well as some examples where this can be useful.
But before we begin, let me quickly answer this basic question and get it out of our way.
What is ‘Macro’ in Google Sheets?
A macro is a piece is of code in the backend of Google Sheets (don’t worry, it isn’t complicated at all).
This macro (a piece of code) is a series of steps that you have defined, and as soon as you run this macro, it will automatically follow all these steps.
To give you an example, suppose you get a dataset where you have to do three things:
- Remove all the duplicate entries
- Remove all the extra spaces in between words
- Give a border to the entire dataset
Now you can do all these three things in Google Sheets manually (one step at a time).
But what if you have to do this over and over again every day or multiple times every day. In that case, you can quickly record a macro and automate these steps. So the next time you have a dataset, all you have to do is run the macro, and it will do these steps.
So let’s get started and record our first macro.
Recording a Macro in Google Sheets
Let’s record a simple macro in Google Sheets which will do the following:
- Select cell A1 in the worksheet
- Enter the text Hello in it
- Color the cell yellow
Here are the steps to record this macro in Google Sheets:
- Click the Tools tab.
- Hover your cursor over the Macros option, it will show some additional options.
- Click on Record Macros. This will enable macro recording and also show a dialog box.
- In the Macro dialog box, select the ‘Use absolute references’ option (this option is explained later in this tutorial).
The above steps start the macro recording. From this point onwards (until you stop the macro recorder), Google Sheets is going to keep a track of what all you do in it and convert your steps into a code in the backend.
Now since Google Sheets is recording every step of our work, let’s do the three things that we want to automate:
- Select cell A1 in the worksheet
- Enter the text “Hello” in it
- Give a yellow color to the cell (use Fill color option from the toolbar)
Once you have done these three things, click on the Save option in the Macro dialog box.
This will open the Save New Macro dialog box, where you need to specify the name of the macro. It’s advisable to keep it short yet descriptive enough so that you know what this macro does. In this example, I will name this ‘Color Hello cell’
Once you save the macro, it may take a few seconds to save it in Google Sheets. At this moment, Google Sheets is converting your macro into a Google App Script (something that Google Sheets understands).
Once it’s done saving the macro, you will see a notification in the bottom left of the Google Sheets document. It also shows the Edit Script option, and if you click on it, it will open the Google Apps Script editor and show you the code it has recorded.
Running the Macro in Google Sheets
Once you have recorded the macro, you can run it whenever you want and it will follow the steps you showed it while recording the macro.
There are a number of ways you can run a macro in Google Sheets:
- By using the Macro Option in the Tools tab
- By using a keyboard shortcut
- From the Google Apps Script Editor
- By Assigning it to a Shape
Let’s quickly go through each of these methods of running a macro in Google Sheets
Using Macro Options
Once you have recorded a macro, you can easily run it by locating it in the Tools tab.
Click on the Tools tab and hover your cursor over the Macros option.
In the additional options that appear, you would see all the macros listed at the bottom (after the first three options).
As soon as you click on any of the Macro names, it will be executed immediately.
Google Sheets is not as fast as other spreadsheet tools (such as Excel). So when you click on any of the macro names, it may take a few seconds to get it done. It also depends on the automation you’re trying to achieve with the macro. If there are many steps to it, it may take more than a few seconds.
Using the Keyboard Shortcut
When you are recording a macro in Google Sheets, it also asks you to specify a keyboard shortcut (optional step). This keyboard shortcut can be assigned in the dialog box where you’re giving you macro a name.
Once the keyboard has been set, you can simply use the shortcut and Google Sheets will instantly run the macro.
Again, it may take a few seconds depending on how many steps are there in the macro.
From Google Script Editor
When you record a macro in Google Sheets and save it, the steps are automatically saved in the Google Apps Script Editor.
Now if you want to execute a macro, you can also do this from the GAS editor.
To do this, you first need to open the GAS editor (by clicking on the Tools tab and then on Script Editor).
When the script editor opens, select the macro that you want to run (from the Select function drop-down) and click on the play button in the toolbar.
By Assigning the Macro to a Shape
You can also insert a shape in the worksheet and then assign your macro to that button.
To insert a button, click the Insert tab and then click on Drawing.
This will open the Drawing dialog box.
Click on the Shapes icon in the dialog box and insert the shape you want to use as a button to run the macro.
When you click on any shape, you will need to draw it in the area provided. Once you draw it and click on Save and Close, the shape will be inserted in the worksheet.
To assign a macro to this shape, select the shape and click on the three dots that appear at the top-right.
From the options that appear, click on ‘Assign script’.
The above steps will open the Assign Script dialog box, where you need to enter the name of the script that you want to run. Unfortunately, at the time of writing this tutorial, there was no way to get a list of all the names of macros right in this dialog box. You need to know the exact name and enter it here.
Now, whenever you will click on the shape, it will instantly run the macro.
Absolute Vs Relative Cell Referencing While Macro Recording
When you use a cell reference in Google Sheets, it can be absolute or relative.
- Absolute cell reference: $A$1
- Relative cell reference: A1
Absolute cell references mean that if you copy and paste these in formulas, these references will not change. These are absolute. For example, let’s say you have some values in cell A1:A10 and have the formula =SUM($A$1:$A$10) in cell B1.
This formula uses the absolute cell references. This means that if I copy and paste this formula from cell B1 to B2 (or any other cell in the worksheet), it will not change the cells it refers to.
On the contrary, let’s say I have the following formula in cell B1: =SUM(A1:A10). Since this uses relative cell references, when I copy and paste the formula from cell B1 to B2, the formula would change to =SUM(A2:A11).
This happens as using the relative cell reference does not lock the cell references and is relative to the position where it’s used. If I copy this formula to cell C1, it would change to =SUM(B1:B10). This is because I shifted the formula one column to the right (from B to C), the reference in the formula also shifted by one column.
Now coming to macros in Google Sheets, you can record a macro with Absolute or Relative cell references. This option appears as soon as you start recording the macro.
When you record a macro with absolute cell referencing, it will remember the cells or ranges you have selected and use the same cells when you run the macro. For example, if you record a macro to enter the text ‘Hello’ in cell A1, when you run this macro again, it will always go back to cell A1 and enter the text ‘Hello’ in it.
But with relative cell references, it remembers the position where you started and then moves relative to this position. For example, if I start recording the macro when I have cell B1 selected and I first select A1 and then enter the text ‘Hello’ in it, Google Sheet’s macro will remember that I moved one cell to the left.
So the next time you run this macro and have cell K1 selected, it will enter the text in cell J1 (which is one cell left to the selected cell).
Advantages of Using a Macro Recorder
Here are the advantages of using a Macro Recorded:
- Macro recorder gives you an easy to use interface that allows you to show Google Sheets the exact steps that you want to automate. Once you’re done, Google Sheets creates a code for the steps you followed. Now you can run the macro without ever touching that code.
- In case you’re creating a code yourself and feel stuck, you can use the Macro recorder and go through the code it creates. For beginners learning Google Apps Script, a macro recorder can be a great guide
What a Macro cannot do in Google Sheets
While macros are amazing, these have some limitations:
- Can’t create custom functions: One of the most frequent uses for me to use Google Apps script is to create custom functions. Unfortunately, you can’t do that with a macro recorder. To create a custom function, you will have to write the code manually and specify the kind of result you want that function to return.
- Can’t create a macro to run based on events: Suppose you want a macro to run whenever a user opens a workbook or selects a specific cell or makes a change in the worksheet, You can not do this using a macro recorder. These are events and since you want a macro to trigger when the event occurs, you will have to manually specify the trigger event and the macro code.
- Can’t run the macro without selecting the object: When you start recording a macro in Google Sheets, it simply follows all the steps you take and creates a code based on it. To do this, it’s necessary for you to show the macro the exact steps to follow. This means that the macro can only record what it sees on the screen, and can not go and do changes in the other sheets to documents that are not selected. You can, however, do this when you write the code manually.
- Can’t create loops: Let’s say you want a macro to loop through each cell in a range and delete row if the value in the cell is ‘No’ and keep it if it’s ‘Yes’. You can not do this with a macro. Sure there are longer ways to get this done (such as apply a filter and delete the cells), but you get the idea. With a macro recorder, you can not loop through cells, which you can do when you manually write the code.
- Can’t analyze conditions and take action based on it: With the macro recorder, since you can not loop through the cells in a range, it’s also not possible to analyze conditions and take actions based on it. For example, suppose I want to loop through all the worksheets in the Google Sheets document and get a list of all the worksheet names in the active sheet. This can not be done with a macro recorder and you have to write the code manually for it.