Database access to a variety of databases from Excel

Random blog posts

Here’s a VBA app that uses Database abstraction with google apps script via the DataHandler library. You need to download the latest version of the dbAbstraction.xlsm workbook from Downloads. Note that a Google Script Webapp is providing the gateway from your VBA app to each of these database back ends. It’s made up of these components

DataHandler REST API which uses the DataHandler and a variety of drivers to access Import.io, Fusion, Sheets, Drive, Parse.com, orchestrate.io , scriptDb and maybe some others that I’ve added after this post.

The VBA JSON and cDataset libraries in the latest version of cDataSet.xlsm

You would normally have created your own copy of DataHandler REST API so you could access your own files, but for the purposes of this example, since we are still in beta, the webapp accessed by Excel can write to testbases for each of the formats. Since others will have access to it, do not put any sensitive or private data there, and I will be clearing it out regularly.

Once you have played around with this, you can set up your own environment fairly easily, as described in DataHandler REST API

I have implemented Google Oauth2 VBA authentication which will allow you to protect your private sheets. You’ll see from the example how to implement that for your PC.

Let’s get into the code – it’s self explanatory, but How to use cJobject and How to use cDataSet will explain the VBA libraries in use here.

Using this you can read, write and query a variety of databases directly from your VBA code. This simple example does all of the following

Gets some test data from import.io using Driver Import.io and writes it to an Excel sheet
Deletes the data in the back end database, and copies the test data from the Excel sheet to the back end database
Does a count

