Understanding Macros and Apps Script
Enhance Google Sheets With Apps Script PART IV
What is a Macro?
Macros are small programs that a Google Sheets user can create to complete repetitive tasks. In fact, macros are an excellent way for a user to become familiarized with Apps Script, as a snippet of code is saved in the script editor each time a macro is recorded. Recording a macro is as simple as going to Tools → Macros → Record macro.
Record a Macro
In this example, let’s record a macro that highlights the cells of an employee row green and bolds the font of their name. After hitting Record macro, the recording macro box will pop up and you have the option to record the macro as absolute or relative. An absolute macro will complete tasks on the exact cells that you record, whereas a relative macro will complete tasks based on the cells you select. For this example, let’s record a relative macro.
Recording a macro is as simple as that! And now when you head over to the Script editor under Tools you will notice that there is now a macros.gs file, which contains the script of the macro that was just recorded. Each macro you record and assign a name to will appear as a function within the macros.gs file. Recording macros, studying the functions generated, and modifying the functions is a great way to learn the JavaScript language that Apps Script uses and how it works.
Use Recorded Macros
Using a previously recorded macro is as simple as clicking in the cell where you want the shortcut to be applied and navigating to Tools → Macros and selecting the macro that you want to run.
You’ll notice that I recorded two other macros to perform slightly different actions. Also, when naming a macro you can assign a numerical number as a shortcut to call the macro function by pressing Command+Option+Shift+number (Ctrl+Alt+Shift+number).
Recording macros and assigning shortcuts to them is a great way to execute repetitive tasks and become a more efficient Sheets user. You can also import macros from other workbooks so you can perform the same tasks and assign the same shortcuts across all of your workbooks.
I now can quickly color each employee of the Awesome Company based on how they are performing!
I encourage everyone to try recording a macro and applying it to their everyday spreadsheet use! I would love to hear about everyones favorite macro that they use!
Part III of this series described how to add to the user interface of a google spreadsheet and also how to cross reference spreadsheets by pushing data to and from other Google Sheets workbooks.
