HOW TO AUTOMATE DATA CLEANING USING MACROS

---

INTRODUCTION TO MACROS

Automating repetitive data cleaning tasks saves time, reduces errors and ensures consistency, especially as your datasets grow.

Google Sheets allows you to record macros, which duplicate a specific series of user interface interactions.

Once recorded, you can link a macro to a keyboard shortcut like ctrl+alt+Shift+Number.

This shortcut lets you quickly repeat data cleaning steps. You can also activate macros from the Extensions > Macros menu.

When you record a macro, Google Sheets automatically creates an Apps Script function that replicates your steps. This function is added to a file named macros.gs within a script project bound to your sheet.

Google Sheets also updates the script's manifest file, recording the macro's name and shortcut.

Since every macro is defined in Apps Script, you can edit them directly in the script editor, write them from scratch or convert existing functions 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 is by importing an existing function from the Google Sheets editor.

If that method doesn't suit your needs, you can also create macros by coding them directly or recording your actions in the sheet.

Coding Macros in Apps Script

1. In the Google Sheets user interface, 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 it 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, that is, 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 perform 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 perform tasks on the cell you select and its neighbors.
  For example, if you record a macro on cell A1 and then run it on cell B1, it will perform the same actions relative to B1. This is generally more flexible.

HOW TO 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.

HOW TO EDIT YOUR MACRO

Once your macros are set up, you may need to adjust them as your data cleaning needs evolve.

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

1. In the Google Sheets user interface, 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 it functions as intended.

IMPORTING FUNCTIONS AS MACROS

In addition to recording and editing macros, you can also import existing functions to expand your automation toolkit.

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 user interface:

1. In the Google Sheets user interface, 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

• Macros are more performant when they are lightweight. Where possible, limit the number of actions a macro takes to finish, especially given how intensive data cleaning functions can be.
• Macros are best for simple operations that need to be repeated frequently with little configuration. For other tasks, consider using a custom menu item instead.
• 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.
• Macros that change a single cell can be applied to a range of cells by first selecting the full range and then activating the macro. This often makes it unnecessary to create macros that duplicate the same operation across a predefined range.

THINGS YOU CANNOT DO WITH MACROS

While macros are powerful, there are some important limitations to keep in mind.

• 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 application.
• 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.

BEYOND MACROS WITH AI-POWERED AUTOMATION

While macros are excellent for automating repetitive sequences of actions, Flookup takes automation a step further with its AI-powered capabilities.

These features can handle complex data cleaning tasks that are difficult or impossible to achieve with traditional macros, such as:

• Intelligent deduplication that understands semantic variations.
• Automated standardization of inconsistent data formats.
• Predictive completion of missing data based on context.
• Error prevention by identifying unusual patterns in real-time.

FOR THE VISUAL LEARNERS - COURTESY OF SAPERIS