Quick Links

Other stuff

Site owners

  • Bruce Mcpherson

Services‎ > ‎Desktop Liberation‎ > ‎

From VBA to Google Apps Script

You have built up an expertise and body of work in VBA, but your company is now seriously considering moving to Google Apps and ditching Microsoft Office products. Is it the end of the world? Actually, in the end it's not so hard. You can find the applicationand test data in the VBA to Google Apps Script Roadmapper project download. You can read a summary in this blog entry. This uses the GAS mcpher shared library.

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 tool set 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.

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 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. Gapps Conversion approach
    5. Some Google Apps Scripts gotchas
    6. Managing shared libraries with Google Apps Script
    7. 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 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 . Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. Much of  the code referred to here is the mcpher library. You can find out how to include it in your project here





Subpages (32): View All
Comments