This is the dbab JSON API for  Database abstraction with google apps script, which is Mj61W-201_t_zC9fJg1IzYiz3TLx7pV4j. In JSON API for data abstraction classes I covered how to access the JSON API for Database abstraction with google apps script. Now let’s take it a stage further and write a fully fledged client for VBA that uses it.

Here’s a primer to introduce the concepts

Google apps script database abstraction exposed version from Bruce McPherson

Starting off

This uses the JSON API for data abstraction classes, so first you should read about that. The VBA API is simply a few classes that translate VBA into commands for JSON API for data abstraction classes. You should download the emptycdataset.xlsm from Downloads and if you want to do some tests, create a sheet called dbab. This will be used to load some test data in. 
Since access to the API is protected by oauth2, first you’ll have to set up Excel to be able to talk oAuth2. Essentially this means creating a cloud project that can access Google Drive, and getting some client credentials. The one off sub in oAuth2Examples will set up your Excel to be able to automatically get and refresh access tokens when needed. The one off script looks like this and should contain your credentials from the cloud console. 

Next, you’ll set up your back end database(s). It’s easiest to start with creating a Google Workbook and using a sheet from that. Once you have it working, you can try some other back-ends. 
There are 2 classes involved – cDbab and cDbabResult. These are used to issue API commands and decode the results. As with the google apps script version, it all starts with getting a handle depending on the type of back end database you are accessing. Here’s a small function to generalize the getting of a handle. This is using my copy of the starter script, so you should make your own copy and point at the published version, setting up your database credentials in the properties of your copy of the script.

Now we have this, it’s simple to get a handler for any supported back-end. Here I’m getting 5 different kinds of back end databases and running the same unit tests on all of them. The only different for each database is these 3 parameters. 

Getting some test data

I actually keep my test data in a Google Sheet somewhere, so I can do the same tests on Google Apps Script as I do in VBA. So i start off the testing session by querying that sheet from VBA and copying the data down to an Excel sheet – and of course, I’m using the dbab API to do that.


These tests, which are the VBA version of the Google Apps Script tests referred to in Some test cases for various backends (which use the Database abstraction with google apps script library directly) look like this. There are many tests here, but I reproduce them so you can get a flavor of the various scenarios that can be handled. All this code can be found in the oauthexamples module of the emptycdataset.xlsm

The Code

Here’s the code for the 2 classes