Far too often, it goes like this: You download data from your apps—your PayPal transaction data, say, or an exported email list from your old newsletter app—and open it in a spreadsheet. Then you move data around, resize columns, format text, delete rows, add headers, and finally add formulas and graphs to actually use the data and make it understandable.
Spreadsheets are powerful tools—and they're also tedious, frustrating nightmares of confusing formulas, hidden features, and messy data. Excel has long included a macro tool to automate those tedious tasks away—and now, with Google Sheets' new macro tool, you can build your own automations in Google's online spreadsheet app, too.
By recording your steps into a macro, you can teach Google Sheets how to do them at the click of a button, and never have to manually do it again. And as a bonus, if you want to add a custom keyboard shortcut for any one menu item in Google Sheets, you can now do that in Google Sheets with a quick macro.
Here's how to use macros in Google Sheets.
How to Make a Macro in Google Sheets
Macros are spreadsheet functions that can automatically do anything in your spreadsheet, faster. They can remove or add formatting, insert extra rows and columns, fill in tricky functions, clean up data, and more. As long as there's a button or menu option for something, a macro can run that along with anything else you want. You just have to teach your spreadsheet what you want it to do, then press the button or keyboard shortcut to run the macro.
Macros are handy to speed up your own work, and even more useful when you need to hand off work to someone else. Instead of needing to tell them exactly how to set up the spreadsheet and which functions to add, tell them to run your macro and the spreadsheet can do it for you.
All you need to do is first build the macro. In Google Sheets, open your spreadsheet, then click Tools → Macros → Record Macro.
That will open a small Recording New Macro box in the bottom of your spreadsheet. Whatever you click or type in Google Sheets now will be recorded—and done again in that same order when you run that macro.
There’s one option to note. On the bottom of the dialog, you can choose to Use absolute references or Use relative references. The former will record the exact cell, column, and row you click—so if you click cell A1 and then click the Bold button, that Macro will always make cell A1 bold. The latter will record the relative direction of the work you do in the spreadsheet. Say cell B1 is selected in the spreadsheet when you start recording, then you click cell A1 and then click Bold. A relative macro will note to select the cell to the left—not precisely cell A1, but the cell to the left of the one selected when you start the macro—then make it bold.
Once you’ve done everything you want this macro to repeat for you in the future, click Save. You can then add a name to the macro along with a number, for easy access to up to 10 macros. Then, every time you want to run that macro, you’ll press Ctrl+Alt+Shift+your number on a PC, and Command+Option+Shift+your number on a Mac. (Unfortunately, macros don’t work on Google Sheets’ iOS and Android apps yet).
Let’s pull it all together. Say you regularly get a contact list with names combined in one cell, along with the contact’s email—and you’d like to get their first and last name in their own columns along with their company name. A few Insert Rows and Split Text functions later, and you can teach Google Sheets how to clean up that spreadsheet. Then the next time you get a similar contact sheet, run the macro and it will do the cleanup work for you.
With Google Sheets Macros you can:
- Use any Google Sheets formatting tools
- Use any feature in a Google Sheets toolbar, menu, or right-click menu
- Use any Google Sheets function
- Select any cell, row, or column
- Use Google Sheets standard keyboard shortcuts
- Type any text into a spreadsheet
Anything you routinely do in Google Sheets, you can have a Macro do in a fraction of the time, on its own.
Add Custom Keyboard Shortcuts for Any Google Sheets Feature
Often what makes spreadsheets difficult to use is that the one feature we need is hidden in some menu, and we never seem to remember where it is when we need it. That’s how the Split Text tool is for me—I need it just rarely enough that I always forget where it lives.
You can’t add custom keyboard shortcuts in Google Sheets—or at least, there wasn't an obvious way to do this before Macros give you a workaround.
Just start the macro recorder, and record you clicking that one Google Sheets feature you need—and perhaps selecting the option you need in it as well (as in the Split Text animation above). Then save and add a custom keyboard shortcut for that macro.
The next time you need that feature, press its keyboard shortcut—which is always Control+Alt+Shift+number on a PC, or Command+Option+Shift+number on a Mac. You can also see the macros' keyboard shortcuts in the Tools → Macros menu, or can edit them anytime from the Manage macros button there.
Add Macros to New Spreadsheets
There’s only one problem: Google Sheets Macros only work on the spreadsheet where you make them. You’ll need to recreate them on other spreadsheets if you want to use them elsewhere.
Or, you could copy them over. To do that, first open the spreadsheet with your macro, click Tools → Macros → Manage Macros, then click the menu beside the macro you want to copy, and select Edit Script.
Each macro is powered by a Google Apps Script that you can copy over to use in any other spreadsheet. All you need to do is copy this script and use it in the other spreadsheet. So, select all the text in the editor, and copy it.
Now, open your new spreadsheet, record a macro, and save it—only here, you can literally start the macro recorder and click save without doing anything else as all we need is a blank, placeholder macro. Repeat the steps above to edit this new macro, and this time select all the text in the Google Apps Script editor and paste in your copied macro script from the first spreadsheet instead.
Go back to your new spreadsheet and run the macro, and you’ll have the same features from your first spreadsheet without all the work of remaking it.
And that’s how to use Google Sheets Macros to teach Google Sheets how to do your hard work for you. It’s a handy way to do routine tasks in your spreadsheet without needing to click a dozen times.
Do More With Google Sheets
- Want to build more powerful macros? Check out our Introduction to Google Apps Script to learn the basics.
- Or, add more features to your spreadsheets without coding them yourself with these 50 best Google Sheets add-ons.
- Go from spreadsheet beginner to expert with Zapier's free Ultimate Guide to Google Sheets book.
Or, use Zapier’s Google Sheets Integrations to get your spreadsheet to do work for you, even while you’re sleeping. Want your new contacts or orders automatically added to a spreadsheet as they come in, or want to create a new project spreadsheet every Monday morning? You can do that and more with Zapier’s Google Sheets integrations that connect over 1000 apps to your spreadsheet. Here are some of the most popular to try:
source https://zapier.com/blog/google-sheets-macros/
No comments:
Post a Comment