Disclaimer: MashTips is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission at no extra cost to you.

Home Google How to Automate Tasks in Google Sheets with Macros

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.

Google sheet record macro

  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.Google sheets macro types
  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.Google sheets conditional format
  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.

Google sheets save macro

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.

Google sheet run macro

  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.

Google sheet macro result

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.

Manage Macros Menu in Google 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.

Google sheet edit macro

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.

Script Editor Google Sheets

  1. Open your spreadsheet and click Tools > Script editor.
  2. A new window will open now.
    Current Project Triggers in Google Sheets Macro Script Editor
  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.

Add Trigger for Recorded Macros Google Sheets

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.

1 COMMENT

  1. Hi, I am Ashish, in my google sheets i want to use macros in such a way that it copies B1:E2 and pastes values (Ctrl+Shift+V) in A1:D2. I want this to repeat every minute from morning 9 to afternoon 4pm.
    Please note that
    E1 cell =CONCATENATE(HOUR(NOW()),”:”,MINUTE(NOW()))
    E2 cell =GOOGLEFINANCE(“AAPL”,”PRICE”)

    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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Free Newsletter

Read More...

How to Swap Values Between Cells, Rows or Columns in Google Sheets

Google Sheets is considered as an excellent alternative to Microsoft Excel for a number of reasons. First, you don't need to pay any fee...

Pivot Table on Google Sheets: A Complete Guide

Are you struggling with a large set of data on a huge table that is too much to handle? You can narrow down the...

8 Best Google Sheets Add-ons to Improve Productivity.

Like browser extensions helps to extend the capabilities of your web browser, Google Sheets does a similar job of providing additional features. You will...

How to Create Your Own Stock Tracker using Google Sheets

These days, people are using apps for everything from tracking their weight loss goals to stock trading. Even though using such apps makes your...

How to Print Selected Area in Google Sheets

Usually, it is very convenient to view a Google Spreadsheet on the go either on your smartphone or a laptop. However, a hard copy...

Conditional Formatting on Google Sheets: Everything You Need to Know

These days, a lot of people prefer Google Sheets to Microsoft Excel for many reasons like zero cost, multi-user access, cloud storage, and most...
%d bloggers like this: