How to Use Google Sheets Macros

How to Use Google Sheets Macros

Macros allow you to automate repetitive tasks, such as removing duplicate data, formatting row and column sizes, and setting up tables. They also ensure that your work remains consistent and reduce the potential for human error.

If you want to learn how to work with Google Sheets macros, read on.

How to Create a Macro in Google Sheets

When creating a macro in Google Sheets, you’re technically just recording a series of steps and changes on your spreadsheet. These changes reapply when you run your macro at a later time.

To illustrate, follow the steps below to create a macro for removing duplicate entries in your data:

Go to Google Sheets, and open a spreadsheet with duplicate data. Navigate to “Extensions -> Macros -> Record macros.”

Opening the Extensions tab to record a macro in Google Sheets

Opt for “Use relative references” from the bottom menu.

Choosing relative references in Google Sheets macro

Highlight the column in which you want to remove duplicates. Choose “Data -> Data cleanup -> Remove duplicates.”

Using the

Configure your options, and click “Remove duplicates.”

Clicking the

Confirm by clicking “OK.”

Clicking the

Click “Save”to finish recording your macro.

Clicking the Save button to finish recording a macro

Name your macro in the “Save new macro”dialog box, then click “Save.”

Adding a name for a newly recorded macro and clicking the Save button

There are two ways to reference cells in Google Sheets: absolute and relative. When you use absolute references, your macro reapplies all the recorded steps to the same cells you used.

Meanwhile, relative references apply the changes, considering your currently selected cell(s). Let’s say you recorded a macro in cell B1. You can freely apply the same macro to other locations, like cells D1 and E999.

How to Use Macro Shortcuts

You can set custom keyboard shortcuts for your macros in Google Sheets. This option appears when saving a newly recorded macro. But if you forget to add one, follow these steps:

Go to “Extensions -> Macros -> Manage macros”on your spreadsheet.

Clicking the

Click on the provided number field in the dialog box for your macro, type any number from 0 to 9, and click “Update” to finish.

Creating a macro shortcut in Google Sheets

To run your macro, press Ctrl + Alt + Shift + [your chosen digit] on your keyboard. If you’re using Google Sheets on Mac, use this key combo instead: + Option + Shift + [your chosen digit].

Click “Continue”when asked to authorize.

Clicking the Continue button in the authorization request dialog box for macros

Choose your account, and click “Allow.”

Allowing permissions for a Google Sheets macro

Run your macro again by pressing the key combo.

Note that you can set a maximum of 10 shortcuts in Google Sheets. If you have more macros, you can manually run them by clicking “Extensions -> Macros -> [your macro].”

Running a macro manually via the Extensions tab

How to Edit Google Sheets Macros

Instead, you need to manually code them in the corresponding Apps Script file generated for your macro. Go through the steps below to find and edit the file:

Select “Extensions -> Macros -> Manage macros”on your Google spreadsheet. Click the three-dot icon beside your macro in the “Manage macros” dialog box.

Clicking the kebab icon to view more options for a macro in Google Sheets

Select “Edit script.”

Clicking

This will open an Apps Script tab, where you can make your changes to the existing code. Click the “Save project” button.

Clicking the Save button for a Google Apps Script file

If you’re unfamiliar with JavaScript, the official Google Sheets programming language, skip this step to preserve your macro and avoid unnecessary changes. Or, if you want to learn this language, check out these games to help improve your JavaScript skills.

How to Import Macros from Other Sheets

Importing your macros can drastically save you time and effort since you’re not recreating them from scratch. While they’re not globally available across spreadsheets, you can manually import them to another file by following these steps:

On your current spreadsheet, head over to “Extensions -> Macros -> Manage macros.” Click the three-dot icon for your macro, and choose “Edit script.”

In the script editor, highlight the function that you would like to import, do a right-click, then choose “Copy” in the context menu.

Copying a function from an Apps Script file

Close the tabs for your current spreadsheet and its script editor.

Go to another spreadsheet and create a new macro, then open the script file of your newly created macro as shown above.

In the new script editor, highlight and delete the existing code, right-click on the script editor, and choose “Paste.”

Pasting a function to an Apps Script file

Click “Save project” to save your changes. Go back to your spreadsheet, and choose “Extensions -> Macros -> Import macro.”

Importing a macro in the Extensions tab

Find your imported macro in the “Import”dialog box, and click “Add Function.”

Clicking the

Keep in mind that the “Import macro” option will only be clickable if there are functions in the Apps Script file of your spreadsheet that have not been imported. If you have already loaded all functions in the macro list, the option will be grayed out.

How to Delete Google Sheets Macros

You can also remove macros from your Google spreadsheet at any time. Deleting a macro would be good if it’s making some changes that mess up your calculations. Alternatively, delete them when they have served their purpose. It also opens up a slot for your macro shortcuts. Delete a macro by following the steps below:

Go to “Extensions -> Macros -> Manage macros.” In the “Manage macros” dialog box, click the three-dot icon for your macro, and select “Remove.”

Removing a Google Sheets macro

Click the “Update” button.

Updating the macro settings on a Google spreadsheet

Frequently Asked Questions

How do I create a script in Google Sheets?

The macro recorder in Google Sheets offers limited functionality. For more defined automation, like using custom functions, create a script with these steps:

Open a Google spreadsheet, and choose “Extensions -> Apps Script.” Delete any existing code in the script editor (on a new tab), add your code, and click “Save” to finish.

Can Google Sheets run VBA macros?

VBA macros don’t work in Google Sheets, as the platform uses Google Apps Script instead. But if you have access to Google Workspace Enterprise Plus and/or G Suite Enterprise for Education, you can convert your VBA macros to a compatible Sheets script using Macro Converter.

Image credit: Freepik. All screenshots by Princess Angolluan.

Leave a Reply

Your email address will not be published. Required fields are marked *