A Macro Introduction to the Google App Script

September 10, 2018 Joe Boyle

Google’s Apps Script makes it possible for those with only an introductory knowledge of programming to build quick, useful applications. You don’t need to be a software engineer to harness the capabilities of Google’s built-in code editor, which makes it possible to build new programs with the Google “G Suite” services like Google Docs, Google Sheets, Google Slides, and Google Forms. Though some familiarity with Javascript doesn’t hurt.

If building your own application from scratch sounds a bit daunting, a great way to get started is through the macro-recording feature released in Google Sheets earlier this year. The macro record feature allows you to record macros in the cloud in order to automate repetitive tasks, such as performing calculations on your data or formatting it for better readability. This is made possible with the help of Apps Script, which allows you  to revise recorded actions afterward by directly editing the code. When you record a macro, Google Sheets automatically creates an Apps Script function (the macro function) that replicates the macro steps.

How Does it Work?

To illustrate, let’s use an example. For simplicity sake, let’s start with a basic spreadsheet that records the grade values for several students in a class over five tests:

A very simple action you’d want to perform on this data is to calculate the average test score for each user. Ignoring the fact that there is a built in function for this in Sheets, we’ll use the Record Macro feature on the first student to calculate an average, and quickly use that recorded macro to calculate the average for the remaining students:

  1. Select Tools>Macros>Record Macro
  2. Select Use Relative Reference
  3. Place your cursor in cell G2 and type ‘=average’, and select the built in Average function
  4. Highlight cells B2-F2
  5. Hit Enter
  6. Click Save in the Record Macro window
  7. Title the macro ‘Calculate Average Grade’
  8. Under Shortcut, fill in the number 1 next to Ctrl+Alt+Shift

Congratulations, you have recorded your first macro! Not only this, but you have created a shortcut making it easy to use this new macro on the remaining data. Now, let’s put it to use. As we’ve created a shortcut for the macro, there are two ways to employ it within the spreadsheet.

Select the Macro from the Menu

  1. Place your cursor in G3, the next cell you wish to perform the calculation
  2. Select Tools>Macros>Calculate Average Grade
  3. The average grade for the next student will be calculated

Use the Shortcut

  1. Place your cursor in G4
  2. Hit Ctrl+Alt+Shift+1 on your keyboard
  3. The average grade for the next student will be calculated

It’s that easy! You are now on your way to utilizing macros in spreadsheets. Macros can be recorded and used to execute a variety of complex actions. Any time you record a macro, a corresponding Apps Script function is created in the script directly attached to the spreadsheet. You can access this script within the spreadsheet by selecting Tools>Script Editor.

Within the Script Editor you will see separate functions for any macros you’ve recorded, and you can directly edit the script to modify the behavior, rather than needing to record a new macro. Pretty cool!

If you’re feeling adventurous, you can also write your own macros from scratch using Apps Script and execute them on the sheet the script is attached to. Do you want to discover different technologies in the industry? Check out our blog to learn more! 

Previous Article
What’s Holding You Back from a Connected Supply Chain?
What’s Holding You Back from a Connected Supply Chain?

As automotive suppliers and OEMs continue to navigate increasingly competitive global markets, it is impera...

Next Article
Take It from a Futurist: The Automated Future Is Coming
Take It from a Futurist: The Automated Future Is Coming

Workplaces will one day be driven by automation. Take a glimpse at an HR department of the not-so-distant f...