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 readonly ScriptDB mode, as well as my Rest Handler. Later on you'll create your own. Get authenticatedWe wont 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 (readonly access to my restHandler) on your PC. This is a one off exercise, for which you'll need to run this code. 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 libraryThe 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 countsThis will show how many objects are of the VBAParseData class in the scriptDBPrimer library 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 United States Debug.Print getScriptDb("VBAParseCustomers", "getStarted").count(JSONParse("{'country':'United States'}")) Do some queriesGet data for customers in USA 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 results { "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 result 7/25/2013 Check that this is really readonly accessTry to delete some records, and you wont be allowed to. Private Sub primerShouldFail() getScriptDb("VBAParseData", "getStarted").deleteObjects End Sub Since I have only granted readonly access in my handler to this app/url combination, then deletion should fail like this. You can get me on Google plus, Twitter or this forum. See more on this at An API for scriptDB, and very closely related APIS at parse.com - nosql database for VBA and parse.com - noSQL database for GAS |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Things that have been deprecated > Google Apps ScriptDB > A VBA API for scriptDB >