A major sticking point with moving from Excel to Google Sheets was the lack of ability to use macros. A macro will let you automate calculations and other aspects in sheets. For instance, if you regularly tally up marks, allocate a grade and then draw a graph or chart of the results for your class, then you can create a macro to do this for you. Google Sheets now have the capacity to write and run your own macros. This will improve your efficiency and save you time.
[bctt tweet=” Google Sheets now have the capacity to write and run your own macros” username=”adifrancis”]
It is simple to build your first macro and save it in a Google Sheet. The great thing about macros in Google Sheets is that they are accessible whenever you use the sheet as they are cloud-based. They are written using Google Apps Script, but you don’t need to understand how to program using this code because you can record a macro within a Google Sheet. Unfortunately, you are unable to import macros from Excel at this stage as they use VBA as the code to make them run.
To build your first macro, follow these easy steps:
1. Create, or open an existing Google Sheet that has some data in it. For example, you may have a mark book with some marks in it.
2. Decide on what action you want to carry out, in the example here I want to convert the raw mark to a percentage.
3. Click on the Tools tab.
4. Click on Record macro
The macro window will now open and indicate that you are now recording a macro.
Complete the calculations and formatting that you want to have in the sheet.
5. When finished, click on save and name the macro.
Running the macro is just as easy:
1. Click on Tools
2. Choose macro
3. Click on the macro you have just written.
From here, you can also edit the macro by editing the underlying Google Apps Script:
1. Click on Manage macros.
2. Click on the three dots.
The apps script looks like this and you can edit it directly.
This macro will stay with this sheet. If you wish to use it in another sheet, you can download the script and then install it in another sheet.
[bctt tweet=”Macros make your spreadsheet life more efficient and are now included in Google Sheets. Watch this video to see how easy it is to create a macro in a Google Sheet” username=”adifrancis”]
Macros make your spreadsheet life more efficient and are now included in Google Sheets. Watch this video to see how easy it is to create a macro in a Google Sheet.
