We most commonly use Google Sheets to store data and share it with others. Among other things it also lets us manipulate the data, analyze it and accordingly establish trends etc. For a regular user, the tasks can get repetitive and time-consuming, and not forget error prone. With the help of formulas, we can automate things to a great extent. But with macros (Google App Scripts) we can accomplish much more. In this article, we explain how to create macros in Google Sheets.
Why create macros in Google Sheets?
Let us consider an example. A sales manager looks at a sales data sheet that is auto-populated with latest raw information every day. Before he begins his work for the day, he needs to generate a report from this raw data in a pre-defined format. This task is accomplished with exact same manual steps, and therefore it is highly repetitive in nature. Fortunately for him, he can automate the process and execute it in a matter of seconds. While previously he was taking much more to achieve the same output.
We will use an extremely simple example to show how to create macros in Google Sheets. Because an advanced example is beyond the scope of this article. We recommend you going through this link to learn the advanced concepts. The program we are going to write, for the purposes of demonstration in this article, does the following:
- Inserts a value “Hello world!” in the cell A1.
- Changes the font to ‘Calibri’.
- Changes the font size in cell A1 to 14.
- Fills the cell A1 with yellow color.
In order for us to get there, we need to navigate to Tools > Script editor.
On doing that, it will provide us with a workspace where we can write and execute our program in a separate tab. On the Script editor window, we write the code as shown in the snapshot below. Before we click on the Run button, we may want to Save it using the floppy disk icon.
When we try to execute this program, it might ask us to authenticate for the very first time. Here’s the result after the program finished its execution.
That was a fairly simple example that explains how to create macros in Google Sheets. However, the real-time applications can get very complex that usually simplifies a lot of stuff, which otherwise would have been difficult to achieve.
The Google Apps script is very similar to Office VBA. But in comparison, Google Apps script offers much more benefits than the traditional VBA does.
- VBA runs on a client machine. Whereas Google script runs on the cloud taking its reach and scale much higher and flexible. One can even build web-based applications using the Google script. But we can’t achieve that with VBA, that at the most can integrate with web applications using an API.