VBA and Office to Google Apps Script Transition
Where to get the document This page concerns the From VBA to Google Apps Script project. You can find the code and test data, under the roadmap section in the VBA to Google Apps Script Roadmapper project download. You […]
I pretty much started here. Since the objective was to migrate from a VBA project to a Google Apps script version of the same, my approach was to write the skeleton, and emulate the underlying […]
In Playing around with GAS color and Playing around with colors in VBA we looked at how various color schemes could be stored and retrieved by Looking up color table. The trouble is that this table can get pretty […]
Let’s say you pass an array of values to be processed to a custom function, and you want to loop through them. In these examples, we’ll just go through each value and sum it if […]
Just like Excel, you can extend Google Sheets by writing custom functions. These functions are written in Google Apps Script. In this section, I”ll show how to do some common VBA things you might want […]
In GAS Rest Library migration, you can see a port of Rest to Excel library from VBA to Google Apps Script. This handles many complex queries and generally can execute them in a few lines of code using […]
NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives. Sometimes you need to create a unique identification for each object. ScriptDB automatically assigns a unique objectId for each object, so […]
One thing I’ve found you need when developing for Google Apps Script is a timer. Everything is pretty slow ( see Optimizing access to sheet values) so you need to dig into exactly what is good […]
The Charts Service Google Apps Script offers a charts service. Using this you can build a dataTable as input to a chart, as well as the chart itself. Elsewhere on this site I covered how to […]
Note that this article uses the Apps Script UI service, which has since been deprecated. I leave it here for interest. The equivalent of inserting a shape in Google Docs is to insert a drawing, […]
Why so slow? Note – this article was written when Apps Script first came out. Since that time, the speed of Sheets access has increased significantly, but I leave this article here for interest. The Data […]
There is a debugger for google apps script. Here’s how to use it Compared to the VBA debugger it sucks, so you probably need to be able to log and assert. javaScript does not have […]
JavaScript does not really have the equivalent of a VBA collection. Since the use of collections is very significant in this project, the best approach is to emulate a collection object in javaScript so that […]
Arguments to procedures in VBA can be optional, and optionally they can have a default value. For example We can also test for the presence of a variant argument by using isMissing(). See Getting Started with […]
Things to look out for As you work with javaScript (most of these gotchas will be about javaScript rather than apps script specifically), you’ll scratch your head about a few things. I know I did. […]
You can find the code (its a work in progress) and test data in the VBA to Google Apps Script Roadmapper project download To minimize the amount of work in converting from VBA to Google Apps […]
In Understanding Scope for VBA I showed some examples of the visibility of functions and variables across modules and procedures inside a VBA project. If you are moving to Apps Script (or JavaScript) from VBA, you’ll need […]
functions are Objects too. As I mentioned in Learning javaScript every variable in js is an object. A javaScript function is also an object. This means it can be assigned to a variable. consider this.. function x() […]
Creating and working with classes in VBA is very different from working with js classes. In fact, JavaScript is a classless language – there is no such thing as a class. This is not so […]
Google apps script is javaScript, with the addition of a set of classes which allow access to Google apps capabilities such as spreadsheet data structures and so on. This means that to be able write […]
If you’ve followed Creating your own scriptDB environment we are ready to start accessing it from VBA. Registering authentication There are 2 things you need to do, both of which are one-off things. You may have done […]
Why a dispatcher Because you may want to have multiple handlers, protected in different ways, as described in Handler for cScriptDbCom requests you need to centralize ‘air traffic control’ for the one or more scriptDB instances you […]
There is also a Google Apps script version of Getting data out of scraperWiki. It uses the mcpher library and the test data and examples can be found in this Google Document There is a rest library entry to […]
The Rest to Excel library is a set of classes and modules that simplify the populating of Excel sheets with data from RestFul APIs. Google Apps Script of course already has plenty of much better ways to […]
As covered in jSon there are many ways to get jSon data into excel. You may be tempted to try to use Excel JSON conversion or get data from google docs. These techniques are for very specific use cases – […]
Storing other objects in cJobject If cJobject was just about getting stuff in and out of JSON, then we’d stop here, but you’ll find that I use it in pretty much all the VBA examples […]
Iteration As per the examples in cJobject deep dive the most common navigation of the cJobject is by iteration through the children, or by direct access as covered in How to access children. However there are a few […]
The excel- rest library is a way to easily populate Excel worksheets with data returned from Rest APIS. Here I will cover the migration from Excel to Google Apps Script. You can get the shared restLibrary […]
NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives. I leave this article here for interest.In Using scriptDB, I covered some usages of scriptDB,along with a method of siloing data […]
scriptDB is a javaScript object database for Google Apps Script project. Here are a few key facts Every project can have its own scriptDB There are no Tables (this is a noSQL database). Instead it is […]
Project conversion 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. This is implemented as a one liner […]
Color manipulation library scripts First off for this project we are going to need to figure out how to manipulate colors in Google Apps Script. As per the approach in Roadmapper migration my objective is to make […]
Project conversion We will use the Color ramp library project as the basis for this migration. The complete code can be found here Google Apps Script Color Ramp workbook Status of conversion General libraries You can read about these […]
Using the modules from Automatic documentation, here’s how to create a skeleton Google Apps Script Module directly from VBA modules or classes. It will also create basic JSDOC markup for documenting your new GAS module. You’ll find all […]
These Data Manipulation Classes have been migrated from VBA so that the Google Docs spreadsheet data can be abstracted from its physical location in the same way as most of the projects on this site abstract data from Excel. […]
Apps for Office (the JavaScript API for office), is a way of using JavaScript along with Office. This section is all about Microsoft APIs that you might need for working with this. Office Add-ins – first […]
Migrating from VBA to Google Apps Script, or using them simultaneously can be hard to get started with. Although this material has been largely superceded by Going Gas, there’s stll some useful stuff here. […]
The fundamental difference between them though, is that although superficially they seem to do the same thing, operations performed on the target workbook from Office is limited to what the API can do. […]
What can you learn here? Populate a treeview Use a cjobject Serialize anything to a treeview How to turn a cJobject into a treeview get it now Since the cJobject is just a tree type structure, it […]
Using a color table In Looking up color table, we covered how to use a color table to reference to make palettes using the functions described in Playing around with colors in VBA. But that was in Excel – […]
These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available from O’Reilly or Amazon What is the Execution API ? This is a brand new API that enables the running […]
In REST access to Google Apps Script list management functions we looked at accessing the Google Apps ScriptDB and some custom functions using the rest to Excel library In that post, I mentioned that it would even be […]
Someone asked in our forum yesterday about Facebook Query Language – FQL. Could we get data straight into Excel from facebook? I dont use facebook, so I haven’t paid much attention to it. I had […]
Excel liberation is all about being able to step outside Excel, whether it’s getting public data, integrating with other platforms or in the case of today’s post, interacting with and delegating processing to the cloud on […]
Today’s entry is pretty cool. I came across the urbarama site, which describes itself as “a collaborative world atlas of architecture and civil infrastructure”. What’s more it has an API, which will return details on […]
Geo code from ip and host names Today’s API is a straightforward query per row, meaning that data is retrieved from the API using the contents of a column as the query. In this case […]
Get rxNorm drug data into Excel and Google Apps Script Aside from being a little specialized, today’s API is another quite unusual one. Normally apis allow you select the format of the output through a […]
In Starter implementation of R-melt for Excel I introduced a one liner in Excel/VBA to transform this into this You could consider this to be a kind of un-pivot – exploding tabulated data back into transactional […]
Shorten URL API directly into an Excel or Google Spreadsheet Today’s API is from “ttb.li” which is a url shortening service. This is a query by row API, populating columns in a spreadsheet row using […]
In passing arguments in VBA using jSON I covered how you could use javaScript Object syntax to pass complex arguments to VBA procedures. Today I’ll cover how to pass complex arguments where there are optional values. […]
Data from CrunchBase API directly into an Excel or Google Spreadsheet Today’s API is another query type from “crunchbase” which is a free database of corporations, people and investors. Yesterday I showed you how to get […]
Data from CrunchBase API directly into an Excel or Google Spreadsheet Today’s API is from “crunchbase” which is a free database of corporations, people and investors. There are a number of types of query available in […]
Today’s API is “FAA airport status” – a dataSet query API, populating rows in a spreadsheet using the Airport IATA code as a key Here’s the Excel code (included in cDataSet.xlsm downloadable from the ramblings site) Public […]
In various posts such as this one, I’ve covered new entries to the REST library but it’s been kind of haphazard. Since I’m adding these regularly I figured I’d make a quick post like this for […]
I haven’t posted anything on new entries to the rest-excel-gas library for a while. As I mentioned in this post the library itself is available as a rest query. There have been quite a number of new APIs added […]
As part of creating a framework for dual maintenance of Google Apps Script and VBA versions of the same application, one of the key things was to produce a set of functions in GAS that […]
In previous posts I covered a couple of conversions of Excel VBA projects to Google Apps Script, with particular emphasis on maintaining compatibility between the solutions to allow for dual maintenance. Previous conversions have been a GAS […]
Passing arguments in VBA can be a pain. Consider foo(a,,,,b,,,,c,e) Of course in some cases you can use named arguments, but the called function needs to know how to do that – it doesn’t work […]
In javaScript, everything is an object. Classes don’t really exist because they don’t have to. To create an class like structure, you just do something like this var employee = {}; employee[‘id’] = 1; employee[‘grade’] […]
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 […]
You are ready to move to the cloud, but what about all those legacy vba apps? Are they really going to influence your decision such that you have to stay where you are? Moving to […]
Ive been using Google Docs as well as Excel for a while now, and certainly Excel is more comfortable a user experience. However it’s clear that Google Docs offer a great opportunity to offer services […]
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines