From VBA to Google apps script – a comparison using a real project

Conversion effort
A few weeks ago I wrote about starting my effort to convert a complex VBA project to Google Apps Script. I vaguely remember thinking at the time that this was going to be a pretty straightforward activity. Just emulate a few VBA functions and do a little tweaking.

It was kind of tough

I was wrong. The reality has been a little different, as along the way I’ve had to figure out a bunch of stuff including the best approach to classes, work through some GAS gotchas , emulate a VBA collection in javaScript, create some timers to figure out why GAS is so slow, and create a caching mechanism to make it acceptable, figure out what shapes to use instead of Excel Shapes, and figure out how to add Charts to the final roadmap

In any case, I am nearly done, and above is the output. Not quite as nice as the Excel version, but it will do for now. Barring a few formatting cleanups, it’s largely done.

Conclusion
It’s been a lot tougher than I had hoped, and I am pretty disappointed at the performance issues, and the missing capabilities. Nevertheless, I now have a very large library of ‘VBA like’ functions that will make future conversions pretty straightforward.

Although I prefer javaScript to VBA, the development experience is pretty poor as a whole (debugging, IDE) and I don’t really think that GAS is quite ready for professional application building – although it’s fine for adhoc automation – which is after all the majority of use cases for VBA.The benefits that could be gained from using javaScript and Google Visualization are largely absent (you cant access the DOM directly to tweak, Google apps script is not available across the whole range of apps, the underlying properties of Visualization objects are hidden, etc, etc)

In addition, the sharing and permissions model is almost incomprehensible and I think that those of you that are used to sending spreadsheets around with embedded macros will pull your hair out.

Don’t give up on VBA yet…. You can read the full story and get the code here – From VBA to Google Apps Script