Automate Google Sheets using Macros

A macro is one of the simplest ways to automate repetitive tasks in Google Sheets. Using a macro, you can record a set of actions and then reuse them in the future with just a single click. Macros enable you to harness the power of Google Apps Script even if you do not know how to code!

For example, let's imagine that you are building a simple compound interest calculator in Google Sheets. The calculator accepts three parameters as input: principal amount, time period, and interest rate as input. The output is the final amount at maturity.

You're about to share this spreadsheet with your friends and family but you want to first format the input cells and output cells clearly so they'll know which cells they can tweak based on their needs.

You come up with the following convention:

• The input cells will have a light yellow background and input values will be dark gray in color and emboldened.

• The output cell will have a light green background and the output value will be dark green in color and emboldened.

Since there are three input cells, you'll have to apply the same set of formatting rules three times. Instead of repeating the same set of actions multiple times, you can record these actions as a macro the first time and then apply them going forward with a single click.

In this tutorial, I will show you how to record and use macros in Google Sheets.

How to record a macro in Google Sheets?

Step 1 — First select an input cell or range that you will format manually

Step 2 — Begin recording a macro

Step 2.1 — Select Tools —> Macros —> Record macro

Step 2.2 — Choose absolute references OR relative references

Use absolute references if the macro only applies to the currently selected cell or range. If you record a macro using absolute references, running the macro will only make changes to those cells (or ranges) that were selected when the macro was recorded.

Use relative references if you want the macro to apply to whichever cell or range is selected at the time the macro is run.

In this tutorial, we will use relative references since we want the macro to apply formatting to whichever cell is currently selected.

Step 3 — Apply formatting changes to the cell while the macro is being recorded and then save the macro

How to use a previously recorded macro in Google Sheets?

To apply a previously recorded macro to a cell or range, select it and then select the macro from the Tools —> Macros menu.

Step 1 — Select the cell or range you want to apply the macro to

Step 2 — Select the macro from the Tools —> Macros menu

That's it! The cell or range you selected will be formatted by the maco. All it requires is a single click.

Macros are essentially just Google scripts that are auto-generated for you

When you record a macro, Google Sheets is essentially creating a Google Apps Script for you based on the actions that you took.

To view the auto-generated code, select Tools —> Macros —> Manage macros and then select Edit script from the three-dots menu.

Here is the code that was auto-generated for the macro we recorded in this tutorial:

/** @OnlyCurrentDoc */


function Formatinputcell() {
 var spreadsheet = SpreadsheetApp.getActive();
 spreadsheet.getActiveRangeList().setBackground('#fff2cc')
 .setFontWeight('bold')
 .setFontColor('#434343');
};

Conclusion

In this tutorial you learned how to use macros to automate repetitive tasks in Google Sheets. You don't even have to write Apps Script code! Simply begin recording a macro, perform a set of actions manually and save the maco. That's it! You can then apply those same set of actions in the future by running the macro that you recorded.

Macros are essentially just Google scripts behind the scenes so you can use them to learn how to implement some functionality in Apps Scripts. Simply record a macro and then look at the code that was auto-generated for you.

Macros are a powerful and underutilized tool in Google Sheets. You can get the benefit of Apps Scripts without even knowing how to code!

Hope this tutorial gave you ideas for things you can automate to become more productive in Google Sheets. Thanks for reading!