How to automate Google Sheets tasks with Macros

Do you get fed up with doing the same monotonous tasks over and over again on your spreadsheets? Well, there’s one thing that may save you time – Macros.

These record you doing actions on your sheets, then with a click of a button you can repeat those actions in seconds.

Here, we’re going to look at some examples of using Macros and you’ll see how easy they are to set up.

Example 1 – Formatting an exported sheet

First, we’re going to look at a common use of Macros. This is where a Google Sheet or an Excel document has been downloaded from somewhere but it needs formatting before it’s used or shared.

I have a colleague who had to download an Excel every Monday to then share with her boss, and she spent the first 10 minutes of every Monday downloading this and reformatting it. Then I showed her Marcos and it now takes a few seconds once uploaded to the Drive.

Here, we have a Google Sheet which contains some sales data. We’re going to go through the following steps:

1. Our Macro lives in a different Google Sheet, so we need to copy it to that Google Sheet.
2. We will set up the Macro the first time we do this.
3. For future documents, we just need to copy the document to the Google Sheet and run the Macro.

Copy the sheet to the Macro sheet

Here’s are sales data in the file called “Sales Export”.

Click on the tab, and select Copy to > Existing spreadsheet.

Click on Recent and select the file your Macro lives in. In this case the file called “3-Macros”.

You will then see a confirmation message and the option to open the Macro spreadsheet. Click on “Open spreadsheet”.

Now we’re in the “3-Macros” spreadsheet. You will see a new tab has been added. Click on that.

Record the macro

Click on “Extensions” and “Record Macro”.

A dialogue box will appear. There are two options – Use absolute references or relative references. Just leave it on the default absolute ones. It’s ready to record the actions you take on the sheet.

First, I’m going to delete the blank columns on the right-hand side of the sheet. Just select the columns, right-click and select “Delete columns X-X”.

Next, let’s add some alternating colours to highlight the rows better. Click on the top-left corner of the grid to select all the data.

From the “Format” menu, click on “Alternating colours”.

Select the style you want.

I’ve also aligned the first column to the right, added bolding tot eh top row, frozen the first row, and changed the Qty column to 0 decimal places. Plus, I changed the sheet name to “Sales Export”.

Basically, you can style it the way you want and in the background sheets is recording all the actions.

Once you’ve got the sheet the way you want it, click “Save” on the Macros dialogue box.

This will prompt you to give it a name, and you can also give it a keyboard shortcut. The shortcut has to be Command+Option+Shift (on a Mac) + a number 0 to 9. It’s optional but saves you having to run the Macro from the menu. Then click “Save”.

You will see a confirmation message and you have the option of editing the script which is where the actions are saved.

All the actions I did were converted into Apps Script and live in this Apps Script editor. If you don’t know anything about Apps Script you may wish to ignore this for now, but if you read it a lot of the steps you will understand and potentially you could edit. More about that later on.

Authorising the macro

When you run a the first macro you set up for the first time, you have to authorise it.

You will see the above notification. Click “Continue”.

Select the account you want to use, which is usually the one you’re currently using.

Then click “Allow”. If you are using a personal Gmail account instead of Google Workspace account you have to go through a couple of extra steps. See my post on authorising scripts for details on how to do that.

Running the macro

Now our Macro is ready to be run. Click on the “Extensions” menu > Macros > Tidy up imported sheet.

This will do all those actions in a few seconds and convert our unformatted sheet to a nice-looking readable one. We can import future sheets and it will do the same.

Example 2 – Hiding & showing tabs

Here, we’re going to set up a macro to hide certain sheets and then another to reset the sheet again.

In this file we have some sheets that are connected to parents evening appointment forms and the numbered sheets are connected to those sheets and show the appointments for the teachers.

I want to hide these form response sheets as the teachers won’t need them.

Then leave only these sheet visible.

Begin to record the macro from the “Extensions” > “Macros” > “Record Macro” menu. Then start recording the actions.

Click on the sheet tabs you want to hide and select “Hide sheet”.

Once you’ve done that for all the sheets, click “Save”.

Save the name of the macro and the shortcut and click “Save”.

When you run the macro, it will hide all the form response sheets and just leave the appointment ones. Often, when you’re hiding things, you want to create a macro that resets the sheet so you can see those sheets again.

So, this time we record a macro, but click on the hamburger menu to the left of the tabs, and click on the sheets in grey to show them again.

As before we save the macro and give it a different shortcut.

This will now give us a macro to hide the sheets and then another to show them all again.

