HOW TO AUTOMATE DATA CLEANING IN GOOGLE SHEETS WITH MACROS

Introduction to Macros

Google Sheets lets you record macros that duplicate a specific series of UI interactions that you define. Once you've recorded a macro, you can link it to a keyboard shortcut in the form "ctrl+alt+Shift+Number". You can use that shortcut to quickly execute repetitive data cleaning steps again, typically in a different place or on different data. You can also activate the macro from the Google Sheets Extensions > Macros menu.

When you record a macro, Google Sheets automatically creates an Apps Script function that replicates the macro steps. The macro function is added to an Apps Script project bound to the sheet, in a file titled "macros.gs". In the event that there is already a project file bound to the sheet with that name, the macro function is appended to it. Google Sheets also automatically updates the script project manifest, recording the name and keyboard shortcut assigned to the macro.

Since every recorded macro is defined entirely within Apps Script, you can edit them directly within the Apps Script editor. You can even choose to write macros from scratch in Apps Script or take the data cleaning functions you have already written and turn them into macros.

Creating Macros in Apps Script

You can take data cleaning functions written in Apps Script and use them as macro functions. The easiest way to do this is by importing an existing function from the Google Sheets editor. However, is this does not work for you, you can still create macros in a couple of other ways. Let us take a look:

Coding Macros in Apps Script

1. In the Google Sheets UI, select Extensions > Apps Script to open the script bound to the sheet in the Apps Script editor.

2. Write the macro function. Macro functions should take no arguments and return no values.

3. Edit your script manifest to create the macro and link it to the macro function. Assign it a unique keyboard shortcut and name.

4. Save the script project. The macro is then available for use in the sheet.

5. Test the macro function in the sheet to verify that functions as intended.

Recording Macros in Sheets

1. In the spreadsheet menu, click Extensions > Macros > Record macro.

2. At the bottom, choose which type of cell reference you want your macro to use i.e. Use absolute references and Use relative references.

3. Complete the task you want to record. When you are done, click "Save".

4. Name the macro, create a custom shortcut, and then click "Save".

--- 

• • Use absolute references: The macro will do tasks on the exact cell you record. For example, if you bold content in cell A1, the macro will only ever bold content in cell A1 regardless of which cell you clicked.

  • Use relative references: The macro will do tasks on the cell you select and its nearby cells. For example, if you record bolding content in cells A1 and B1, the macro can later be used to bold content in cells C1 and D1.

Schedule Your Macro

You can set your macro to run based on actions, calendar updates, time intervals, or a chosen time and date.

1. On your computer, open a spreadsheet at sheets.google.com.

2. Click Tools > Script editor.

3. At the top, click Edit and then Current project's triggers.

4. At the bottom right, click "Add trigger" and select your options.

5. Click "Save".

Editing Macros

You can edit macros attached to a sheet by doing the following:

1. In the Google Sheets UI, select Extensions > Macros > Manage macros.

2. Find the macro you want to edit and select ⋮ > Edit macro. This opens the Apps Script editor to the project file containing the macro function.

3. Edit the macro function to change the macro behaviour.

4. Save the script project. The macro is then available for use in the sheet.

5. Test the macro function in the sheet to verify that functions as intended.

Importing Functions as Macros

If there is already a script bound to a sheet, you can import a function in the script as a new macro and then assign it a keyboard shortcut. You can do this by editing the manifest file and adding another element to the sheets.macros[] property.

You can also follow these steps to import a function as a macro from the Sheets UI:

1. In the Google Sheets UI, select Extensions > Macros > Import.

2. Select a data cleaning function from the list presented and then click "Add function".

3. Click the "close dialog" button.

4. Select Extensions > Macros > Manage macros.

5. Locate the function you just imported in the list and assign a unique keyboard shortcut to the macro. You can also change the macro name in this section and that name will default to the name of the function.

6. Click "Update" to save the macro configuration.

Best Practices for Using Macros

1. Macros are more performant when they are light-weight. Where possible, limit the number of actions a macro takes, especially given how intensive data cleaning functions can be.

2. Macros are best suited for rote operations that need to be repeated frequently with little or no configuration. For other operations, consider using a custom menu item instead.

3. Always remember that macro keyboard shortcuts must be unique, and a given sheet can only have ten macros with shortcuts at any one time. Any additional macros can only be executed from the Extensions > Macros menu.

4. Macros that make changes to a single cell can be applied to a range of cells by first selecting the full range and then activating the macro. This means it is often unnecessary to create macros that duplicate the same operation across a predefined range of cells.

Things You Cannot Do with Macros

• Use macros outside bound scripts: Macros are defined in scripts bound to specific Google Sheets but are ignored if defined in a standalone script or web app.

• Define macros in Sheets add-ons: You cannot distribute macro definitions using a Sheets add-on. Any macro definitions in a Sheets add-on project are ignored by users of that add-on.

• Distribute macros in script libraries: You cannot distribute macro definitions using Apps Script libraries.

• Use macros outside Google Sheets: Macros only work in Google Sheets and are not a feature in Google Docs, Slides or Forms.

Explore More

• How to Highlight Duplicates in Google Sheets

• Tips for Cleaning Data in Google Sheets

• How to Remove Duplicates in Google Sheets