Some notes on this section
I wrote this entire section on VBA to Apps Script transition quite some time ago, and of course, some things have changed since then in the fast-moving world of Apps Script. My new book is all about this subject and contains brand new information, especially if you are interested in VBA to Apps Script migration. You’ll also find some adapted material from the book here.
What is Google Apps Script
Transitioning from VBA
First observation – it’s painfully slow compared to the same thing in VBA, but see Optimizing access to sheet values. This a feature-rich caching capability, both read and write and applying to all range methods that have both a set… and get… version.
Many of these libraries are now part of a Google Apps Script shared library you can incorporate in your own project.
Running Apps Script from VBA
I have talked about data abstraction extensively on this site, meaning separating the physical data location from the data itself so that procedures are not aware of where the data is coming from, or even that it originated in Excel. This approach will serve us well since it means that if we simply change the Data Manipulation Classes to understand about the Google Apps Data structures, the bulk of the logic will remain intact, even if they do have to be translated.
If a regular visitor to this site or the associated blog, you will know that many main functions are executed in one or a just a few lines of code, and generally speaking these avoid addressing specific Excel capabilities through data abstraction. Once the underlying libraries and classes are ported to Google Apps, it will then be a simple job to port most of the Excel procedures on this site.
Things we need to get started
- Getting started
- Useful hacks
- Dealing with optional arguments
- VBA debug object
- Optimizing access to sheet values
- What to use in place of Excel Shapes
- How to make charts in Google Apps Script
- Apps Script timer collection
- GAS hacks for VBA
- Using scriptDB
- Tracking activity with ScriptDB
- Generate an auto-incrementing key with scriptDB
- Some Excel library conversions
- Leaving VBA behind
- Performance blog posts
- Roadmapper migration. We will use the Roadmap Generation project as a model for learning how to take an existing, complex project and convert it from VBA to Google Apps Script.
- Google Script Color Ramp . A conversion from the VBA Color ramp library
- GAS Rest Library migration. A conversion from the VBA Rest to Excel library
Power of apps script
The Google Apps API is very powerful and is evolving very quickly. Expect to see the continued added functionality.