We most commonly use Google Sheets to store data and share it with others. Among other things it also lets us manipulate and analyze data, resulting in accordingly establishing trends etc. For a regular user, the tasks can get repetitive and time-consuming leading to 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 macro 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 the 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 the 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.
Example
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.
Example
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.
Example
Conclusion
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.
Advantages
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 are much higher and more 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.
- We can build user interfaces using both VBA and Google script. The former is constrained when it comes to design (we can only build user forms). But with the latter, we can also leverage the technologies such as HTML, CSS, and Javascript.
- The list of data types in VBA is limited, as it is still relying on a relatively older technology. While Google script is based on Javascript, which is one of the core web technologies. Therefore we can bank upon its object-oriented elements, flexible data structures, and numerous utilities and build upon that.
Feel free to read through the following blog post to see what 10 tips and tricks we listed for you to maximize the results in Google Sheets!