Example 3 – Change tab colour

Here, we’re going to change the tab colour to green to show that they are ready. This is useful if we have a lot of tabs to colour and just saves us going into the menu, selecting “Change colour” and then picking a colour every time.

This time when we open the macro recording, click on the option “Use relative references”. We do that as we don’t want the same specific tab to be coloured every time, we want whatever tab we’ve selected to be coloured.

Record one being done by clicking on a tab, selecting “Change colour” and then the colour you want. Then Save the macro.

Give the macro a name and save it.

When you click on a tab, for speed I would use the keyboard shortcut and it will quickly change the tab to the colour you want.

Example 4 – Hiding and showing columns

In this example, I want to show you some of the limitations of Macros and why sometimes you might need to edit the code it creates.

We’re going to hide certain columns on the sheet – columns A, E, F, and L. We might want to do that so that someone can print out the relevant columns without having to manually hide the columns they don’t need.

Set up recording the macro and hide the columns you want.

Save it. So far, so good. When we run the macro it will hide the columns we want. Now, let’s create another which will reset the. sheet, i.e. show all the columns.

Start recording the macro and unhide the columns. Then save the macro.

Run the macro that hides the columns, then run the one that should reset the sheet. You may find you get this error message. Oh dear, what’s happened!? It’s saying something about the columns being out of bounds. Let’s investigate!

To access the macro code, we go to the “Extensions” menu and select “Apps Script”.

In the code you will see a function called “Showallcolumns” or whatever you called the last macro. Note, it creates a name without spaces.

We can see in the last line that it’s getting the active sheet and. showing the columns 0,1. This means it’s trying to show one column in column 0. Unfortunately, column 0 doesn’t exist and that’s why it says it’s out of bounds. Column A for example is column 1.

But we can correct this, just by changing the zero to a one, which will get column A.

We need to save the change by clicking on the save icon in the toolbar. Congratulations! You’ve just edited your first Apps Script!

Run the macro again and we can see column A is now visible. However, we have another problem, it hasn’t shown column K.

Go back into the Apps Script editor and you will see a line which refers to column 10, which is column J. That’s not the one we want to show, it’s column 11 (K).

That’s an easy change, just change the 10 to an 11 and save the code.

We run the macro again and it will show all the columns. This shows one of the limitations with Macros, in that sometimes the references. aren’t correct and sometimes happens at the extremes of our data, for example the first and last column.

As you can see, even. if it doesn’t work. exactly how you wanted it, you can go in and edit the code to correct it.

Example 5 – Create and format a chart

It’s not just changes to the sheets we can do, we can create macros for all sorts of actions. In this last example, we’re going to create and format a chart, and you will see how quickly it creates them.

Start recording the macro and then select the data you want to use for the chart.

Click the create chart icon.

This creates the chart. Now let’s format it.

Im going to change it to a line chart, add a title, and thicken the line.

And finally, I’m going to move it to its own sheet.

Save the macro and when you run it, it will get the data, create the chart and format it in a few seconds. In the future, you can select new data (note in this example it will get the same range although it could be from a different sheet) and it will make a chart for that.

Further thoughts

Hopefully, you can see that you can quickly set up macros, which will automate some of your workflows.

The Macros you make live in the spreadsheet you created them in. This is why in the first example we had to import the sheet into another file.

If you make a copy of a sheet which contains macros, those macros are also copied. You will have to authorise the script again when using them the first time.

Managing macros

You can edit and delete macros.

To do this, go to “Extensions” > Manage macros”.

From here you can edit the macro names, change the keyboard shortcuts, access the scripts, or delete the macros. Just click “update” once you’ve made your changes.

Macros and Apps Script

They are also a good first step into the wonderful world of Apps Script, which is the code that sits behind Google Sheets and many of the other Google Apps, and which can automate your work even further, allowing you to connect the apps. If you’re interested I highly recommend my book “Beginner’s Guide to Apps Script 1 – Sheets” as a first step. You can also find examples of Apps Script on the Apps Script page of this site.

Even if you know some Apps Script already, it can be a useful starting point to save you having to create the code from scratch. For example, the code that makes the chart is quite long, and that would take me a while to put that together, whereas I could record a macro then edit the code to what I wanted, which would be much quicker.

Plus, it gives you opportunities to learn new ways to use Apps Script. You will notice that the script that is produced by a macro isn’t optimised and often it can be shorted or changed to run quicker.

If you’ve written a script, you can also import that as a macro and add it to the macros menu, by going to Extensions > Macros > Import macro.