Saltar al contenido

How to automate Google Spreadsheets with macros

How to automate Google Spreadsheets with macros

Google Spreadsheets lets you automate repetitive tasks with macros, and then you can connect them to keyboard shortcuts for quick execution. They work with Google Apps Script to capture your actions for later use.

What are macros?

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

When you register a macro in Google Spreadsheets, it automatically creates one Application script with all the code to reproduce your actions for you. This means that you can create complex macros without knowing how to write code. The next time you run it, Spreadsheets will do everything it did when you recorded the macro. Basically, learn Google Spreadsheets how to handle a document at will with a single command.

Macros are a powerful feature that can do just about anything a spreadsheet is capable of. Here are some examples of its functionality:

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

The sky is the limit.

How to record a macro in Google Spreadsheets

Launch a Google spreadsheet and click Tools> Macros> Record Macro.

Click Tools> Macros> Record Macro» class =»alignnone wp-image-413423 size-full» height =»291″ src =»https://www.howtogeek.com/wp-content/uploads/2019/05/2019-05-04_16h22_57.png?trim=1,1&bg-color=000&pad=1,1″ style =»chenar: 0px;  box-sizing: inheritance;  height: car;  maximum width: 650 px;» width =»480″></p>
<p style='background-color: white; box-sizing: inherit; color: #404040; font-family: Roboto, This opens the recording menu at the bottom of the window, with two options to capture your actions:

  • Absolute references: the macro will perform tasks only on the exact cells you are recording. If you enter the italic of cell B1, the macro will make italic B1, regardless of which cell you clicked.
  • Relative references: the macro will perform tasks on the selected cells, no matter where they are on the sheet. If you enter italics B1 and C1, you can reuse the same macro to run cells D1 and E1 later.

Select whether you want an absolute or relative reference, then you can start clicking, formatting, and handing out spreadsheets in the order you want these actions to be repeated.

Choose Absolute References or Relative References

Once you’ve captured all the actions for this macro, click «Keep».

Click Save when you have finished recording

Enter a name for the macro. Google also allows you to create shortcuts for up to ten macros. If you want to link a macro to a keyboard shortcut, enter a number from 0 to 9 in the space provided. When you’re done, click «Keep».

Enter a name for the macro, and then click Save

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

To create a keyboard shortcut, click Tools> Macros> Manage Macros» class =»alignnone wp-image-413484 size-full» height =»314″ src =»https://www.howtogeek.com/wp-content/uploads/2019/05/2019-05-06_15h42_36.png?trim=1,1&bg-color=000&pad=1,1?trim=1,1&bg-color= 000 & pad = 1.1″ style =»chenar: 0px;  box-sizing: inheritance;  height: car;  maximum width: 650 px;» width =»531″></p>
<p style='background-color: white; box-sizing: inherit; color: #404040; font-family: Roboto, From the window that opens, adjust as desired, and then click «Update».

Next to each macro, enter a number from 0 to 9 to connect to a shortcut

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

How to run a macro in Google Spreadsheets

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

Click Tools> Macros> YourMacroName to run the macros» class =»alignnone wp-image-413483 size-full» height =»332″ src =»https://www.howtogeek.com/wp-content/uploads/2019/05/2019-05-06_15h40_08.png?trim=1,1&bg-color=000&pad=1,1″ style =»chenar: 0px;  box-sizing: inheritance;  height: car;  maximum width: 650 px;» width =»650″></p>
<p style='background-color: white; box-sizing: inherit; color: #404040; font-family: Roboto, Otherwise, if your macro is a relative reference, highlight the cells in the worksheet where you want the macro to run, and then click the appropriate shortcut, or click it in Tools> Macros> Macros. your

If you created a relative referenced macro, highlight the cells, and then click Tools> Macros> YourMacroName to run the macro.» class =»alignnone wp-image-413486 size-full» height =»209″ src =»https://www.howtogeek.com/wp-content/uploads/2019/05/2019-05-06_15h54_48.png?trim=1,1&bg-color=000&pad=1,1″ style =»chenar: 0px;  box-sizing: inheritance;  height: car;  maximum width: 650 px;» width =»650″></p>
<h2 aria-level=How to import macros

As mentioned above, when you register a macro, it is linked to the worksheet in which you registered it. But if you want to import a macro from another spreadsheet? Although it is not a simple and easy task, you can do it with this little solution.

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

Open the Google Spreadsheet you want to copy, and then click Tools> Macros> Manage Macros.

Click Tools> Macros> Manage Macros» class =»alignnone wp-image-413484 size-full» height =»314″ src =»https://www.howtogeek.com/wp-content/uploads/2019/05/2019-05-06_15h42_36.png?trim=1,1&bg-color=000&pad=1,1?trim=1,1&bg-color= 000 & pad = 1.1″ style =»chenar: 0px;  box-sizing: inheritance;  height: car;  maximum width: 650 px;» width =»531″></p>
<p style='background-color: white; box-sizing: inherit; color: #404040; font-family: Roboto, Then click the icon «Which add» next to the macro you want to copy, then click «Edit the script».

Click the plus icon, and then click Edit Script

All macros are saved in the same file, so if you have several macros saved, you may need to review them. The name of the function is the same as the one you gave when you created it.

Highlight the macros you want to copy, then press Ctrl + C. Make sure you copy everything to the included semicolon.

Highlight and copy the macro function with Ctrl + C

Now open the other spreadsheet where you are importing the macro and click Tools> Macro> Register Macro.

Create a new macro in the other spreadsheet.  Click Tools> Macros> Record Macro» class =»alignnone wp-image-413493 size-full» height =»244″ src =»https://www.howtogeek.com/wp-content/uploads/2019/05/2019-05-06_19h01_00.png?trim=1,1&bg-color=000&pad=1,1″ style =»chenar: 0px;  box-sizing: inheritance;  height: car;  maximum width: 650 px;» width =»479″></p>
<p style='background-color: white; box-sizing: inherit; color: #404040; font-family: Roboto, Click immediately «Keep» without registering any action to create a placeholder function in the sheet macro file for us. You will remove this a little later.

Click Save

Click on «Keep» again.

You don't have to worry about its name, click Save

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

In the macros.gs file, paste the macro function from the first worksheet

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

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

Then click Tools> Macro> Import.

Click Tools> Macros> Import to import the macros into this document.» class =»alignnone wp-image-413492 size-full» height =»282″ src =»https://www.howtogeek.com/wp-content/uploads/2019/05/2019-05-06_16h23_03.png?trim=1,1&bg-color=000&pad=1,1″ style =»chenar: 0px;  box-sizing: inheritance;  height: car;  maximum width: 650 px;» width =»478″></p>
<p style='background-color: white; box-sizing: inherit; color: #404040; font-family: Roboto, Finally, click «Add a function» below the macro you want to add.

Finally, click Add Function next to the macro you want to add

Unfortunately, you will need to manually link the macro to a keyboard shortcut again. Just follow the instructions above and you are ready to use this macro on several sheets.


That’s all macros need to be created and used in Google Spreadsheets. There is no limit to the routines you can create for spreadsheets to limit the time you spend on these repetitive tasks.

.