How to Use Google Sheets Macros to Automate Tasks

The Data Formatting Grind

If you work in accounting or operations, you likely start your morning by downloading a raw CSV file from your company’s database. When you open it in Google Sheets, it looks terrible. The columns are the wrong width, the dates are formatted incorrectly, the top row isn’t bolded, and the numbers lack currency symbols.

You then spend the next ten minutes manually resizing columns, clicking the bold button, freezing the top row, and applying number formats. You do this exact same sequence of clicks every single morning, five days a week, fifty weeks a year. That is over 40 hours of your life wasted on repetitive formatting.

You do not need to learn how to write complex code to automate this. Google Sheets has a built-in feature called a Macro Recorder. It literally acts as a video camera for your mouse. It records you performing the formatting steps once, translates those clicks into Google Apps Script code behind the scenes, and allows you to replay the entire sequence with a single keyboard shortcut.

Recording Your First Macro

Start with your ugly, raw data open in Google Sheets.

1. Go to the Extensions menu at the top of the screen.
2. Hover over Macros and click Record macro.

A small, floating “Recording new macro…” box will appear at the bottom of your screen. From this exact moment, every single click you make is being recorded.

The Two Reference Types

Before you start clicking, look at the two options in that floating box:

• Use absolute references: Choose this if you want the macro to always perform actions on the exact same cells (e.g., “Always make cell A1 bold”).
• Use relative references: Choose this if you want the macro to act based on wherever your mouse currently is (e.g., “Make whatever cell I have clicked on bold”). For formatting a standard report, Absolute is usually the safest choice.

Performing the Actions

Now, perform your morning routine exactly as you normally would. Do not rush; the recorder captures the actions, not the time it takes you to do them.

1. Click Row 1 and make it Bold.
2. Go to View > Freeze > 1 Row.
3. Highlight Column C (your sales numbers) and click the Currency ($) button.
4. Double-click the column dividers to auto-fit the widths.

Every time you do something, you will see a checkmark and the action listed in the floating recorder box.

Saving and Triggering the Macro

When you have finished perfectly formatting the sheet:

1. Click the Save button on the floating recorder box.
2. Give your macro a clear name (e.g., “MorningFormat”).
3. Assign it a custom keyboard shortcut (e.g., Ctrl + Alt + Shift + 1).
4. Click Save.

The next morning, when you import your raw, ugly CSV file, do not touch the formatting buttons. Simply press Ctrl + Alt + Shift + 1. You will see a small notification that the script is running, and within two seconds, your entire spreadsheet will instantly transform into your perfectly formatted layout.

(Note: The very first time you run a macro, Google will ask you to grant permission for the script to modify your files. This is normal; simply click through the security warnings to authorize your own script).

Conclusion

If you perform the exact same sequence of mouse clicks more than three times a week, you should not be doing it manually. By using the Google Sheets Macro Recorder, you can teach the software your routine, completely eliminating the repetitive drudgery of daily data formatting.

Related posts

1. How to Track Changes and Add Comments in Microsoft Word
2. How to Compare Two Word Documents and Highlight Differences
3. How to Insert and Format a Bibliography in Microsoft Word