Google Apps Script or VBA – Which one to choose?

Learn the strenghts and weekness of Google Apps Script compared to VBA and make your choice based on the most important factors.

Visual Basic for Applications (VBA) and Google Apps Script (GAS) are two scripting languages developed specifically for process automation. VBA was developed by Microsoft with the goal of automating Office products like Excel and Word. In the context of each specific application, it’s referred to as Excel VBA or Word VBA, and so on. Conversely, GAS (Google Apps Script) serves a similar purpose for Google Workspace Applications, mirroring the functionality of VBA for Microsoft Office.

Learn what is Google Apps Script and how businesses can leverage it

Each of these languages has its own set of strengths and weaknesses, and your selection between the two depends on your specific needs. Although we can list many points of comparison, we will focus on the most important factors to consider while making your choice.

Similarities between GAS and VBA

Here are a few points Google Apps Script has in common with VBA.

1. Automation: Both Google Apps Script and Excel VBA are used to automate tasks and processes in their respective environments.
2. Scripting Language: Google Apps Script is based on JavaScript, while VBA uses Visual Basic. Though their syntax looks slightly different, both languages use the same fundamental programming concepts (variables, loops, functions, etc.).
3. External services integration: GAS can integrate with Google services such as Gmail and Calendar, and external services as well using the UrlFetchApp method for HTTP requests. VBA also used methods like XMLHttpRequest for external services integration.

Advantages of GAS over VBA

1. Accessibility: When you write a script using GAS, it’s attached to your Google account and saved in the cloud. This means you can access the code with any computer with a browser and you can collaborate with other people on the same project. On the other hand, VBA is limited to the computer where Microsoft Office is installed, hindering easy collaboration.
2. Platform dependence: When you write a VBA script on the Windows operating system and it is fully functional, the same code may bug if you try to run it on the Mac platform, and vice versa. This is due to the fact that the two systems have different programming libraries and functions for VBA. With Google Apps Script, there is no such problem. Since the code is run on the cloud, it has no dependence on the operating system of your computer.
3. Cost: GAS can be written in any Google account for free whereas VBA comes at a price since it’s included in the Microsoft Office pack.

Advantages of VBA over GAS

1. Execution time: Because it runs on a server, GAS has a maximum execution time of 6 min (which may change in the future). This means that your script should finish and accomplish its goal within this time. There are, however, workarounds for this issue. VBA does not have such a limitation.
2. Libraries: VBA has more third-party libraries than Google Apps Script (GAS) because of its extensive community and oldness. While GAS has some libraries for common tasks, its ecosystem is not as large as VBA’s because it is relatively new compared to VBA.

Conclusion

VBA Excel and Google Apps Script are two scripting languages created to automate processes. While Google Apps Script helps automates repetitive sequences and extend functionalities in Google Workspace, Excel VBA accomplishes the same goal in Microsoft Office. Both languages are really helpful when it comes to automation.

The best language will vary from one person to another depending on their needs. You need to choose the scripting language that best suits you after considering the factors mentioned above.

For instance, if easy collaboration really matters in your project and you also don’t want to pay the cost of Microsoft Office, then Google Apps Script is what you need. On the other hand, VBA may be more helpful for you if none of the advantages of GAS matter for your project, and you want to tap into the large library resources of the VBA community.

---