How to Automate Google Sheets With Macros
Tips and Tricks

How to Automate Google Sheets With Macros

Google Sheets lets you automate repetitive tasks with macros, and you can then bind them to keyboard shortcuts to execute them quickly. They work using Google Apps Script to capture your actions for later use.

What are Macros?

A macro or macroinstruction is a specific sequence of actions that let you automate a series of steps to increase productivity. They work by recording your actions and saving them into a file that’s bound to the spreadsheet in which they were recorded.

When you record a macro in Google Sheets, it automatically creates an Apps Script with all the code to replicate your actions for you. This means you can create complex macros without knowing how to write code. The next time you run it, Sheets will do everything you did when you recorded the macro. Essentially, you’re teaching Google Sheets how to manipulate a document to your liking with a single command.

Macros are a powerful feature that can do basically anything Sheets is capable of doing. Here are just a few examples of its functionality:

  • Apply formatting and styles.
  • Create completely new spreadsheets.
  • Use any Google Sheets function, toolbar, menu, or feature.

The sky is the limit.

How to Record a Macro in Google Sheets

Fire up a Google Sheet and click Tools > Macros > Record Macro.

Click Tools > Macros > Record Macro” width=”480″ height=”291″/></p><p>This opens the recording menu at the bottom of the window, with two options for capturing your actions:</p><ul><li><strong>Absolute References: </strong>The macro will do tasks only on the exact cells you record. If you italicize cell B1, the macro will only ever italicize B1 regardless of what cell you clicked on.</li><li><strong>Relative References:  </strong>The macro will do tasks on the selected cells, regardless of where they are in the sheet. If you italicize B1 and C1, you can re-use the same macro to italicize cells D1 and E1 later.</li></ul><p>Select whether you want an absolute or relative reference, then you can start to click, format, and teach Sheets what order you want these actions to replicate.</p><p><img data-lazyloaded=

After you’ve captured all the actions for this macro, click “Save.”

Click Save when you're done recording

Enter a name for your macro. Google also lets you create shortcuts for up to ten macros. If you want to bind a macro to a keyboard shortcut, enter a number from 0-9 in the space provided. When you finish, click “Save.”

Enter a name for your macro, then click Save

RELATED: How to Insert a Google Sheets Spreadsheet into Google Docs

If you need to tweak your macro’s name or shortcut, you can edit a macro by clicking Tools > Macros > Manage Macros.

To create a keyboard shortcut, click Tools > Macros > Manage Macros” width=”531″ height=”314″/></p><p>From the window that opens, tweak as desired and then click “Update.”</p><p><img data-lazyloaded=

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 a Macro in Google Sheets

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

Click Tools > Macros > YourMacroName to run your macro” width=”650″ height=”332″/></p><p>Otherwise, if your macro is a relative reference, highlight the cells in your spreadsheet on which you want the macro to run and then press the corresponding shortcut, or click on it from Tools > Macros > Your Macro.</p><p><img data-lazyloaded=

All macros save to the same file, so if you have a couple of macros saved, you may have to sift through them. The function’s name is the same one you gave it when you created it.

Highlight the macro(s) you want to copy, then press Ctrl + C. Be sure to copy everything up to and including the closing semi-colon.

Highlight and copy the macro's function with Ctrl + C

Now, open the other spreadsheet you’ll be importing the macro to and click Tools > Macros > Record Macro.

Create a new macro in your other spreadsheet. Click Tools > Macros > Record Macro” width=”479″ height=”244″/></p><p>Immediately click “Save” without recording any actions to create a placeholder function in the sheet’s macro file for us. You’ll be deleting this a little later.</p><p><img data-lazyloaded=

Click “Save” again.

You don't need to worry about naming it, click Save

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

In the macros.gs file, paste the macro's function from the first spreadsheet

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

Your spreadsheet reads the macros.gs file and looks for changes made to it. If a new function is detected, you can use the Import feature to add a macro from another sheet.

Next, click Tools > Macros > Import.

Click Tools > Macros > Import to import the macros to this document” width=”478″ height=”282″/></p><p>Finally, click “Add Function” under the macro you want to add.</p><p><img data-lazyloaded=

Unfortunately, you will have to bind the macro manually to a keyboard shortcut again. Just follow the instruction previously mentioned, and you’ll be all set to use this macro across multiple sheets.

You might also like

How to Fix a “Your clock is ahead” Error in Google Chrome

How to Use Google Chrome Enhanced Privacy Settings

How to Download and Install Google Earth Pro on Windows PC?

How to Make a QR Code in Google Docs

Comments are closed.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. AcceptRead More