First of all you need to Download cDataSet.xlsm and go to the scriptDBCom module, where you’ll find various examples.
Readonly, no oAuth2.
Get authenticated
Private Sub firstTimescriptdbComReadonly() Dim scriptdbCom As cScriptDbCom Set scriptdbCom = New cScriptDbCom With scriptdbCom.init(, _ "getStarted", _ "scriptDBPrimer", _ "primerApp", _ "xliberation", _ False, _ "scriptDBPrimer", _ False, _ "https://script.google.com/macros/s/AKfycbx7_gPpc38Map4QqHOQrzx_kvIX00nfYGO9OLq8_cMD486Va6M/exec") .tearDown End With End Sub
What just happened is that you’ve created an encrypted entry in your registry, called “getStarted”. In future when you need to access this from any workbook you can simply refer to it by its name. We’ll deal with the meaning of the parameters later, but the URL refers to the Google Apps Script handler that will handle all requests associated with this registry entry, as described in Handler for cScriptDbCom requests
Data in the scriptDBPrimer library
Do some counts
Private Sub primerCount() Debug.Print getScriptDb("VBAParseData", "getStarted").count End Sub
And in the VBAParseCustomers library
Private Sub primerCount() Debug.Print getScriptDb("VBAParseCustomers", "getStarted").count End Sub
How many customers in the United States
Debug.Print getScriptDb("VBAParseCustomers", "getStarted").count(JSONParse("{'country':'United States'}"))
Do some queries
Private Sub primerQueries() With getScriptDb("VBAParseCustomers", "getStarted") Debug.Print .getObjectsByQuery(JSONParse("{'country':'United States'}")).jObject.stringify(True) End With End Sub
results
{ "status":"good", "count":3, "results":[ { "region":"GO", "zip":"D51 4LF", "country":"United States", "city":"Luziânia", "siloId":"VBAParseCustomers", "customerid":85, "email":"sit.amet@congueturpis.com", "address":"2195 Scelerisque Road", "name":"Hammond", "company":"Consectetuer Rhoncus Nullam Industries", "coordinates":"-49.77372, 82.53103", "objectId":"S321108000293" }, { "region":"Castilla y León", "zip":"B9L 8T4", "country":"United States", "city":"Segovia", "siloId":"VBAParseCustomers", "customerid":97, "email":"nascetur@orciUt.net", "address":"P.O. Box 271, 4700 Tellus St.", "name":"Nash", "company":"Nascetur Ridiculus Institute", "coordinates":"53.60934, 6.94959", "objectId":"S321108000305" }, { "region":"Principado de Asturias", "zip":"11713", "country":"United States", "city":"Oviedo", "siloId":"VBAParseCustomers", "customerid":37, "email":"quis@anteipsum.org", "address":"Ap #770-5598 Faucibus Rd.", "name":"Lyons", "company":"Sed Dolor Fusce Foundation", "coordinates":"-36.59556, -133.78963", "objectId":"S321097057213" } ] }
Get data for a particular customer
Private Sub primerQueries2() With getScriptDb("VBAParseData", "getStarted") Debug.Print .getObjectsByQuery(JSONParse("{'customerid':1}")).jObject.stringify(True) End With End Sub
{ "status":"good", "count":4, "results":[ { "siloId":"VBAParseData", "customerid":1, "value":1050, "quantity":2, "date":{ "__type":"Date", "iso":"2013-08-22T00:00:00.000Z" }, "invoiceid":175, "objectId":"S321113127055" }, { "siloId":"VBAParseData", "customerid":1, "value":4230, "quantity":9, "date":{ "__type":"Date", "iso":"2013-10-08T00:00:00.000Z" }, "invoiceid":145, "objectId":"S320995946256" }, { "siloId":"VBAParseData", "customerid":1, "value":1560, "quantity":3, "date":{ "__type":"Date", "iso":"2013-05-21T00:00:00.000Z" }, "invoiceid":151, "objectId":"S320995946262" }, { "siloId":"VBAParseData", "customerid":1, "value":3900, "quantity":10, "date":{ "__type":"Date", "iso":"2013-07-25T00:00:00.000Z" }, "invoiceid":246, "objectId":"S321104310680" } ] }
Get data by unique ID
{ "status":"good", "count":1, "results":[ { "siloId":"VBAParseData", "customerid":1, "value":3900, "quantity":10, "date":{ "__type":"Date", "iso":"2013-07-25T00:00:00.000Z" }, "invoiceid":246 } ] }
Deal with a Date
With getScriptDb("VBAParseData", "getStarted").getObjectById("S321104310680").jObject.child("results.1") Debug.Print getAnIsoDate(.child("date")) End With
7/25/2013
Check that this is really read-only access
Private Sub primerShouldFail() getScriptDb("VBAParseData", "getStarted").deleteObjects End Sub
For help and more information join our forum, follow the blog or follow me on Twitter