Learn how to transition from VBA to Google Apps Script

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

VBA is to Office as Google Apps Script is to Google Apps. Those tasks to automate repetitive sequences, or extend the functionality of Office can usually be done in Google Apps Script. Not only that but since it is really just javaScript, you could either know the basics already or by learning it, you will be able to write regular web applications as well as to extend Google Apps. Google Apps Scripts ‘live in the cloud’ and form part of the toolset available to make your Google apps experience more fulfilling.

Transitioning from VBA

These following article set will take you through an accelerated program to learn javaScript and Google Apps Script with an emphasis on how to use it to do things you already know how to do in VBA, by showing you how it’s done in each language. 

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

This is often a good way of migrating a step at a time. Take a look at Integrating VBA and Javascript and this blog post to see more on this approach

Abstraction

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

1. Getting started
   
   1. Learning javaScript
   2. classes and javaScript
   3. javaScript functions
   4. Apps Script versus VBA scope
   5. Gapps Conversion approach
   6. Some Google Apps Scripts gotchas
   7. Managing shared libraries with Google Apps Script
   8. Automatic VBA to GAS skeleton
      
      
2. Useful hacks
   
   1. Dealing with optional arguments
   2. VBA collection in javaScript
   3. VBA debug object
   4. Optimizing access to sheet values
   5. What to use in place of Excel Shapes
   6. How to make charts in Google Apps Script
   7. Apps Script timer collection
   8. GAS hacks for VBA
   9. Using scriptDB
   10. Tracking activity with ScriptDB
   11. Generate an auto-incrementing key with scriptDB
       
       
3. Some Excel library conversions
   
   1. Data manipulation Classes in Google Apps Script
   2. Color Ramp useful scripts
   3. cJobject Class in Google Apps Script
   4. Scraperwiki data to Google Docs
   5. Playing around with GAS color
   6. Color space conversions in GAS
   7. Color Matching in GAS
      
      
4. Leaving VBA behind
   
   1. A simple Google Apps Script RestQuery
   2. Apps Script custom functions
   3. Looping in Google Apps Script
   4. Advanced Array functions
      
      
5. Performance blog posts
   
   1. New sheets for old – writing comparison with new,old and excel
   2. Execution time woes

Project conversion

1. 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.  
2. Google Script Color Ramp . A conversion from the VBA Color ramp library
3. GAS Rest Library migration. A conversion from the VBA Rest to Excel library

Power of apps script

VBA is often not given the credit it deserves as a very well integrated and capable language, with plenty of bundled capability to reach into the object model of Office – but it’s not really going anywhere. Although you can’t believe everything you read, you can see where even Microsoft are going with HTML5 and javaScript versus VBA. 
The Google Apps API is very powerful and is evolving very quickly. Expect to see the continued added functionality.

Some slides with a sample of VBA function translated to Google Apps Script

For more like this, see  From VBA to Google Apps Script . Much of the code referred to here is the mcpher library. You can find out how to include it in your project there.