How to Automate Tasks in Google Sheets with Macros

You can get your works done in minimal time with the unmatched features Google Sheets offers. The Macro is such a thing that lets you automate tasks in Google Sheets simply using keyboard shortcuts, which can contribute much towards your productivity.

Let us see what are Google Sheets Macros and learn how to automate Google Sheets tasks.

Content

1. How to Create Macros in Google Sheets
2. How to Run Macros in Google Sheets
3. How to Edit/Delete Macros in Google Sheets
4. How to Schedule Macros in Google Sheets

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.

1. On your browser, go to Google Sheets and open your spreadsheet.
2. Click on the Tools from the menu.
3. Go to Macros and click Record Macro.
4. Then, you need to select the type of cell reference for your macro.
5. Now, to set conditional formatting for the table, click Format > Conditional Formatting.
6. If you are new to this, you can refer our guide on Conditional Formatting in Google Sheets.
7. Let’s assume that someone needs to work for more than 9 hours to claim overtime. Click any cell on column H (Total Hours).
8. Next, select Greater than in the Format cells dropdown box and enter 9 in the box titled Value or formula.
9. Then, select Fill color to Green under Formatting style.
10. Click Done to close the Conditional Format rules window.
11. Next, click Save located on the Macro window.
12. Enter a name for the macro (eg. Overtime).
13. Create a Keyboard Shortcut for the macro and click Save.

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.

Related: How to Swap Values Between Cells, Rows, and Columns in Google Sheets

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.

1. Navigate to Google Sheets and open your spreadsheet.
2. Next, select all the non-empty cells of Column H (Total Hours).
3. Then, click Tools > Macros and select the macro you need to run (eg.overtime).
4. Allow permission for the macro and click Continue.
5. Then, you will see the message “Running script” followed by “Finished script” once the macro has processed the data.

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.

Related: Google Sheets Offline: Everything You Need to Know

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.

1. Open the spreadsheet in Google Sheets.
2. Click on Tools from the menu bar.
3. Go to Macros > Manage macros.
4. Select the macro you would like to edit.
5. Click the three-dot menu button.
6. Choose Edit script to make changes to the macro actions.
7. Click on the Remove option to delete macro in Google 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.

Related: 8 Best Google Sheets Add ons to Improve Productivity

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.

1. Open your spreadsheet and click Tools > Script editor.
2. A new window will open now.
   
3. At the top menu, click Edit > Current project’s triggers.
4. In the new window, click the Blue button titled Add Trigger, at the bottom right.
5. On the popup window, choose the function (macro) to schedule.
6. Select event-source as Time-driven.
7. To run the macro every day, choose Day timer from the next menu.
8. Select the time of the day.
9. To get a macro running report every day, choose “Notify me daily” from the menu on the right.
10. Click the Save button.

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.