google apps script database abstraction exposed version
DESCRIPTION
TRANSCRIPT
Database abstraction exposedJSON API and a VBA client
Database abstraction – Google Apps Script
Desktop liberation
What is database abstraction - dbab
Google Apps Script libraries providing a noSQL API for a variety of backends
Provides reusability and transportability of code between databases, and simplified migration between them
Simple API to learn, with the most common capabilities exposed and standardized
A consistent results structure Locking, backing off , caching,
authentication and limits handled transparently
Abstraction supported storesWith dbAbstraction, noSQL queries are the same for
all supported backend stores. So far this includes scriptdb Parse.com Fusion Orchestrate.io Fusion Sheets Import.io (readonly) Drive Properties DataStore MongoDB
The Exposed version
A web app that exposes the GAS dbab as a JSON rest API
Handles POST and GET queries, updates, inserts, gets, count and removes
Exposes all dbab capabilities Oauth2 is mandatory to authenticate to
the webapp Authentication credentials for backend
databases are securely stored in your copy of its property store
Dbab webapp structure
cDataHandler library
cEzyOauth2 library
cCacheHandler library
Dbab webapp
App template
Your property store
Backend driver
Backend database
cFlattenerlibrary
noSQL queries
Access token
Google oauth2
infrastructure
Query caching
cNamedLocklibrary
Transaction locking
JSON API
Query translation
authentication
Cloud credentials
Access & refresh tokens
(un)Flatten objects
Your client
Oauth2 authentication
JSON REST API
Authentication
You authenticate to dbab via oAuth2 using Drive scope
Your published copy of dbab webapp already has your various authentication properties built in for your supported databases
If the backend database needs oauth2, that’s also handled by the dbab JSON api
API parameter summary
It’s a REST API that returns either JSON or JSONP. GET or POST is supported for all actions.
action=query|remove|save|update|get query= some noSQL query, the same syntax as the
regular GAS API params = some params (skip|limit|sort) as the regular
GAS API driver=scriptdb|mongolab|sheet|drive|orchestrate|parse|
datastore|fusion (any exposed backend) nocache=0|1 (use cache/don’t) siloid= roughly equivalent to a table name dbid = roughly equivalent to a database name driverid=0|1 (whether to include driver generated ids in
response) callback=some callback – ask for jsonp rather than json
API response
Same format as the GAS API{ handleCode: 0, // +ve number= good, -ve bad
handleError:’’, //’some text about an error’
data:[], // the results
driverIds:[], // if asked for, any keys the driver generates automatically
handlerIds:[] // if asked for, the key the driver considers to be the unique key for each data item
+ various other informational properties about handler version etc…
}
Writing APIs for other languagesNow it’s simple to write an API for another
language that looks like the standard GAS API GAS dbab handles the translation to various
backends, with new features and new supported drivers immediately available
Your API just needs to know how to talk to the GAS dbab JSON API
You can centralize your various credentials in the cloud, protected by oauth2
Your code can look very similar to the same code using the GAS API directly (language syntax constraints aside)
VBA API example structure
• Gets handler, creates nosql directives, deals with responsesVBA application
• Deals with oauth2 authentication to your web app. Translates directives to JSON API requests and handles responsesVBA API
• Deals with requests and responsesDbab GAS REST API
• deals with authentication credentials, caching. Gets handler and interprets nosql queriesDbab GAS API
• Translates nosql queries to backend syntax and handles, authentication, locking, responses and requestsDbab GAS driver
• Handles queries and returns data and responses to its driverVarious backends
Step 1 - Get authenticated
Oauth2 integration with VBA is covered elsewhere in detail
Create an application in Google Cloud Console
If using VBA Do a one off registration to your
Windows registry From now on you can simply do this to
get authorizedSet oauth2 = getGoogled("drive")
Step 2 – Set up your webapp Copy from this template Add any credentials needed for any
backend as described here Save a version and publish as a webapp –
(set to run as “user accessing the webapp”)
Take a note of the webapp URL Run doGet to authorize it If needed, run the web app once with ?
driver=datastore to provoke setting up of the refresh token structure
Step 3 – access JSON API
Do any setup needed to create your databases or sheets you are using as a backend
You can now access your webapp as a REST API to talk to the supported backends
If you want to use the VBA API, continue to step 4
Step 4 – Download Excel template
Get emptycdataset.xlsm from desktop liberation downloads
Set up your handler and web app URL Set handler = New cDbAb handler.setOauth2(oauth2).setEndPoint (_
"https://script.google.com/macros/s/xxxxxx/exec")
Set up your database type name, database and data silohandler.setDbName("sheet") _
.setDbId(“xxxx") _
.setSiloId(“yyyy”)
You can start…
VBA syntax
I’ve tried to keep the VBA syntax is as close to original Google Apps Script as possible within the limitations of the language.
VBA has no native JavaScript like objects or JSON, but I use cJobject throughout
The cJobject JSON parser allows both single and double quotes to be used in JSON eg.
{‘name’:’ethel’} can be used so it can more be easily represented as a VBA string “{‘name’:’ethel’}”
cDataset can be used to write and read excel workbooks to and from JSON
Examples
Code What it does
Set result = handler.remove() Removes everything from the handler’s silo
Set result = handler.save(testData) Saves data in the given cJObject. You can easily create a cJobject from an Excel sheet like this Set ds = New cDataSetSet testData = ds.load("dbab").jObject(, , , , "data")
Set result = handler.query() Get everything in the handler’s silo
Set result = handler.query(, "{'limit':2}")
Get the first 2 objects. Note that both single quotes and double quotes are allowed by this JSONParser to make it easier to enter JSON as a VBA string
Set result = handler.query(, "{'sort':'-name'}")
Get everything in the handler’s silo, sorted in reverse order using the property ‘name’
Examples – simple queries
Code What it does
Set result = handler.query(, "{'sort':'-name','skip':3}")
Sort by name and skip the first 3
Set result = handler.query("{'name':'ethel'}")
Select all objects whose name is ‘ethel’
Set result = handler.query( ("{'stuff':{'sex':'female'}}")
Select all objects where stuff.sex = ‘female’
Set result = handler.query( ("{'stuff.sex':'female'}")
Same as above. Note that you can use either deep or flattened objects for both data and queries. Regardless of how the driver has to store the data (for example two dimensional backends like sheets need to store flattened data), the result of the query will be natural, unflattened objects
Examples – keys, gets, updates and removes
Code What it does
Set result = handler.query("{'stuff.sex':'male'}", , 1, 1)
Select all objects where name is john or mary and stuff.sex is male, don’t use cache, and return the unique keys recognized by the driver for each object as well
handler.update(result.handleKeys, data)
Replace all the objects with the keys returned from the previous query with new data
handler.remove("{'stuff.sex':'male'}")
Remove all the objects where stuff.sex is male
Set r2 = handler.getObjects(result.handleKeys)
Get all the objects whose keys were returned by a previous query
Examples – queries with constraints
Code What it does
Set result = handler.query("{'name':" & handler.constraints("[['IN',['ethel','fred']]]") & "}"
Note that queries other than equality, need constraints. Unfortunately the syntax is a bit fiddly to construct as a VBA string. The function handler.constraints() converts your query into consumable syntax for the API. This query selects all objects whose name is in the given list
handler.query("{'stuff.age':" & handler.constraints("[['GT',25],['LTE',60]]") & "}")
Select all objects where stuff.age > 25 and <= 60
handler.query("{'stuff':{'age':" & handler.constraints("[['GT',25]]") & "}}")
Select all objects where stuff.age > 25
"{'stuff':{'sex':'male', 'age':" & handler.constraints("[['GTE',25],['LT',60]]") & "}}"
Select all objects where stuff.sex = ‘male’ and age is >= 25 and < 60
Examples – more queries with constraints
Code What it does
handler.query( "{'name':" & handler.constraints("[['IN',['john','mary']]]") & ",'stuff.sex':'male','stuff.age':" & handler.constraints("[['GT',25]]") & "}"
Select all objects where name is john or mary and stuff.sex is male and stuff,age > 25
handler.query("{'stuff.age':" & handler.constraints("[['GT',25],['LTE',60]]") & "}")
Select all objects where stuff.age > 25 and <= 60
handler.query( "{'stuff.age':" & handler.constraints("[['GT',25]]") & "}", "{'limit':1}“)
Select the first object where stuff.age > 25
handler.query( "{'stuff':{'sex':'male', 'age':" & handler.constraints("[['GTE',25],['LT',60]]") & "}}“)
Select all objects where stuff.sex = ‘male’ and age is >= 25 and < 60
Copying from a database to an Excel sheet
It’s simple to copy the database contents to a worksheet
Do a query Construct a sheet
Set result = handler.query()Set ds = makeSheetFromJob(result.data, “yoursheetname”)
Since an excel sheet is two dimensional,
any deep objects will be flattened to dot syntax so they can be represented as an excel table
Copying from an Excel sheet to a database
It’s simple to copy the worksheet contents to a database
Load the sheet and convert it to JSON Save to your selected back end
Set testData = ds.load(“yoursheetname”).jObject(, , , , "data") Set result = handler.save(testData)
Any flattened objects will be automatically
converted back to deep objects
More information
Read about Google Apps Script data abstraction here.
Read about the dbab JSON API here Read about the VBA API client here. Read about ezyOauth2 here. Contact me on google plus or at my
forum and share with others how you are using this API
Join the Google Apps Script Community