The complete guide to simple automation in Google Sheets, using Macros

Macros are small programs you create inside of Google Sheets without needing to write any code. They allow you to automate repetitive tasks. They work by recording your actions as you do something and saving these actions as a “recipe” that you can re-use again with a single click.

Sound good?

They sure are! Read on to learn how to use them, see some examples, discover their limitations and also see how they’re a great way into the wonderful world of Apps Script coding 😀

This video is the third lesson from my free Apps Script Blastoff! course: an online course teaching Google Apps Script from scratch.

Contents

  1. What are macros?
  2. Why should you use macros?
  3. How to create your first macro
  4. Other options
  5. A peek under the hood
  6. More macro examples!
  7. Resources


1. What are macros?

Think of a typical day at work with Google Sheets open. There are probably some tasks you perform repeatedly, such as formatting reports to look a certain way, or adding the same chart to new sales data, or creating that special formula unique to your business.

They all take time, right?

They’re repetitive. Boring too probably. You’re just going through the same motions as yesterday, or last week, or last month. And anything that’s repetitive is a great contender for automating.

This is where macros come in, and this is how they work:

  • Click a button to start recording a macro
  • Do your stuff
  • Click the button to stop recording the macro
  • Redo the process whenever you want at the click of a button
  • Watch your favourite show with all the extra time you have*

* on second thoughts, perhaps omit this step 😉

They really are that simple.

^ Back to Contents

2. Why should you use macros?

There’s the obvious reason that macros can save you heaps of time, allowing you to focus on higher value activity.

But there’s a host of other less obvious reasons like: avoiding mistakes, ensuring consistency in your work, decreased boredom at work (corollary: increased motivation!) and lastly, they’re a great doorway into the wonderful world of Apps Script coding, where you can really turbocharge your spreadsheets and G Suite work.

^ Back to Contents

3. How to create your first macro

Let’s run through the process of creating a super basic macro, in steps:

1) Open a new Google Sheet (pro-tip 1: type sheets.new into your browser to create a new Sheet instantly, or pro-tip 2: in your Drive folder hit Shift + s to create a new Sheet in that folder instantly).

Type some words in cell A1.

2) Go to the macro menu: Tools > Macros > Record macro

Google Sheets macro menu

3) You have a choice between Absolute or Relative references. For this first example, let’s choose relative references:

Macro with relative reference

Absolute references apply the formatting to the same range of cells each time (if you select A1:D10 for example, it’ll always apply the macro to these cells). It’s useful if you want to apply steps to a new batch of data each time, and it’s in the same range location each time.

Relative references apply the formatting based on where your cursor is (if you record your macro applied to cell A1, but then re-run the macro when you’ve selected cell D5, the macro steps will be applied to D5 now). It’s useful for things like formulas that you want to apply to different cells.

4) Apply some formatting to the text in cell A1 (e.g. make it bold, make it bigger, change the color, etc.). You’ll notice the macro recorder logging each step:

Macro logging step

5) When you’ve finished, click SAVE and give your Macro a name:

Save macro

(You can also add a shortcut key to allow quick access to run your macro in the future.)

Click SAVE again and Google Sheets will save your macro.

6) Your macro is now available to use and is accessed through the Tools > Macros menu:

select macro menu

7) The first time you run the macro, you’ll be prompted to grant it permission to run. This is a security measure to ensure you’re happy to run the code in the background. Since you’ve created it, it’s safe to proceed.

First, you’ll click Continue on the Authorization popup:

Macro authorization

Then select your Google account:

Macro choose Google account

Finally, review the permissions, and click Allow:

Macro grant permissions

8) The macro then runs and repeats the actions you recorded on the new cell you’ve selected!

You’ll see the following yellow status messages flash across the top of your Google Sheet:

Macro running script

Macro finished script

and then you’ll see the result:

Macro result

Woohoo!

Congratulations on your first macro! You see, it was easy!

Here’s a quick GIF showing the macro recording process in full:

Recording a macro

And here’s what it looks like when you run it:

Run your macro

^ Back to Contents

4. Other options

4.1 Macro Shortcuts

This is an optional feature when you save your Macro. They can also be added later via the Tools > Macros > Manage macros menu.

Shortcuts allow you to run your macros by pressing the specific combination of keys you’ve set, which saves you further time by not having to click through the menus.

Macro shortcut

In the above example, I could run this macro by pressing:

⌘ + option + shift + 1

keys at the same time (takes practice 😉). Will be a different key combo on PC/Chromebooks.

4.2 Deleting macros

You can remove macros from your Sheet through the manage macros menu: Tools > Macros > Manage macros

Under the list of your macros, find the one you want to delete. Click the three vertical dots on right side of macro and then choose Remove macro:

remove macro

4.3 Importing other macros

Lastly, you can add any functions you’ve created in your Apps Script file to the Macro menu, so you can run them without having to go to the script editor window. This is a more advanced option for users who are more comfortable with writing Apps Script code.

import function to macro menu

This option is only available if you have functions in your Apps Script file that are not already in the macro menu. Otherwise it will be greyed out.

^ Back to Contents

5. A peek under the hood

Behind the scenes, the macro converts your actions into Apps Script code, which is just a version of Javascript run in the Google Cloud. If you want to take a look at this code, you can see it by opening the script editor (Tools > Script editor or Tools > Macros > Manage macros).

You’ll see an Apps Script file with code similar to this:

Essentially, this code grabs the spreadsheet and then grabs the active range of cells I’ve selected.

