Don’t forget to share this post

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.
Google Sheets Macros Enable Create Use Them Extensions Macros
Google Sheets Macros: Enable, Create & Use Them - Extensions > Macros
  1. 2. You should see a list of macros. To enable any of them, click on it.
Google Sheets Macros Enable Create Use Them Click on the macro to enable
Google Sheets Macros: Enable, Create & Use Them - Click on the macro to enable

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 Track Changes in Google Sheets
How to Track Changes in Google Sheets?

There are several ways to track changes in Google Sheets to make collaboration in your team even easier. Here'show to track changes in Google Sheets.

READ MORE

How to create macros in Google Sheets?

  1. 1. Open a new Google Sheet and head over to Extensions > Macros > Record macro.
Google Sheets Macros Enable Create Use Them Click on macro to enable
Google Sheets Macros: Enable, Create & Use Them - Click on macro to enable

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.
Google Sheets Macros Enable Create Use Them Use relative references
Google Sheets Macros: Enable, Create & Use Them - Use relative references

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”.
Google Sheets Macros Enable Create Use Them Save macro
Google Sheets Macros: Enable, Create & Use Them - Save macro
  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.
Google Sheets Macros Enable Create Use Them Name macro
Google Sheets Macros: Enable, Create & Use Them - Name macro
  1. 5. Head over to Extensions > Macros. To enable the macro, click on it.
Google Sheets Macros Enable Create Use Them Macros menu
Google Sheets Macros: Enable, Create & Use Them - Macros menu
  1. 6. Google Sheets will ask for permission every time it runs a macro for the first time. Click “Continue”.
Google Sheets Macros Enable Create Use Them Authorization required
Google Sheets Macros: Enable, Create & Use Them - Authorization required
  1. 7. Select the Google account you’d like to run this macro on.
Google Sheets Macros Enable Create Use Them Choose account
Google Sheets Macros: Enable, Create & Use Them - Choose account
  1. 8. Click on “Allow” to confirm that you trust the recorded macro.
Google Sheets Macros Enable Create Use Them Allow
Google Sheets Macros: Enable, Create & Use Them - Allow

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 Excel To Automate Tasks?

Macros are a powerful tool that can be used to automate tedious tasks in Excel. Here's how to use Macros in Excel.

READ MORE
How To Use Macros In Excel To Automate 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.
Google Sheets Macros Enable Create Use Them Apps Script
Google Sheets Macros: Enable, Create & Use Them - 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);
  });
};
Google Sheets Macros Enable Create Use Them Paste Sort Sheets code
Google Sheets Macros: Enable, Create & Use Them - Paste Sort Sheets code
  1. 3. Don’t forget to save the script by clicking on the disk icon, as shown below.
Google Sheets Macros Enable Create Use Them Save function
Google Sheets Macros: Enable, Create & Use Them - Save function
  1. 4. Go to Extensions > Macro Import, to import this function as a macro.
Google Sheets Macros Enable Create Use Them Import macro
Google Sheets Macros: Enable, Create & Use Them - Import macro
  1. 5. Click on “Add Function” to add to your list of macros.
Google Sheets Macros Enable Create Use Them Add Function
Google Sheets Macros: Enable, Create & Use Them - Add Function
  1. 6. Go to Extensions > Macros and click on the macro you just imported.
Google Sheets Macros Enable Create Use Them Click on imported macro
Google Sheets Macros: Enable, Create & Use Them - Click on imported macro
  1. 7. Google Sheets will automatically sort your sheets in alphabetical order, as shown below.
Google Sheets Macros Enable Create Use Them Sorted Sheets
Google Sheets Macros: Enable, Create & Use Them - Sorted Sheets

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();
}
Google Sheets Macros Enable Create Use Them Paste Code
Google Sheets Macros: Enable, Create & Use Them - Paste Code
  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.

How to automate Google Sheets tasks with Layer?

Layer is a collaboration platform for spreadsheets that works on top of your existing Excel files and Google Sheets. Share parts of your spreadsheet, collect and consolidate data, and review changes to make collaboration seamless and more efficient while keeping full control over your data. Using Layer, you can:

  • Manage Access: Give spreadsheet access on a tab or cell level to relevant stakeholders.
  • Collaborate: Automate tasks, set deadlines, and communicate on top of your files.
  • Review & Track: Consolidate input, track changes, and restore previous versions.

Sign up to get started with Layer right now or schedule an onboarding call so we can help you set up and share your spreadsheet flows.

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.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jun 22 2022