Here’s the code. It’s in the dbAbstraction.xlsm workbook, which also contains all the libraries you’ll need. see Downloads. and here are the results in their respective homes. Excel (from Driver Import.io) Fusion DriverParse DriverDrive [ { "key": "up1398763280434", "data": { "skills": "Intellect Cyperpathic Link Strenght and durability Energy repulsors Missiles", "species": "Human", "origin": "Earth", "photo": "http://cdn.import.io/integrate/img/iron-man.png", "alter_egos": "Anthony Edward "Tony" Stark", "name": "Iron Man", "more_info": "http://en.wikipedia.org/wiki/Iron_Man", "photo__source": "img/iron-man.png", "more_info__text": "Iron Man on Wikipedia", "more_info__title": "Wikipedia", "photo__alt": "Iron Man using repulsors", "photo__title": "Iron Man" } }, { "key": "ih1398763280434", "data": { "skills": "Intellect Sheild-fighting Hyperkinetic senses Field commanding", "species": "Human", "origin": "Earth", "photo": "http://cdn.import.io/integrate/img/captain-america.png", "alter_egos": "Steven "Steve" Rogers", "name": "Captain America", "more_info": "http://en.wikipedia.org/wiki/Captain_America", "photo__source": "img/captain-america.png", "more_info__text": "Captain America on Wikipedia", "more_info__title": "Wikipedia", "photo__alt": "Captain America looking forlorn", "photo__title": "Captain America" } }, { "key": "jj1398763280435", "data": { "skills": "Super strength Speed and endurance Anger empowerment", "species": "Human", "origin": "Earth", "photo": "http://cdn.import.io/integrate/img/hulk.png", "alter_egos": "Dr Robert Bruce Banner", "name": "Hulk", "more_info": "http://en.wikipedia.org/wiki/Hulk_(comics)", "photo__source": "img/hulk.png", "more_info__text": "Hulk on Wikipedia", "more_info__title": "Wikipedia", "photo__alt": "Hulk looking angry and being shot at", "photo__title": "Hulk" } }, { "key": "qg1398763280435", "data": { "skills": "Durability and longevity Strength Dimensional transportation Electric manipulation Weather manipulation Big hammer", "species": "Asgardian", "origin": "Asgard", "photo": "http://cdn.import.io/integrate/img/thor.png", "alter_egos": "Thor Odinson", "name": "Thor", "more_info": "http://en.wikipedia.org/wiki/Thor_(Marvel_Comics)", "photo__source": "img/thor.png", "more_info__text": "Thor on Wikipedia", "more_info__title": "Wikipedia", "photo__alt": "Thor looking at destruction", "photo__title": "Thor" } }, { "key": "jk1398763280435", "data": { "skills": "Slowed aging Enhanced immune system Superior athletic condition Hand to hand combat training Hypnosis Psychological defences", "species": "Human", "origin": "Earth", "photo": "http://cdn.import.io/integrate/img/black-widow.png", "alter_egos": "Natalia Alianovna Romanova Natasha Romanova", "name": "Black Widow", "more_info": "http://en.wikipedia.org/wiki/Black_Widow_(Natalia_Romanova)", "photo__source": "img/black-widow.png", "more_info__text": "Black Widow on Wikipedia", "more_info__title": "Wikipedia", "photo__alt": "Black Widow in a martial-arts fighting pose", "photo__title": "Black Widow" } }, { "key": "by1398763280435", "data": { "skills": "Grandmaster archer Trick arrows Arm strength Swordsman", "species": "Human", "origin": "Earth", "photo": "http://cdn.import.io/integrate/img/hawkeye.png", "alter_egos": "Clinton Francis "Clint" Barton", "name": "Hawkeye", "more_info": "http://en.wikipedia.org/wiki/Hawkeye_(comics)", "photo__source": "img/hawkeye.png", "more_info__text": "Hawkeye on Wikipedia", "more_info__title": "Wikipedia", "photo__alt": "Hawkeye preparing to fire an arrow", "photo__title": "Hawkeye" } }

DriverOrchestrate

HTTP/1.1 200 OK
Date: Tue, 29 Apr 2014 09:47:32 GMT
Content-Encoding: gzip
X-ORCHESTRATE-REQ-ID: 49465ba0-cf83-11e3-917d-12313d2f7cdc
Server: nginx
Vary: Accept-Encoding, User-Agent
Content-Type: application/json
Connection: keep-alive
Content-Length: 1144
{
    "count": 6,
    "total_count": 6,
    "results": [
        {
            "path": {
                "collection": "excelDemo",
                "key": "ep1398763273610",
                "ref": "632934156c99b3da"
            },
            "value": {
                "skills": "Intellect Cyperpathic Link Strenght and durability Energy repulsors Missiles",
                "species": "Human",
                "origin": "Earth",
                "photo": "http://cdn.import.io/integrate/img/iron-man.png",
                "alter_egos": "Anthony Edward "Tony" Stark",
                "name": "Iron Man",
                "more_info": "http://en.wikipedia.org/wiki/Iron_Man",
                "photo__source": "img/iron-man.png",
                "more_info__text": "Iron Man on Wikipedia",
                "more_info__title": "Wikipedia",
                "photo__alt": "Iron Man using repulsors",
                "photo__title": "Iron Man"
            },
            "score": 1
        },
        {
            "path": {
                "collection": "excelDemo",
                "key": "jt1398763273677",
                "ref": "902f9c76e8bf3f0e"
            },
            "value": {
                "skills": "Super strength Speed and endurance Anger empowerment",
                "species": "Human",
                "origin": "Earth",
                "photo": "http://cdn.import.io/integrate/img/hulk.png",
                "alter_egos": "Dr Robert Bruce Banner",
                "name": "Hulk",
                "more_info": "http://en.wikipedia.org/wiki/Hulk_(comics)",
                "photo__source": "img/hulk.png",
                "more_info__text": "Hulk on Wikipedia",
                "more_info__title": "Wikipedia",
                "photo__alt": "Hulk looking angry and being shot at",
                "photo__title": "Hulk"
            },
            "score": 1
        },
        {
            "path": {
                "collection": "excelDemo",
                "key": "hr1398763273708",
                "ref": "65f26ff7fe55fdc4"
            },
            "value": {
                "skills": "Durability and longevity Strength Dimensional transportation Electric manipulation Weather manipulation Big hammer",
                "species": "Asgardian",
                "origin": "Asgard",
                "photo": "http://cdn.import.io/integrate/img/thor.png",
                "alter_egos": "Thor Odinson",
                "name": "Thor",
                "more_info": "http://en.wikipedia.org/wiki/Thor_(Marvel_Comics)",
                "photo__source": "img/thor.png",
                "more_info__text": "Thor on Wikipedia",
                "more_info__title": "Wikipedia",
                "photo__alt": "Thor looking at destruction",
                "photo__title": "Thor"
            },
            "score": 1
        },
        {
            "path": {
                "collection": "excelDemo",
                "key": "pm1398763273741",
                "ref": "31a43e80a1cf6d6c"
            },
            "value": {
                "skills": "Slowed aging Enhanced immune system Superior athletic condition Hand to hand combat training Hypnosis Psychological defences",
                "species": "Human",
                "origin": "Earth",
                "photo": "http://cdn.import.io/integrate/img/black-widow.png",
                "alter_egos": "Natalia Alianovna Romanova Natasha Romanova",
                "name": "Black Widow",
                "more_info": "http://en.wikipedia.org/wiki/Black_Widow_(Natalia_Romanova)",
                "photo__source": "img/black-widow.png",
                "more_info__text": "Black Widow on Wikipedia",
                "more_info__title": "Wikipedia",
                "photo__alt": "Black Widow in a martial-arts fighting pose",
                "photo__title": "Black Widow"
            },
            "score": 1
        },
        {
            "path": {
                "collection": "excelDemo",
                "key": "bm1398763273645",
                "ref": "457fdb7371099f31"
            },
            "value": {
                "skills": "Intellect Sheild-fighting Hyperkinetic senses Field commanding",
                "species": "Human",
                "origin": "Earth",
                "photo": "http://cdn.import.io/integrate/img/captain-america.png",
                "alter_egos": "Steven "Steve" Rogers",
                "name": "Captain America",
                "more_info": "http://en.wikipedia.org/wiki/Captain_America",
                "photo__source": "img/captain-america.png",
                "more_info__text": "Captain America on Wikipedia",
                "more_info__title": "Wikipedia",
                "photo__alt": "Captain America looking forlorn",
                "photo__title": "Captain America"
            },
            "score": 1
        },
        {
            "path": {
                "collection": "excelDemo",
                "key": "do1398763273771",
                "ref": "f5237a609423908d"
            },
            "value": {
                "skills": "Grandmaster archer Trick arrows Arm strength Swordsman",
                "species": "Human",
                "origin": "Earth",
                "photo": "http://cdn.import.io/integrate/img/hawkeye.png",
                "alter_egos": "Clinton Francis "Clint" Barton",
                "name": "Hawkeye",
                "more_info": "http://en.wikipedia.org/wiki/Hawkeye_(comics)",
                "photo__source": "img/hawkeye.png",
                "more_info__text": "Hawkeye on Wikipedia",
                "more_info__title": "Wikipedia",
                "photo__alt": "Hawkeye preparing to fire an arrow",
                "photo__title": "Hawkeye"
            },
            "score": 1
        }
    ]
}

DriverSheet

DriverScriptDb
[
{
"siloId": "excelDemo",
"data": {
"skills": "Grandmaster archer Trick arrows Arm strength Swordsman",
"species": "Human",
"more_info__text": "Hawkeye on Wikipedia",
"origin": "Earth",
"photo__alt": "Hawkeye preparing to fire an arrow",
"photo__title": "Hawkeye",
"photo": "http://cdn.import.io/integrate/img/hawkeye.png",
"alter_egos": "Clinton Francis "Clint" Barton",
"more_info__title": "Wikipedia",
"name": "Hawkeye",
"more_info": "http://en.wikipedia.org/wiki/Hawkeye_(comics)",
"photo__source": "img/hawkeye.png"
}
},
{
"siloId": "excelDemo",
"data": {
"skills": "Intellect Sheild-fighting Hyperkinetic senses Field commanding",
"species": "Human",
"more_info__text": "Captain America on Wikipedia",
"origin": "Earth",
"photo__alt": "Captain America looking forlorn",
"photo__title": "Captain America",
"photo": "http://cdn.import.io/integrate/img/captain-america.png",
"alter_egos": "Steven "Steve" Rogers",
"more_info__title": "Wikipedia",
"name": "Captain America",
"more_info": "http://en.wikipedia.org/wiki/Captain_America",
"photo__source": "img/captain-america.png"
}
},
{
"siloId": "excelDemo",
"data": {
"skills": "Slowed aging Enhanced immune system Superior athletic condition Hand to hand combat training Hypnosis Psychological defences",
"species": "Human",
"more_info__text": "Black Widow on Wikipedia",
"origin": "Earth",
"photo__alt": "Black Widow in a martial-arts fighting pose",
"photo__title": "Black Widow",
"photo": "http://cdn.import.io/integrate/img/black-widow.png",
"alter_egos": "Natalia Alianovna Romanova Natasha Romanova",
"more_info__title": "Wikipedia",
"name": "Black Widow",
"more_info": "http://en.wikipedia.org/wiki/Black_Widow_(Natalia_Romanova)",
"photo__source": "img/black-widow.png"
}
},
{
"siloId": "excelDemo",
"data": {
"skills": "Super strength Speed and endurance Anger empowerment",
"species": "Human",
"more_info__text": "Hulk on Wikipedia",
"origin": "Earth",
"photo__alt": "Hulk looking angry and being shot at",
"photo__title": "Hulk",
"photo": "http://cdn.import.io/integrate/img/hulk.png",
"alter_egos": "Dr Robert Bruce Banner",
"more_info__title": "Wikipedia",
"name": "Hulk",
"more_info": "http://en.wikipedia.org/wiki/Hulk_(comics)",
"photo__source": "img/hulk.png"
}
},
{
"siloId": "excelDemo",
"data": {
"skills": "Intellect Cyperpathic Link Strenght and durability Energy repulsors Missiles",
"species": "Human",
"more_info__text": "Iron Man on Wikipedia",
"origin": "Earth",
"photo__alt": "Iron Man using repulsors",
"photo__title": "Iron Man",
"photo": "http://cdn.import.io/integrate/img/iron-man.png",
"alter_egos": "Anthony Edward "Tony" Stark",
"more_info__title": "Wikipedia",
"name": "Iron Man",
"more_info": "http://en.wikipedia.org/wiki/Iron_Man",
"photo__source": "img/iron-man.png"
}
},
{
"siloId": "excelDemo",
"data": {
"skills": "Durability and longevity Strength Dimensional transportation Electric manipulation Weather manipulation Big hammer",
"species": "Asgardian",
"more_info__text": "Thor on Wikipedia",
"origin": "Asgard",
"photo__alt": "Thor looking at destruction",
"photo__title": "Thor",
"photo": "http://cdn.import.io/integrate/img/thor.png",
"alter_egos": "Thor Odinson",
"more_info__title": "Wikipedia",
"name": "Thor",
"more_info": "http://en.wikipedia.org/wiki/Thor_(Marvel_Comics)",
"photo__source": "img/thor.png"
}
}
]
See Database abstraction with google apps script for more on this topic.

Subpages

All formats are available from O'Reilly, Amazon and all good bookshops. You can also read a preview on O'Reilly

A video course over about 8 hours and 70 lessons taking you through the basics of Apps Script and JavaScript. Available at O'Reilly, Amazon, Infinite Skills & all good media outlets

Google Apps Script for Beginners: A video course over about 8 hours and 70 lessons taking you through the basics of Apps Script and JavaScript. Available from O'Reilly, Infinite Skills and all good media outlets

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

Desktop Liberation

The definitive resource for Google Apps Script and Microsoft Office automation

Database access to a variety of databases from Excel