First of all you need to Download cDataSet.xlsm and go to the scriptDBCom module, where you’ll find various examples.
Readonly, no oAuth2.
To get used to the API we’ll first of all use some of my test data in a read-only ScriptDB mode, as well as my Rest Handler. Later on, you’ll create your own.
Get authenticated
We won’t need to bother with oAuth2 for this since the handler we’ll use is set up for public access, but we will need to register this app (read-only access to my restHandler) on your PC. This is a one-off exercise, for which you’ll need to run this code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 |
Data in the scriptDBPrimer library
The data in the scriptDBprimer library has been copied from two sheets in the cDataSet workbook – VBAParseData and VBAParseCustomers. This is the same data used in parse.com – nosql database for VBA and parse.com – noSQL database for GAS
Do some counts
This will show how many objects are of the VBAParseData class in the scriptDBPrimer library
|
1 2 3 |
Private Sub primerCount() Debug.Print getScriptDb("VBAParseData", "getStarted").count End Sub |
|
1 2 3 |
Private Sub primerCount() Debug.Print getScriptDb("VBAParseCustomers", "getStarted").count End Sub |
|
1 |
Debug.Print getScriptDb("VBAParseCustomers", "getStarted").count(JSONParse("{'country':'United States'}")) |
Do some queries
Get data for customers in the USA
|
1 2 3 4 5 |
Private Sub primerQueries() With getScriptDb("VBAParseCustomers", "getStarted") Debug.Print .getObjectsByQuery(JSONParse("{'country':'United States'}")).jObject.stringify(True) End With End Sub |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
{ "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" } ] } |
|
1 2 3 4 5 |
Private Sub primerQueries2() With getScriptDb("VBAParseData", "getStarted") Debug.Print .getObjectsByQuery(JSONParse("{'customerid':1}")).jObject.stringify(True) End With End Sub |
results
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
{ "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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
{ "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
|
1 2 3 |
With getScriptDb("VBAParseData", "getStarted").getObjectById("S321104310680").jObject.child("results.1") Debug.Print getAnIsoDate(.child("date")) End With |
result
|
1 |
7/25/2013 |
Check that this is really read-only access
Try to delete some records, and you won’t be allowed to.
|
1 2 3 |
Private Sub primerShouldFail() getScriptDb("VBAParseData", "getStarted").deleteObjects End Sub |
Since I have only granted read-only access in my handler to this app/URL combination, then deletion should fail like this.
For help and more information join our forum, follow the blog or follow me on Twitter