Understanding Macros and Apps Script

James MacAdam
Apr 5, 2020 · 3 min read

Enhance Google Sheets With Apps Script PART IV

Image for post
Image for post
Image for post
Image for post

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.

Image for post
Image for post

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.

Image for post
Image for post

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.

Image for post
Image for post

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.

Image for post
Image for post

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!

Image for post
Image for post

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.

macadamScripts

Sharing Ideas One at a Time

James MacAdam

Written by

A Civil Engineer by day who is passionate about others, sharing ideas, building relationships, and learning new things. Columbus, OH. macadamscripts.com

macadamScripts

Exploring Google Apps Script

James MacAdam

Written by

A Civil Engineer by day who is passionate about others, sharing ideas, building relationships, and learning new things. Columbus, OH. macadamscripts.com

macadamScripts

Exploring Google Apps Script

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store