How to automate Google Sheets with macros

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

1. 5 source to get macro to automate Excel spreadsheets
2. How to enable macros on Excel
3. How to turn on / off Macro in Word

What is a macro?

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

When recording macros on Google Sheets, it will automatically create an Apps Script with all the code to copy the action 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 recording the macro. Basically, you are "teaching" Google Sheets how to manipulate documents with a single command.

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

1. Apply formatting and styles.
2. Create a completely new spreadsheet.
3. Use any Google Sheets functions, toolbars, menus or features.

How to write macros in Google Sheets

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

How to automate Google Sheets with macros Picture 1

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

Absolute References : Macro will only perform tasks on exactly the cells you write. If it is italicized in B1, the macro will only be in italic B1, even if you click on another cell.

Relative References : Macros will perform tasks on selected cells regardless of where they are on the spreadsheet. If italics in cells B1 and C1, you can use the same macro to italic in boxes D1 and E1.

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

How to automate Google Sheets with macros Picture 2

After recording the actions for the macro, click Save .

How to automate Google Sheets with macros Picture 3

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

How to automate Google Sheets with macros Picture 4

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

How to automate Google Sheets with macros Picture 5

On the window that opens, adjust as you like then click Update .

How to automate Google Sheets with macros Picture 6

The next time you press the shortcut key associated with the macro, it will run without opening the macro menu from the toolbar.

How to run Macro in Google Sheets

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

How to automate Google Sheets with macros Picture 7

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

How to automate Google Sheets with macros Picture 8

How to import macros

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

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

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

How to automate Google Sheets with macros Picture 9

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

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

How to automate Google Sheets with macros Picture 10

Now, open another spreadsheet, you'll enter the macro and click Tools> Macros> Record Macro .

How to automate Google Sheets with macros Picture 11

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

How to automate Google Sheets with macros Picture 12

Click Save again.

How to automate Google Sheets with macros Picture 13

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 + V to paste the macro from another Sheet.

How to automate Google Sheets with macros Picture 14

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

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

Next click on Tools> Macros> Import .

How to automate Google Sheets with macros Picture 15

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

How to automate Google Sheets with macros Picture 16

However you need to link macros manually with the shortcut again.

So you just know how to use macros in Google Sheets. I wish you all success!