Google Sheets Macros: Enable, Create & Use Them

If you use Google Sheets to store, format, and analyze data, then you might be looking for ways to automate manual tasks, so you can focus on higher-value tasks. Although most users turn to Google Sheets when they’re looking for an easier way to collaborate online, it has recently been leveling up to key competitors by offering automation tools that don’t require using code. We recently covered how to use macros in Excel, and now it’s time for Google Sheets!

In this article, you’ll learn what Google Sheets Macros are, how to enable this feature in Google Sheets, how to create your own macros, and how to apply them to your data by following practical Google Sheets Macros examples.

What are Google Sheets Macros?

Macros are small programs that you can create within Google Sheets. Since you don’t need to use code, it’s become a highly valuable feature to automate repetitive tasks, regardless of the user level. For example, if a regular task in your day-to-day is to format tables following a specific process, you can record this process using the Macros feature (see the last section in this article for more detailed examples).

There’s an obvious advantage - apart from saving you tons of manual work - and it’s that Google Sheets Macros can help you focus on higher-value tasks for your business. For example, you can use macros to automate advanced analysis or develop complex formulas. By recording tasks such as filtering or formatting data input, you’ll also be able to deliver more accurate and consistent information.

How to enable macros in Google Sheets?

Let’s say that a colleague has shared a Google Sheets file containing a recorded macro that you can use to speed up table formatting. You open your Google Sheets but don’t want to have to ask the obvious questions, “How do I find those macros in Google Sheets?”. This is how to enable macros in Google Sheets.

1. 1. Open the Google Sheets containing the recorded macro and head over to Extensions > Macros.

1. 2. You should see a list of macros. To enable any of them, click on it.

Tip: An alternative option to enable a macro is to press Ctrl + Alt + Shift + 1.

In case you haven’t found any list of macros, this means that there are no recorded Macros on that Google Sheet. Let’s see how you can create your own macros in a few simple steps.

How to create macros in Google Sheets?

1. 1. Open a new Google Sheet and head over to Extensions > Macros > Record macro.

Tip: To create a Google Sheet without going into your Drive, type “sheets.new” into your browser. If you are in your Drive, you can press Shift + s.

1. 2. In the box below, you’ll see two options: “Use absolute references” or “Use relative references”. If you intend to repeat the same calculation across multiple rows or columns, select the “Use relative references” option.

Tip: Use absolute references if you want to apply the macro to new sets of data that have the same range location every time you run it. Relative references are more commonly used when you need to apply formulas to cells. Take into account that the macro will apply to the cell where your cursor is at that moment.

1. 3. The macro recorder will show every action you take. Once you’ve finished your task, click “Save”.

1. 4. In the “Save new macro” dialog box, provide a name. Optionally, you can enter a number from 0-9 to create your own shortcut to run that macro. Click “Save” to finish.

1. 5. Head over to Extensions > Macros. To enable the macro, click on it.

1. 6. Google Sheets will ask for permission every time it runs a macro for the first time. Click “Continue”.

1. 7. Select the Google account you’d like to run this macro on.

1. 8. Click on “Allow” to confirm that you trust the recorded macro.

Now that you know how to enable and create a Google Sheets Macros, here are a few examples of how to use them in your daily tasks.

How to use macros in Google Sheets: Examples

So far, we’ve shown basic examples to illustrate how to enable and create Google Sheets Macros. However, you can automate these macros by using the Google Apps Script. This would be the equivalent of Excel Visual Basic for Applications (VBA). Don’t worry! There are ready-made codes for repeatable tasks, so you still don’t need to learn a single line of code.

How to use macros to sort all your sheets alphabetically?

If your Google Sheets is separated according to named tabs, you might find this code useful to quickly arrange alphabetically.

1. 1. Open Google Sheets and go to Extensions > Apps Script.

1. 2. A new tab will open in your browser. Copy and paste the following code onto a new line:

// sort sheets alphabetically
function sortSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var sheetNames = [];
  sheets.forEach(function(sheet,i) {
    sheetNames.push(sheet.getName());
  });
  sheetNames.sort().forEach(function(sheet,i) {
    spreadsheet.getSheetByName(sheet).activate();
    spreadsheet.moveActiveSheet(i + 1);
  });
};

1. 3. Don’t forget to save the script by clicking on the disk icon, as shown below.

1. 4. Go to Extensions > Macro Import, to import this function as a macro.

1. 5. Click on “Add Function” to add to your list of macros.

1. 6. Go to Extensions > Macros and click on the macro you just imported.

1. 7. Google Sheets will automatically sort your sheets in alphabetical order, as shown below.

And that’s it! You’ve automated your first Google Sheets Macros using Apps Script.

How to use macros to reset all filters?

Filters are commonly used to visualize and interpret your data better and more clearly. However, not everyone in your team might be interested in your filtering and need to remove these every time you share the data. This is how Google Sheets Apps Script and macros join forces to remove them in one go.

1. 1. Open Google Sheets and go to Extensions > Apps Script.
2. 2. A new tab will open in your browser. Copy and paste the following code onto a new line:

// reset all filters for a data range on current Sheet
function resetFilter() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.getFilter().remove();
  range.createFilter();
}

1. 3. Follow steps 3-6 from the previous example.

Like before, as soon as you run the macro, Google Sheets will automatically reset any filter you created in a matter of seconds.

Conclusion

While Excel offers powerful features to automate tasks, including Power Automate or Power Query, Google Sheets Macros now also provides powerful automation features. On top of this, Google Apps Script is the equivalent of VBA in Excel and has unlocked a greater range of tasks that can help you speed up more complex tasks.

You should now know what Google Sheets Macros are, how to enable them and how to create your own macros. You should also feel ready to start using Apps Script to create functions and import them to run as Google Sheets Macros.