Automate by teaching Google Sheets how to do them by recording your steps into the Macros and never have to manually do it again.
If you work with a new data set each month and need to manipulate that data in the same sequence each time, then you can record a Macro and have Google Sheets do the work for you.
What are Macros?
A macro (short for "macroinstruction") is a specific sequence of actions that let you automate a series of steps to increase productivity. Recording your steps into Macro can automatically do anything in your spreadsheet, faster.
When you record a macro in Google Sheets, it automatically creates an Apps Script with all the code to replicate your actions for you. This means you can create complex macros without knowing how to write code.
Macros are handy to speed up your own work. Here are just a few examples of what you can do with it.
Remove or add formatting and styles
Create completely new spreadsheets.
Insert extra rows and columns
Fill in functions
Clean up data
Use any Google Sheets function, toolbar, menu, or feature and more
How to Record a Macro in Google Sheets
On your computer, open a spreadsheet in Google Sheets.
Click Tools > Macros > Record Macro
2. Recording new macro box will open in the bottom of your spreadsheet with two options for capturing your actions:
Select “Use absolute references” if you want the shortcut to always be performed on the same specific cells.
Example: If you bold the cell A1, then the macro will always make A1 bold regardless of what cell you clicked on.
Select “Use relative references” if you want the shortcut to always be performed on the selected cells, regardless of where they are in the sheet.
Example: If you bold the cell A1 and B1, then the macro can re-use it to bold cells C1 and D1 later.
3. Then perform whatever actions you want to record and teach Sheets what order you want these actions to replicate.
4. After you’ve captured all the actions for this macro, click “Save.”
5. Enter a name for your macro. Google also lets you create shortcuts for up to 10 macros. If you want to bind a macro to a keyboard shortcut, enter a number from 0-9 in the space provided. When you finish, click “Save.”
How to Edit Macro's Name or Keyboard Shortcut
If you need to tweak your macro’s name or shortcut, you can edit a macro.
1. Click Tools > Macros > Manage macros
2. From the window that opens, edit the data as desired and then click “Update.”
The next time you press the keyboard shortcut associated with the macro, it will run even without having to open the macro menu from the toolbar.
How to Run a Macro in Google Sheets
If your macro is an absolute reference:
Run the macro by pressing the keyboard shortcut or go to Tools > Macros > Your Recorded Macro.
Click the appropriate option.
If your macro is a relative reference:
Highlight the cells in your spreadsheet on which you want the macro to run
Press the corresponding shortcut, or click on it from Tools > Macros > Your Recorded Macro.
How to Import Macros to New Spreadsheets
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 to other spreadsheets.
1. Open the spreadsheets with the macro you want to copy and then click on Tools > Macros > Manage Macros.
2. Click the “More” icon next to the macro you’d like to copy and then click the “Edit Script.”
All macros save to the same file, so if you have a couple of macros saved, you may have to sift through them. The function’s name is the same one you gave it when you created it.
3. Highlight the macro(s) you want to copy, then press Ctrl + C. Be sure to copy everything up to and including the closing semi-colon.
4. Now, open the other spreadsheet you’ll be importing the macro to and click Tools > Macros > Record Macro.
5. Immediately click “Save” without recording any actions to create a placeholder function in the sheet’s macro file for us. You’ll be deleting this a little later.
6. Click “Save” again.
7. 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.
8. 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.
Unfortunately, you will have to bind the macro manually to a keyboard shortcut again. Just follow the instruction previously mentioned, and you’ll be all set to use this macro across multiple sheets.
That’s all there is to creating and using macros in Google Sheets. There’s no limit to the routines you can create for your spreadsheets to limit the amount of time you spend doing these repetitive tasks.
Things you can't do with Macros
Here are a few restrictions on what you can do with Macros:
1. Use macros outside bound scripts
Macros are defined in scripts bound to specific Google Sheets.
2. Define macros in Sheets add-ons
You cannot distribute macro definitions using a Sheets add-on.
3. Distribute macros in script libraries
You cannot distribute macro definitions using Apps Script libraries.
4. Use macros outside of Google Sheets
Macros are only a feature in Google Sheets and do not exist for Google Docs, Forms, or Slides.
The next time you run it, Google Sheets will do everything you did when you recorded the macro. Essentially, you’re teaching Google Sheets how to manipulate a document to your liking with a single command.
We want to help G Suite users do more with G Suite. We are providing a free resource of updates, quality tips and tricks for the G Suite Business and Education Communities.
SUBSCRIBE to receive more useful G Suite Tips and Tricks!
コメント