Accessing and scriptDB noSQL databases from Excel

I figured it would be pretty nice to be able use one of the cloud based noSQL databases directly from VBA, so that I could share data easily between various platforms. On Excel Liberation , I recently released a few APIs to open up cloud based noSQL databases to VBA access to make this possible.

In this post, I’m going to use the VBA APIs for Google Apps Script ScriptDB and to copy data between the two databases using VBA as the agent.

The APIs have very similar interfaces, so the coding is exactly the same once the connection has been made.

Here’s the main procedure.

The test will be to copy an entire class from to scriptDb and back again, and do a few counts and queries to compare before and after.

Private Sub scriptDBandParseCopy()
    ‘ copy from scriptDB to Parse
    Dim dbParse As cParseCom, dbScriptDb As cScriptDbCom, Class As String
    Class = “VBAParseCustomers”
    Set dbParse = getParsed(Class)
    Set dbScriptDb = getScriptDb(Class, “dbTest”)
    ‘ copy from scriptdb to parse
    dbCopyAny dbScriptDb, dbParse
    ‘ see what we have
    Debug.Print dbParse.count
    Debug.Print dbParse.getObjectsByQuery(JSONParse(“{‘country’:’Turkey’}”)).jObject.stringify(True)
    ‘ copy back again
    dbCopyAny dbParse, dbScriptDb
    ‘ see what we have
    Debug.Print dbScriptDb.count
    Debug.Print dbScriptDb.getObjectsByQuery(JSONParse(“{‘country’:’Turkey’}”)).jObject.stringify(True)
    ‘ clean up
End Sub

And here’s the common copy procedure – which remains oblivious to the flavor of data base being used.

Private Sub dbCopyAny(dbSource As Object, dbTarget As Object)
    Dim jobSkip As cJobject, job As cJobject
    ‘ delete everything in target db of this class
    ‘we have to do it in chunks because of potential query limits
    Set jobSkip = JSONParse(“{‘skip’:0}”)

    ‘ we’ll just use the default limit for a big query
        With dbSource.getObjectsByQuery(Nothing, jobSkip).jObject.child(“results”)
            If .children.count = 0 Or Not dbSource.isOk Or Not dbTarget.isOk Then Exit Do
            ‘ There are special reserved fields we need to delete between databases
            For Each job In .children
                dbTarget.createObject _
            Next job
            jobSkip.child(“skip”).value = jobSkip.child(“skip”).value + .children.count
        End With
    ‘ clean up
    dbTarget.batch (False)
End Sub

And here’s the result of the query – they match
      “results”:[         {
            “address”:”Ap #157-9046 At, Rd.”,
            “company”:”Vulputate Eu Odio Limited”,
            “coordinates”:”65.15408, -59.50909″,
            “region”:”Pays de la Loire”,
            “city”:”La Roche-sur-Yon”,
            “address”:”Ap #433-7194 Ante St.”,
            “company”:”Vestibulum Company”,
            “coordinates”:”23.12752, -77.15718″,
            “region”:”Northern Territory”,
            “address”:”251-8346 Vestibulum, Av.”,
            “company”:”Mi Tempor Lorem Incorporated”,
            “coordinates”:”-80.71595, -106.84″,

Here’s a couple of primer decks to get some background on how all this works.

For more like this see Excel Liberation.

About brucemcp 224 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

Be the first to comment

Leave a Reply

Your email address will not be published.