How to Automate Google Sheets with Macros

Google Sheets allows users to automate repetitive tasks with macros and bind them to keyboard shortcuts for quick execution. Macros work by using Google Apps Scripts to record your activity for later use.

What are macros?

Macros or macro commands are a series of specific actions that allow you to automate a series of actions to increase productivity. They work by recording your actions and saving them to a file associated with the spreadsheet they record.

When recording a macro on Google Sheets, it automatically generates an Apps Script with all the code to replicate the action for you. This means you can create complex macros without knowing how to code. The next time you run it, Sheets will do everything you did when recording the macro. You’re essentially “teaching” Google Sheets how to manipulate documents with a single command.

Macros are a powerful feature that can do basically everything Sheets can do. Here are a few examples of its functionality:

• Apply formatting and styling.
• Create a whole new spreadsheet.
• Use any Google Sheets function, toolbar, menu, or feature.

How to record macros in Google Sheets

Open Google Sheet and click on Tools > Macros > Record Macro.

This will open the macro recording menu at the bottom of the window with two options for recording your actions:

Absolute References (Absolute reference): The macro will only perform actions on the exact cells that you record. If cell B1 is italic, the macro will only italicize cell B1 even if you click on another cell.

Relative References (Relative reference): The macro will perform actions on selected cells no matter where they are on the worksheet. If italicizes cells B1 and C1, you can use the same macro to italicize cells D1 and E1.

Select the option you want to use, then click, format, and teach Sheets the order of copying actions.

After recording the actions for the macro, click Save.

Enter a name for the macro, you can create shortcuts for up to 10 macros. If you want to associate a macro with a keyboard shortcut, press the number 0 to 9 in the empty box, then click Save.

If you need to edit the name or shortcut of the macro, you can edit the macro by clicking Tools > Macros > Manage Macros.

On the window that opens, adjust to your liking then click Update.

The next time you press the shortcut associated with the macro, it will run without having to open the macro menu from the toolbar.

How to Run Macros in Google Sheets

If your macro is an absolute reference, you can run the macro by pressing a keyboard shortcut or going to Tools > Macros > Your Macros and then click on the appropriate option.

If your macro is a relative reference, select the cell in the worksheet where you want to run the macro and then press the corresponding keyboard shortcut or click on it from Tools > Macros > Your Macros.

How to enter macros

As mentioned earlier, when recording a macro, it will link to the worksheet you recorded. But what if you want to import macros from another spreadsheet?

Since recorded macros are stored as functions in Google Apps Script, to import the macro you need to copy the function and then paste it into a new sheet’s macro file.

Open the Google Sheets with the macro you want to copy and then click Tools > Macros > Manage Macros. Next, click on the icon of More next to the macro you want to copy and then click Edit Script.

All macros are saved to the same file, so if you only want to use certain macros, you will have to filter them out. The name of the function is the same as the name you gave when creating the macro.

Select the macro you want to copy, then press Ctrl +. Make sure to copy everything in the function including the closing semicolon.

Now open another workbook, you will enter the macro and click on Tools > Macros > Record Macro.

Then you need to immediately click on Save without recording any action to create a placeholder function in that sheet’s macro file.

Click Save again.

Open Google Apps Script by clicking Tools > Script Editor, then open the macros.gs file from the left pane. Delete the existing function and then press Ctrl + DRAW to paste the macro from another Sheet.

Press Ctrl + WILL to save the script, close the tab and return to your spreadsheet.

Your spreadsheet will read the macros.gs file and look for the changes made. If you delete a new function, you can use the Import feature to add a macro from another sheet.

Next click on Tools > Macros > Import.

Finally, click on Add Function under the macro you want to add.

However you need to manually bind the macro to the shortcut again.

So you just know how to use macros in Google Sheets. Wishing you success!