The best way to Automate Google Sheets With Macros

0
45

Google Sheets permits you to automate repetitive duties with macros, and you may then bind them to keyboard shortcuts to execute them rapidly. They work utilizing Google Apps Script to seize your actions for later use.

RELATED: Be taught The best way to Use Excel Macros to Automate Tedious Duties

What are Macros?

A macro—or macroinstruction—is a selected sequence of actions that allow you to automate a sequence of steps to extend productiveness. They work by recording your actions and saving them right into a file that’s certain to the spreadsheet during which they had been recorded.

Whenever you file a macro in Google Sheets, it routinely creates an Apps Script with all of the code to duplicate your actions for you. This implies you possibly can create advanced macros with out understanding the best way to write code. The following time you run it, Sheets will do the whole lot you probably did once you recorded the macro. Primarily, you’re instructing Google Sheets the best way to manipulate a doc to your liking with a single command.

RELATED: The best way to Supercharge Your Google Apps with the Script Editor

Macros are a robust characteristic that may do principally something Sheets is able to doing. Listed here are only a few examples of its performance:

  • Apply formatting and types.
  • Create utterly new spreadsheets.
  • Use any Google Sheets operate, toolbar, menu, or characteristic.

The sky is the restrict.

The best way to Document a Macro in Google Sheets

Hearth up a Google Sheet and click on Instruments > Macros > Document Macro.

Click Tools > Macros > Document Macro” width=”480″ top=”291″></p>
<p>This opens the recording menu on the backside of the window, with two choices for capturing your actions:</p>
<ul>
<li><strong>Absolute References: </strong>The macro will do duties solely on the precise cells you file. In the event you italicize cell B1, the macro will solely ever italicize B1 no matter what cell you clicked on.</li>
<li><strong>Relative References:  </strong>The macro will do duties on the chosen cells, no matter the place they’re within the sheet. In the event you italicize B1 and C1, you possibly can re-use the identical macro to italicize cells D1 and E1 later.</li>
</ul>
<p>Choose whether or not you need an absolute or relative reference, then you can begin to click on, format, and educate Sheets what order you need these actions to duplicate.</p>
<p><img class=

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

Click Save when you're done recording

Enter a reputation on your macro. Google additionally permits you to create shortcuts for as much as ten macros. If you need to bind a macro to a keyboard shortcut, enter a quantity from Zero-9 within the house offered. Whenever you end, click on “Save.”

Enter a name for your macro, then click Save

RELATED: The best way to Insert a Google Sheets Spreadsheet into Google Docs

If it is advisable to tweak your macro’s title or shortcut, you possibly can edit a macro by clicking Instruments > Macros > Handle Macros.

To create a keyboard shortcut, click Tools > Macros > Handle Macros” width=”531″ top=”314″></p>
<p>From the window that opens, tweak as desired after which click on “Replace.”</p>
<p><img class=

The following time you press the shortcut related to the macro, it’s going to run with out having to open the macro menu from the toolbar.

The best way to Run a Macro in Google Sheets

In case your macro is an absolute reference, you possibly can run the macro by urgent the keyboard shortcut or go to Instruments > Macros > Your Macro after which click on the suitable choice.

Click Tools > Macros > YourMacroName to run your macro” width=”650″ top=”332″></p>
<p>In any other case, in case your macro is a relative reference, spotlight the cells in your spreadsheet on which you need the macro to run after which press the corresponding shortcut, or click on on it from Instruments > Macros > Your Macro.</p>
<p><img class=

All macros save to the identical file, so when you’ve got a few macros saved, you might have to sift by them. The operate’s title is identical one you gave it once you created it.

Spotlight the macro(s) you need to copy, then press Ctrl + C. Make sure you copy the whole lot as much as and together with the closing semi-colon.

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

Now, open the opposite spreadsheet you’ll be importing the macro to and click on Instruments > Macros > Document Macro.

Create a new macro in your other spreadsheet. Click Tools > Macros > Document Macro” width=”479″ top=”244″></p>
<p>Instantly click on “Save” with out recording any actions to create a placeholder operate within the sheet’s macro file for us. You’ll be deleting this a bit of later.</p>
<p><img class=

Click on “Save” once more.

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

Open Google Apps Script by clicking Instruments > Script Editor, after which open the macros.gs file from the left pane. Delete the present operate after which press Ctrl + V to stick within the macro out of your different Sheet.

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

Press Ctrl + S to avoid wasting the script, shut the tab, and return to your spreadsheet.

Your spreadsheet reads the macros.gs file and appears for modifications made to it. If a brand new operate is detected, you should utilize the Import characteristic so as to add a macro from one other sheet.

Subsequent, click on Instruments > Macros > Import.

Click Tools > Macros > Import to import the macros to this doc” width=”478″ top=”282″></p>
<p>Lastly, click on “Add Operate” below the macro you need to add.</p>
<p><img class=

Sadly, you’ll have to bind the macro manually to a keyboard shortcut once more. Simply observe the instruction beforehand talked about, and also you’ll be all set to make use of this macro throughout a number of sheets.


That’s all there’s to creating and utilizing macros in Google Sheets. There’s no restrict to the routines you possibly can create on your spreadsheets to restrict the period of time you spend doing these repetitive duties.

Leave a Reply