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.
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.
Once you’ve captured all the actions for this macro, click «Keep».
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».
If you need to change the name or shortcut of your macro, you can edit a macro by clicking Tools> Macros> Manage Macros
From the window that opens, adjust as desired, and then click «Update».
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.
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
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.
Then click the icon «Which add» next to the macro you want to copy, then click «Edit the 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.
Now open the other spreadsheet where you are importing the macro and click Tools> Macro> Register Macro.
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 on «Keep» again.
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.
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.
Finally, click «Add a function» below 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.