google apps script database abstraction exposed version

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