Setting Up Macros in Google Sheets

With advancements in technology comes an even greater need for efficiency at work. Basic spreadsheet tricks like formulas, tables, and filters in Google Sheets can no longer get you very far. Even though these skills are still essential to know, they are quickly becoming somewhat outdated.

Workplace production really demands something more than mere efficiency. It demands automation — it demands macros.

The Importance of Automation

Automation is not only used in manufacturing or research. It’s also being used more and more in spreadsheet applications (Microsoft Excel, Google Sheets, Libre Calc) for software-assisted tasks. Automation is the predator of menial, repetitive tasks, and office work is chock-full of this nourishment.

Instead of exhausting our fingers and eyes with repetitive keyboard strokes, all we have to do is set a few rules and leave the rest for automation. Now, we’re free to use our brains for analyzing anomalies in data points, search for causes, brainstorm solutions, or plan important events.

The primary form of automation used in spreadsheet software is called macro.

Google Sheets

Gone are the days of Microsoft Excel’s software monopoly on spreadsheets. With an explosive entrance into the data entry world by Google, Google Sheets introduced a new fresh take on spreadsheet software.

Google Sheets is a spreadsheet software, primarily used for computing and formulating data sets. Google Sheets focuses on portability, making its web application better and more accessible than the standard desktop software. A Google Spreadsheet can be easily accessed by specified persons for more efficient work collaboration or viewing. The software also lends itself well to incorporating macros.

Macro

Macro, from the Greek word makros, directly translates to “long/large.” In computer language, macro means a set of instructions that can be repeated and designed to perform a particular task. Even creating simple macros can save precious time and effort on your project. The more complicated a macro gets, the more time and effort you save.

In Google Sheets, you can set up macros using one of two methods.

1. Macro Recording

​

This method is the simplest one. It works well for simple strings of instruction. The bigger the data, the less this method will fit your needs. The macro recording method is mostly aimed for beginner users like college students and small business owners. Here’s a basic example that can be applied to automating formatting.

1. To open Google Sheets, go to docs.google.com/spreadsheets.
2. Go to Tools > Macros > Record Macro.
3. Press Ctrl + B (bold).
4. Press Ctrl + I (italic).
5. Set the Text color to red.
6. Set font size to 18.
7. Make sure the reference is set to Relative.
8. Press Save.
9. Type in a name for the macro, and set the shortcut number. You can only input up to 10 macros.

To execute the macro, press Ctrl + Alt + Shift + (chosen number). It will format the text inside a selected cell bold, italic, red, and size set to 18.

Think of recording a macro like recording the commands you would input into the computer to accomplish a particular task. When you activate the macro, it simply replicates the sequence of those commands at once.

2. Writing Script

Writing Script is more reliable, precise, and robust. However, it takes practice to learn. This method is aimed at advanced users who work with massive datasets, e.g.,  Data Analyst and Project Managers who want laser-like precision and to work as quickly as possible.

1. To open a spreadsheet, go to docs.google.com/spreadsheets.
2. Go to Tools > Script Editor.
3. Below /** @OnlyCurrentDoc */ , paste:
   function FormatText() {  var spreadsheet = SpreadsheetApp.getActive();

     spreadsheet.getActiveRangeList().setFontWeight(‘bold’)

     .setFontStyle(‘italic’)

     .setFontColor(‘#ff0000’)

     .setFontSize(18);

   };

4. Press Ctrl + S to save the script.
5. Go to Tools > Macros > Manage Macros.
6.  Set the shortcut number for your saved script.

The result should look like this:

​

To execute the macro, just press Ctrl + Alt + Shift + (chosen number). Or you can navigate to Tools > Macros > (Macro name) to execute manually.

The script above does precisely the same thing as the record macro method. This is just a small portion of what you can do with a macro script.

Conclusion

Essentially, Google Sheets is an online-based spreadsheet application developed by Google. Macro is a powerful tool for spreadsheet automation. So it makes sense that, when you join the two, it increases work efficiency dramatically. This will certainly help you keep up with the demands of an ever-growing technology-based work environment and give you an advantage in the office.