In the course of processing and analyzing data in Google Sheets, you will find yourself doing a set of repeated actions, clicking on the different options in the toolbar and the main menu. You can automate them by recording a macro. Macros are a programmed set of actions you do in Google Sheets. Recording macros will help you save time and streamline your workflow.
These macros are stored using the Google Apps Script, so if you have some knowledge of how Google Apps Script works you can edit the macro you created later on.
The macros in Google Sheets can be accessed under the Extensions option in the main menu. Click Extensions, select Macros, then choose Record macro.
The following small box will appear on the lower-center portion of your sheet. Once it appears, any action you make that changes the sheet will be recorded, so make sure to rehearse what you want the macro to do.
For our example, we want to insert two columns with their respective formulas and then add a header color.
You can program the macro such that Google Sheets will apply the changes using the absolute references or the relative references.
Use absolute reference if you want the actions you program to be applied on the same exact cells every time. This is best done when you want to apply the changes once to new sheets; for example, adding header color, adding a column with a formula applied to all rows, etc. This is not recommended if you intend to apply the macros repeatedly whenever new rows are added.
Use relative reference if you want to specify later on where in the sheet to apply the changes. Google Sheets does this by recording the location of the cells you modified adjacent to the first cell you select when recording the macro. This gives you flexibility to apply the macro anywhere in the sheet. However, make sure that, when you choose this option, you select the right cell when you start recording the macro.
For our example, we want to insert two columns with their respective formulas and then add a header color, so we will select Use absolute references.
Once the macro box appears, it will start recording any action you make that modifies the sheet.
For our example, here are the following actions we want to program:
1. Add new column headers total cost and profit to cells G1 and H1, respectively. The macro box will change, showing the action number and the nature of the action. They will show up as Set value actions.
2. Add formulas for each column, and then autofill. The action of adding the formula will be recorded as Set formula in the macro.
Whenever you add a formula to a cell for the first time, Google Sheets automatically scans the sheet if the cell is part of a table with multiple rows of data. If that’s the case, it suggests an autofill action. Click the check button to Autofill the column.
The autofill is then recorded as Autofill range macro action by Google Sheets.
Do the same for each of the columns you added.
3. Fill color the header row. To add the header color, select the cells in the header row you want to color, then select the Fill color option in the main toolbar. A color palette will load. Select the color you want to use.
The header row becomes colored:
This action is recorded to the macro as Set format style.
Once you have done all the steps you want to program to the macro, click Save in the macro box.
A box labeled Save new macro will appear. Set the name of the macro by typing in the textbox labeled Name.
You can also set a keyboard shortcut for the macro. The shortcut takes the format
Ctrl+Alt+Shift+Number
Where you can specify the Number to append to the keyboard shortcut. For our example, we will set it as
Ctrl+Alt+Shift+1
This action is optional.
Once you are done, click Save. A notif saying Saving new macro will appear
A message saying Macro saved will appear.
You can now use the macros you created!
The list of macros you created will appear. Select the one you want to run.
If it is the first time you run the macro, you will be asked for authentication to run the associated script. Click OK.
A new window will load. You will be asked to choose an account to run the macro. Select the listed account.
You will then be asked to grant the required permissions to run the macro. Click Allow.
You have now authorized the macro to run in your spreadsheet. Run the macro again from the Extensions menu. The macro will finally run, modifying the active sheet!
You can edit the actions programmed in the macro by editing its associated Google Apps Script. Here are the steps:
To edit macros, click Extensions, select Macros, then choose Manage macros.
A small box labeled Manage macros will load.
For each macro saved in the spreadsheet, there are three dots on the right of the list. Click it. Select Edit script.
Edit the script with the changes you need to make. Once you are done, click the Save button on the main toolbar.
To import a macro, you need the Google Apps Script, then convert it to a macro. Here are the steps:
Find the script of the macro you want to copy. For example, you want to copy the headerformat macro from the following link:
https://github.com/rvbautista/google-sheets-macros/blob/main/headerformat.gs
Copy the code.
In the Google Sheets where you want to use the macro, click Extensions then select Apps Script.
A new tab will load for Google Apps Script. Clear the code area then paste the code. You can also name the apps script project.
Click Save.
To convert the script to a macro, click Extensions, select Macros, then choose Import macro.
A box labeled Import will appear. Find the function you added, then click Add Function below it. In our example, the function is named headerformat. Click the Add Function below it.
The Add Function becomes a check icon.
The function is now listed as a macro!
We have prepared a set of common macros you can import to your Google Sheets! Follow the steps in importing macros in Google Sheets, and you are good to go!
