How to Automate Tasks in Google Sheets with Macros

You can get your kit and caboodle done in borderline 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 automatize Google Sheets tasks.

Content

1. How to Create Macros in Google Sheets
2. How to Melt down 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 repetitious actions (OR a series of actions). You can later consumption the saved Macro to do the same action on the set back, on different sets of information.

Countenance's imagine that you are working with the weekly timesheets of your team members. And, you need to mark the days connected which they worked extra time. Let's see how to quickly do this mechanically with big, 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. Attend 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 tabular array, clack Format > Conditional Formatting.
6. If you are new to this, you can cite our guide Conditional Formatting in Google Sheets.
7. Let's usurp that someone needs to work for more than 9 hours to claim overtime. Click any cell happening column H (Total Hours).
8. Next, select Greater thanin the Format cells dropdown box and enter 9 in the box titled Value or formula.
9. Then, select Replete color to Green under Formatting style.
10. Click Through to good the Conditional Format rules window.
11. Next, click Savelocated on the Macro window.
12. Enter a mention for the macro (eg. Overtime).
13. Create a Keyboard Shortcut for the macro and click Save.

If you find the cell character option confusing while creating a untested macro connected Google Sheets, hither is what they mean.

• Use absolute references: The macro wish doh tasks on the exact cellphone you record. For deterrent example, if you bold electric cell B1, and so the macro will only ever so bold cell A1 regardless of which jail cell you clicked
• Wont relational references: The large will ut 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 elect the option Enjoyment relative references.

That's it. Now, you have successfully created a simple macro in Google Sheets. Adjacent, permit's see how to run the macro you had created.

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

How to Run Macros in Google Sheets

Regardless how many timesheets you need to evaluate, you can quickly obtain the overtime metrics for your squad members by squirting the macro you created earlier. Let's figure 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, pick out all the non-empty cells of Column H (Total Hours).
3. And so, click Tools > Macros and select the large you need to run (eg.overtime).
4. Allow permission for the macro and click Continue.
5. So, you will see the message " Running script " followed by " Finished book " once the big 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 prevail it across spreadsheets to coiffe the selfsame 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 large, it might not work American Samoa 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 take macros in Sheets.

1. Open the spreadsheet in Google Sheets.
2. Click connected Tools from the bill of fare ginmill.
3. Attend Macros > Make do macros.
4. Superior the macro you would like to edit.
5. Click the tierce-dot bill of fare button.
6. Opt Blue-pencil hand to induce changes to the macro actions.
7. Tick connected the Remove option to delete macro in Google Sheets.

After devising the required modifications, click Update to save your changes. When you prefer to Edit playscript of a macro, it bequeath deal you to another window. You can only change the actual script running behind the macro. Thusly, we don't recommend making changes to Google Sheets macros if you cannot understand what the script encrypt actually is.

Related:8 Best Google Sheets Add ons to Improve Productiveness

How to Schedule Macros in Google Sheets

When we say about automating tasks in Google Sheets, we rattling hateful. Course, the Macros does half the farm out for you, only you really need to run it. What if you can automate the track itself? Google Sheets lets you schedule macros and automate whenever you want.

1. Open your spreadsheet and click Tools > Script editor program.
2. A new window volition 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 bed right.
5. On the popup window, choose the occasion (macro) to schedule.
6. Select event-source as Time-driven.
7. To run the macro each day, choose Mean solar day timer from the succeeding 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. Get through the Hold open push.

You will be asked to authorize using Google account to schedule a macro. Once you set this, the given macro instruction will rill at the given interval on the taxon Google Sheets spreadsheet doc.

Considerably, in our good example we have in use a smooth macro to record the overtime hours of employees from their timesheets. Macros can exist exploited for to a greater extent than that from kidney-shaped data format tasks to tricky functions on your spreadsheet.

Just you need to teach your spreadsheet along how to automate lengthy and metre intense repetitive tasks step past measure. Then, your macros will save very much of time and addition your productivity.

The only limitation of Google Sheets Macros is that it works lonesome with the spreadsheet you had created. If you need to practice a Macro in some other spreadsheet, either you need to import operating theater copy & paste the script to automate tasks in Google Sheets.

Revelation: Mashtips is supernatant by its audience. Equally an Amazon River Affiliate I earn from qualifying purchases.

How to Automate Tasks in Google Sheets with Macros

Source: https://mashtips.com/google-sheets-macros/