The macro then makes this selection bold (line 5), italic (line 6), red (line 7, specified as a hex color), font size 18 (line 8) and finally changes the font family to Montserrat (line 9).

The video at the top of this page goes into a lot more detail about this Apps Script, what it means and how to modify it.

Macros are a great first step into the world of Apps Script, so I’d encourage you to open up the editor for your different macros and check out what they look like.

(In case you’re wondering, the line /** @OnlyCurrentDoc */ ensures that the authorization procedure only asks for access to the current file where your macro lives.)

^ Back to Contents

6. More macro examples!

6.1 Formatting tables

Record the steps as you format your reporting tables, so that you can quickly apply those same formatting steps to other tables. You’ll want to use Relative references so that you can apply the formatting wherever your table range is (if you used absolute then it will always apply the formatting to the same range of cells).

Check out the video at the top of the page to see this example in detail, including how to modify the Apps Script code to adjust for different sized tables.

6.2 Creating charts

If you find yourself creating the same chart over and over, say for new datasets each week, then maybe it’s time to encapsulate that in a macro.

Record your steps as you create the chart your first time so you have it for future use.

The video at the top of the page shows an example in detail.


The following macros are intended to be copied into your Script Editor and then imported to the macro menu and run from there.

6.3 Convert all formulas to values on current Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will convert any formulas in the current sheet to values.

6.4 Convert all formulas to values in entire Google Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will convert all the formulas in every sheet of your Google Sheet into values.

6.5 Sort all your sheets in a Google Sheet alphabetically

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will sort all your sheets in a Google Sheet alphabetically.

6.6 Unhide all rows and columns in the current Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will unhide any hidden rows and columns within the data range. (If you have hidden rows/columns outside of the data range, they will not be affected.)

6.7 Unhide all rows and columns in entire Google Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will unhide any hidden rows and columns within the data range in each sheet of your entire Google Sheet.

6.8 Set all Sheets to have a specific tab color

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will set all of the tab colors to red.

Want a different color? Just change the hex code on line 5 to whatever you want, e.g. cornflower blue would be 6495ed

Use this handy guide to find the hex values you want.

6.9 Remove any tab coloring from all Sheets

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will remove all of the tab colors from your Sheet (it sets them back to null, i.e. no value).

Here’s a GIF showing the tab colors being added and removed via Macros (check the bottom of the image):

color tabs with Macros

6.10 Hide all sheets apart from the active one

Copy and paste this code into your script editor and import the function into your Macro menu:

Running this macro will hide all the Sheets in your Google Sheet, except for the one you have selected (the active sheet).

6.11 Unhide all Sheets in your Sheet in one go

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will show any hidden Sheets in your Sheet, to save you having to do it 1-by-1.

Here’s a GIF showing how the hide and unhide macros work:

hide unhide sheets with macros

You can see how Sheet6, the active Sheet, is the only one that isn’t hidden when the first macro is run.

6.12 Resetting Filters

Ok, saving the best to last, this is one of my favorite macros! 🙂

I use filters on my data tables all the time, and find it mildly annoying that there’s no way to clear all your filters in one go. You have to manually reset each filter in turn (time consuming, and sometimes hard to see which columns have filters when you have really big datasets) OR you can completely remove the filter and re-add from the menu.

Let’s create a macro do to that! Then we can be super efficient by running it with a single menu click or even better, from a shortcut.

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will remove and then re-add filters to your data range in one go.

Here’s a GIF showing the problem and macro solution:

Macro to reset filters

^ Back to Contents

7. Resources

Google blog post about Macros

Macro reference guide in Google Docs help

Macro reference guide in the Google Developer documentation

If you’re interested in taking things further, check out the following resources for getting started with Apps Script:

Apps Script Blastoff! — my free, introductory online course teaching Apps Script from scratch (including macros).

Beginner guide to coding with Google Apps Script

And if you want to really start digging into the Apps Script code, you’ll want to bookmark the Apps Script documentation for the Spreadsheet Service.

Finally, all of this macro code is available here on GitHub.

9 thoughts on “The complete guide to simple automation in Google Sheets, using Macros”

  1. Thank you for this sneak peak. I am looking forward to the entire course. If you don’t mind answering a question, I’d love to learn more.

    1. Is there a way to employ a macro (or app script) on any google sheet? Right now I only know how to copy and paste the script into my new spreadsheet where I then have to authorize all over again. By the time I complete those tasks I might as well have just performed the task by hand.

    Thanks again.

    1. Great question, Jason!

      Unfortunately there’s no easy way to utilize macros in different Google Sheets, except by copy-pasting them into the script editor.

      Per this forum discussion, you maybe able to create a library, but you’d still have to create a script and load the library, so doesn’t really save any time. Also, at the bottom of this article, it suggests you can’t do this anyway.

      Seems like this would be a killer feature though. FWIW, I’ll pass this idea on.

      Cheers,
      Ben

  2. Thank you, Ben. Great and useful examples.

    I’m looking forward to see your Apps Script Blastoff! course. I suppose some VBA background wouldn’t hurt, is it?

    I wish you all the good in the world for any of your courses.

  3. Hi Ben,
    I have a spreadsheet with formula being populated on all columns, and I am setting it using formula to appear depending on the date of the year. Which means at any one time, a column will have data populated.

    Is there a way I can edit the script for it to only convert the columns with data generated into values, while keeping the rest of the columns with data intact?

Leave a Reply

Your email address will not be published. Required fields are marked *