Let us see what are Google Sheets Macros and learn how to automate Google Sheets tasks. Content
How to Create Macros in Google Sheets
Macros in Google Sheets is simply recording any of your repetitive actions (or a series of actions). You can later use the saved Macro to do the same action on the table, on different sets of data. Let’s imagine that you are working with the weekly timesheets of your team members. And, you need to mark the days on which they worked overtime. Let’s see how to quickly do this automatically with macro, instead of doing manually.
If you find the cell reference option confusing while creating a new macro on Google Sheets, here is what they mean.
Use absolute references: The macro will do tasks on the exact cell you record. For example, if you bold cell B1, then the macro will only ever bold cell A1 regardless of which cell you clicked Use relative references: The macro will do tasks on the cell you select and its nearby cells. For example, if you record bolding cells A1 and B1, the macro can later be used to bold cells C1 and D1. Here, we have selected the option Use relative references.
That’s it. Now, you have successfully created a simple macro in Google Sheets. Next, let’s see how to run the macro you had created.
How to Run Macros in Google Sheets
No matter how many timesheets you need to evaluate, you can quickly obtain the overtime metrics for your team members by running the macro you created earlier. Let’s see how to run macro in Google Sheets. And, here we are using the macro we just created.
Now, if you observe Column H, you will see a Green color, against the row that has overtime work. Similarly, you can create macros for a certain task and run it across spreadsheets to do the same actions anywhere.
How to Edit/Delete Macros in Google Sheets
The first time you create a macro, it might not work as expected. You can give it a little polish with the right actions to automate tasks in Google Sheets. Let us see how you can edit or remove macros in Sheets.
After making the required modifications, click Update to save your changes. When you choose to Edit script of a macro, it will take you to another window. You can only change the actual script running behind the macro. So, we don’t recommend making changes to Google Sheets macros if you cannot understand what the script code actually is.
How to Schedule Macros in Google Sheets
When we say about automating tasks in Google Sheets, we really mean. Of course, the Macros does half the job for you, but you actually need to run it. What if you can automate the running itself? Google Sheets lets you schedule macros and automate whenever you want.
You will be asked to authorize using Google account to schedule a macro. Once you set this, the given macro will run at the given interval on the specific Google Sheets spreadsheet doc. Well, in our example we have used a simple macro to record the overtime hours of employees from their timesheets. Macros can be used for more than that from simple formatting tasks to tricky functions on your spreadsheet. Just you need to teach your spreadsheet on how to automate lengthy and time consuming repetitive tasks step by step. Then, your macros will save a lot of time and increase your productivity. The only limitation of Google Sheets Macros is that it works only with the spreadsheet you had created. If you need to use a Macro in another spreadsheet, either you need to import or copy & paste the script to automate tasks in Google Sheets. Now you understand that im trying to create an intraday chart from the googlefinance live price shown (delay doesnt matter). If i run the macro of {copies B1:E2 and pastes values (Ctrl+Shift+V) in A1:D2}, it shows this : Error The coordinates of the source range are outside the dimensions of the sheet. Can googlefinance function not be macro triggered. But if I manually press Ctrl+Shift+Alt+1, that is macro shortcut, it works. but the trigger doesnt work. Please help I am badly stuck. Your help would be heartily appreciated.
Δ
