Macros in Google Sheets

What Is Macros in Google Sheets?

Google Sheets macros allow one to automate repetitive tasks within a spreadsheet. Most of us know what a macro is, but if you don’t, a macro is a series of actions that are recorded and can be replayed later to automate repetitive tasks. Users can record a sequence of actions—and replay those steps with a single click. Therefore, macros are essentially Apps Script functions that are automatically generated based on the actions you record.

For example, you may want to apply a specific number format to a particular column regularly. For this, you can record a macro step-by-step once and then run it whenever you need to apply the formatting. To do this, click on Extensions -> Macros -> Record macro.  Next, perform the steps for formatting. Save the macro with a name. This allows you to run the macro anytime from the same menu.

How To Create Macros in Google Sheets?

Let us look at the two methods of creating macros in Google Sheets.

1. Using the Google Sheets UI
2. Using Apps Script

Using the Google Sheets UI

Step 1: Navigate to the spreadsheet where you want to create the macro. In this example, we will insert a chart.

Step 2: To start recording, go to Extensions > Macros > Record macro. 

Step 3: You can choose whether to use absolute or relative references. Absolute references use specific cell coordinates, while relative references are based on the current selection. 

Step 4: Now, perform the actions you want to automate. Here, we are inserting a chart. You can also perform formatting and do other tasks.

Once you’re done recording, save the macro and assign a custom keyboard shortcut (e.g., Ctrl + Alt + Shift + number).

Step 6: Now you can run the macro. Press the assigned keyboard shortcut or find it under Tools > Macros.

Using Apps Script

Step 1: In a spreadsheet, go to Extensions -> Apps Script to open the Apps Script editor.

Step 2: Write the JavaScript code for your macro. For example, here, we create a function to format a header row: 

function makeHeadersBold() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn()); // First row

  headerRange.setFontWeight(“bold”);

}

Step 4: In the manifest (or manifest.json file) in the Apps Script editor, add your function to the addOn section. This creates the macro and links it to your function. 

Step 5: Save your script and run the formatHeaderRow function. 

You can access the macro in Google Sheets under Extensions > Apps Script > (your function name) or by assigning a custom keyboard shortcut. 

How To Run/Use Macros in Google Sheets?

UI Created Macro

1. Open the Google Sheet where the macro was created. Go to Extensions -> Macros.

2. You’ll see a list of your saved macros.

3. Click the name of the macro you want to run — it will automatically perform the recorded actions.

You can also assign a shortcut key when saving the macro for faster access.

For Macros Using Apps Script:

1. Open the required spreadsheet and go to Extensions > Apps Script.

2. Select the macro you want to run from the dropdown at the top.

3. Click the Run button.

4. Authorize the script the first time if prompted. Check the result. A chart is created.

How To Edit Macros in Google Sheets?

1. Open the Google Sheet containing the macro.
2. Go to Extensions > Apps Script.
3. In the script editor, look for the name you gave it.
4. Edit the code as needed.

How To Import A Macro in Google Sheets?

Let us follow these simple steps to import a macro into Google Sheets.

1. Open the Google Sheet where you want to use the macro. Click on “Extensions,” then “Apps Script.” It will open a new tab with the Google Apps Script editor.

If you’re importing from a file, first click the + icon next to “Files”` in the left sidebar. Then,  choose Script and paste the macro code there.

Save the script by using the option File -> Save and give the project a name.

Close the editor. Again, in the sheets, go to Extensions > Macros > Import macro.

You’ll see a list of all functions in the script. Select the macro you want to use.

Click Add function shown above to import it as a macro.

Examples

Let us look at some Google sheets macros examples to understand macros in detail.

Example #1 – Show All Hidden Columns in the Active Sheet

In this example, let us try to unhide all the hidden columns in an active sheet of a Google Sheet using a macro. For this, we can use the showColumns method. Let us look at a simple macro that achieves this:

function showAllHiddenColumns() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var lastColumn = sheet.getMaxColumns();

   for (var col = 1; col <= lastColumn; col++) {

    if (sheet.isColumnHiddenByUser(col)) {

      sheet.showColumns(col);

    }

  }

}

Explanation:

function showAllHiddenColumns(): This defines a new function named showAllHiddenColumns.

1. var sheet = SpreadsheetApp.getActiveSheet(): This gets the active sheet in the current Google Sheets file and stores it in a variable sheet.
2. var lastColumn = sheet.getMaxColumns(): This gets the total number of columns in the active sheet and stores the value in the variable lastColumn.
3. for (var col = 1; col <= lastColumn; col++): It starts a loop that goes through each column from 1 to the last column number.
4. if (sheet.isColumnHiddenByUser(col)): It checks if the column at the current position is hidden by the user.
5. sheet.showColumns(col): If the column is hidden, this line makes it visible.

Look at the steps below:

Step 1: Go to the Google Sheet which you must check the hidden columns for.

Step 2: Go to “Extensions” > “Apps Script.” Go to the next step.

Step 3: Copy – paste the code into a new script, in the Macros Apps Script editor. Save the script.

Step 5: Run the script by clicking on the “Run” button.

Step 6: Any hidden columns in the active sheet will now be unhidden. 

Example #2 – Highlight Rows with “Pending” Status

In this example, we have a table which consists of several tasks. Their status is either In progress, Completed and Pending. Let us identify and highlight all the rows which are pending.

Step 1: Enter the data in a sheet.

Step 2: Under Extensions -> App Script, enter the following code and save it.

function highlightPendingRows() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var range = sheet.getDataRange();

  var values = range.getValues();

for (var i = 1; i < values.length; i++) {

    var row = values[i];

    if (row.includes(“Pending”)) {

      sheet.getRange(i + 1, 1, 1, row.length).setBackground(“#FFA500”);

    }

  }

}

Here,

1. We define a new function named highlightPendingRows.
2. Next,  var sheet = SpreadsheetApp.getActiveSheet() gets the currently active sheet in the open Google Sheets document and stores it in a variable, sheet.
3. Next, we get the range of all cells that have data and store it in a variable called range.
4. We then start a loop in the second row as the index is 1.
5. var row = values[i] is used to store the current row of data from the values array.

Step 3: Run the code as shown in the previous example and check the result.

Important Things to Note

1. Remember that when you record a macro in Google Sheets, it automatically generates the script for the same. You can go to Extensions -> Apps Script and edit the same.
2. Macros are only sheet-specific. To run it for another sheet, you have to manually copy-paste the script.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand Macros in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Macros in Google Sheets. We learn how to create macros to automate tasks by recording or through App scripts with examples. You can learn more from the following articles. –

Stock Chart in Google Sheets

Themes in Google Sheets

CHISQ.DIST in Google Sheets