How to Make Macros in Google Sheets

Macros in Google Sheets are a powerful and easy-to-use tool for recording and then automating repetitive tasks in a spreadsheet. Even better, they’re very easy to create and share.

Basically, macros in Google Sheets save a ton of time and tedium so you can draw insights from your data faster.

I dare say they even make spreadsheets more fun to use.

Macros are an essential tool for those of us who live in spreadsheets. Excel had macro support for years. Indeed, macros were one of Excel’s most powerful features.

Lack of macro support in Google Sheets kept many Excel users from switching. But by introducing macros in Google Sheets, the company is clearly committed to growing Google Sheets’ base of professional and business users.

How Macros in Google Sheets Work

Google explains it this way:

“Let’s say you need to format new data imports or build the same chart across multiple sheets of quarterly data. Repeating the same steps manually can take hours, but the Sheets macro recorder lets you record those actions and play them back on command without having to write any code.”

The new “record macros” feature sits in the Tools menu. It’s very easy to use. Via

1. On your computer, open a spreadsheet at sheets.google.com.
2. At the top, click Tools > Macros > Record macro.
3. At the bottom, choose which type of cell reference you want your macro to use:
   • Use absolute references: The macro will do tasks on the exact cell you record. For example, if you bold cell A1, the macro will only ever bold cell A1 regardless of which cell you clicked.
   • Use relative references: The macro will do tasks on the cell you select and its nearby cells. For example, if you record bolding cells A1 and B1, the macro can later be used to bold cells C1 and D1.
4. Complete the task you want to record. When you’re done, click Save.
5. Name the macro, create a custom shortcut, and click Save.
   • Note: When you create a macro in Google Sheets, an Apps Script is created. To edit this Apps Script, at the top, click Tools > Script editor.

To perform a macro, click Tools  Macros > the macro you want.

Macros in Google Sheets + Collaboration

Easy collaboration has always been one of Google Sheet’s most important features. True to form, macros in Google Sheet are positioned as a tool for teams and collaboration. As Google says on its blog, “After nearly 40 years, it’s time for teams to approach spreadsheets cloud-first.”

Groups of people can run macros simultaneously in the same Google Sheets file without interrupting each other. As Google notes,

“The best part about Sheets Macros is that they’re built for use in cloud-based files, which means that teams can run macros at the same time that others are working in the sheet without interrupting them. For example, a finance team having a budget meeting can run macros while reviewing the same spreadsheet. It also means that coworkers or clients won’t be forced to download sensitive files to use your macros. Since your Sheets files are in the cloud, you can keep tighter controls over who can view and re-share your data.”

Macros also guarantee process consistency. On a team where everyone might process spreadsheet data slightly differently, macros make it easy for everyone to follow an identical workflow.

Editing Macros in Google Sheets With Google Apps Script

When you record macros in Google Sheets, your actions are converted into a Google Apps Script. You can edit this script if you want to update your macro. (No need to re-record from scratch.) You can also write your own Apps Script functions and import them as new macros.

Edit your macros

1. On your computer, open a spreadsheet at sheets.google.com.
2. At the top, click Tools  Macros  Manage macros.
3. Next to a macro, click More . Rename or remove the macro.
4. When you’re done, click Update.

Import custom functions

You can import custom functions made with Google Apps script.

1. On your computer, open a spreadsheet at sheets.google.com.
2. At the top, click Tools  Macros > Import.
3. Next to the function you want to import, click Add function.

Further reading: Quickstart: Macros, Menus, and Custom Functions

Other Updates in Google Sheets

Macros in Google Sheets were the headline announcement for this most recent Sheets update, but several other powerful features were also rolled out:

• New chart types (waterfall and 3D)
• New ways to embed charts while keeping data up-to-date in Docs and Slides (even if you move files)
• More functions (up to 400+ now)
• The ability to add printing page breaks, custom paper sizes
• New options for row and column grouping
• A way to add checkboxes in cells.
• A new to group your data by time frame (like week, month or year) when you create pivot tables.

Regarding pivot tables, Google notes: “Our engineering team has also been hard at work bringing the power of Google’s artificial intelligence into Sheets to help teams know what their data knows. You might have noticed that we recently added intelligent pivot tables in Sheets to help analyze and find new insights.”

Further Reading

Think macro: record actions in Google Sheets to skip repetitive work

Quickstart: Macros, Menus, and Custom Functions

Techcrunch: Google Sheets Gets Macros

Forbes: Macros in Google Sheets