As the title of today’s topic suggests, we are going to learn how to use Google Sheets to record and then re-use the macros. There are two types of macro recording;
- Using absolute references
Use absolute references if you want to apply the macro to new sets of data that have the same range location every time you run it.
- Using relative references
Relative references are more commonly used when you need to apply formulas to cells. Take into account that the macro will apply to the cell where your cursor is at that moment.
Let’s see how it can be done by following these steps.
Google Sheets Macros are small pieces of App Script Code generated automatically by Google Sheets without you knowing anything about App Script Coding. The idea is pretty much similar to Excel’s macros, which are used to automate repeatable tasks. The Google Sheets macros work by recording your actions as you do something and saving these actions as a “piece of code” that you can re-use or re-run again with a single click.
Step 1 – Locate the Record Macros Options in Extensions Tab
– On the main list of the tabs, go to the Extensions tab.
– Click on the dropdown arrow.
– Locate the Macros option and then you will see the Record macro option.
Step 2 – Record the Macros using default options
– Let’s assume that we wish to change the fill color of the consignment IDs column data and we want to record a macro to repeat this action. For this purpose, click on the record macro.
– By default, “using absolute references” will be selected. Keep that option ticked.
– Perform the desired action. When you are done with the desired actions, click on Save.
– The macro will be recorded and Google Sheets will generate a piece of code for your action.
– A new dialog box will appear and ask you to assign a name to the recorded macro. Choose an appropriate name e.g., First macro.
– Here we’ll also see the option to assign a shortcut key to run the macro. The combination of CTRL+ ALT + SHIFT + any desired number will trigger the recorded macro. We’ll select 1 and click save. So, our shortcut key will be CTRL+ ALT + SHIFT + 1.
Step 3 – Run the recorded macro
– To run the macro, go to the Extension tab.
– Click on Macros and then you will find the name of your recorded macro in the opened list.
– Click on that name. Alternatively, you can press the shortcut key that you defined while recording the Macro which was CTRL + ALT + SHIFT + 1.
– Google will ask you for some permissions. Go through the procedure and don’t worry it is the standard procedure of Google to ask permission when it runs the macro for the first time.
– After allowing all permissions, run the macro again and you will see the macro in effect as shown above.
So this is how you can record and then re-run the macros in Google Sheets.