AUTOMATING DATA CLEANING TASKS 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.

SOURCE: Google. (2018). "Google Sheets Macros ". [Article] Google Workspace Learning Center. Available at: https://developers.google.com/apps-script/guides/sheets/macros [Accessed 17 March 2024]

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

Recording Macros in Sheets

--- 

Schedule Your Macro

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

Editing Macros

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

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:

Best Practices for Using Macros

Things You Cannot Do with Macros

For the Visual Learners - Courtesy of Saperis