Google cloud datastore Database abstraction – Google Apps Script

Desktop liberation

What is the Cloud Datastore A massively scalable cloud based datastore Usually used along with Google App Engine APIs available for Python, Go, Java, Php A JSON API exists Has an SQL like query language (GQL) GQL is query only Only really supports flat level objects Free daily quota can be eaten up fairly

quickly Many capabilities, fairly complex It’s a database Jim, but not as we know it

Datastore with GAS

There is no API for Google Apps Script

Have to use the JSON API, which is complex and limited

Complex queries need pre-specified indexes

It’s conceptually different to normal databases and structures

Oauth2 is mandatoryDatabase abstraction aims to simplify

all that

Abstraction objective

With dbAbstraction, DataStore noSQL queries are the same for all supported backends. So far this includes

Scriptdb Parse.com Fusion Orchestrate.io Fusion Sheets Import.io Drive Properties DataStore

cDataStore driver componenetsfor GAS

Your Script cDataHandler library

cEzyOauth2 library

cCacheHandler library

Your webapp

App template

Your property store

cDataStore driver

Cloud DataStore

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

Datastore abstraction

The same as other database backends up to the driver

Caching, locking and oAuth2 built in Complex objects supported through

flattening with dot syntax Complex queries handled by

DataStore when index exists Non indexed queries automatically

handled by driver filtering

Example queries/counts

handler.query ( ); handler.query ({sex:”male”} ); handler.query ({sex:”male”,country:”USA”} ); handler.query ({sex:”male”,network:{google:{usage:”seldom”}}});Handler.query({sex:”male”,”network.google.usage”:”seldom”});Handler.query({sex:”female”,handler.constraint([[c.GT,50],[c.LT,60]])});Handler.query({},{limit:20,sort:”-age”});Handler.query({sex:”female”, age:handler.constraint([[c.GT,50],[c.LT,60]] ,

name:handler.constraint([[c.IN,[”smith”,”jones”]] )}, {sort:”network.google.usage”});handler.count ({sex:”male”} );

Example removes

handler.remove ( ); handler.remove ({sex:”male”} ); handler.remove({query ({sex:”male”,country:”USA”} ); handler.remove({sex:”male”,network:{google:{usage:”seldom”}}});Handler.remove({sex:”male”,”network.google.usage”:”seldom”});Handler.remove({sex:”female”,handler.constraint([[c.GT,50],[c.LT,60]])});Handler.remove({},{limit:20,sort:”-age”});

Example saves

handler.save({name:”john”,age:25,country:”USA”,network:{google:”seldom”,facebook:”often”}} );

handler.save([{name:”john”,age:25,country:”USA”,network:{google:”seldom”,facebook:”often”}}, name:”mary”,age:28,country:”UK”,network:{google:”often”,facebook:”never”}}]);

Example update

var result = handler.query ({network.google:”seldom”},undefined,1, true ); result.data.forEach(function(d) { d.target = true; });handler.update (result.handleKeys, result.data);

var result = handler.query ({network.google:”often”},{sort:”age”,limit:10},1, true); result.data.forEach(function(d) { d.target = true; });handler.update (result.handleKeys, result.data);

Example cache

var resultFromCache = handler.query ({network.google:”seldom”});var resultNoCache = handler.query

({network.google:”seldom”,undefined, 1});

Oauth2 pattern

A standard recipe

function doGet (e) { return doGetPattern(e, constructConsentScreen, doSomething,

'googleDatastore') ;}

1. The name of a function that returns an html string with a user consent dialog. Will only get called the first time doGet() is run to kick off the self maintaining access/refresh token details

2. The name of a function that does what your doGet() would normally do, and returns what it would normally return

3. A unique name against which to store the access/refresh token details in the property store

1 2 3

Credentials

Your datastore credentials and scopes should be stored in your properties store

EzyOauth2 will update your credentials object with refresh and access token information

Every time after the first time run, your access token will be refreshed automatically when necessary

Functions are provided in the pattern for your function to read and write properties, but the libraries never access your properties directly.

First time run

The ongoing pattern is more or less the same for both webapp and non-webapp

doGet() needs to be run at least once to provoke an authorization dialog

Non- webapps need a doGet() function run just once to get the refresh token infrastructure set up. You can delete the doGet() function after that.

The credentials

Code is in the Patterns example, and looks like this. Substitute your own credentials and run

function oneTimeSetProperties () { setAuthenticationPackage_ ({ clientId : “xxx.apps.googleusercontent.com", clientSecret : “xxx", scopes : ['https://www.googleapis.com/auth/datastore','https://www.googleapis.com/auth/

userinfo.email'], service: 'google', packageName: 'googleDatastore' });}

Credentials - properties store

Substitute your credentials and run this , one timefunction oneTimeSetProperties () { setAuthenticationPackage_ ({ clientId : “xxx.apps.googleusercontent.com", clientSecret : “xxx", scopes : ['https://www.googleapis.com/auth/datastore','https://www.googleapis.com/auth/userinfo.email'], service: 'google', packageName: 'googleDatastore' });}

This will get created

Library Keyslibrary key comments

cDataHandler Mj61W-201_t_zC9fJg1IzYiz3TLx7pV4j

Abstracted interface to back end databases, and all known drivers

cCacheHandler MXhfe1Z1GKU-_Qnd94LqcsKi_d-phDA33

Manages caching of query results

cNamedLock MBaYiatjgCSvDcsG6fHIFsyz3TLx7pV4j

Cross script locking of abstract resources

cEzyOauth2 MSaYlTXSVk7FAqpHNCcqBv6i_d-phDA33

Manages oAuth2 credentials and offline refreshing of access tokens

cFlatten MqxKdBrlw18FDd-X5zQLd7yz3TLx7pV4j

Flattens complex objects to 1 level dot syntax objects so they can be stored/queries in a 2 dimensional space

ezyOauth2 templates https://script.google.com/d/1ll5nvMOZL5YODaj71l0-XSaD0BBciG_zIV2I0Neu3Nz1LaKY6-4WiJAt/edit?usp=sharing

Patterns for web and nonweb apps for easy oath2 authentication. Take a copy of this

More information

Read about data abstraction using the datastore here.

Read about ezyOauth2 here. Contact me on google plus or at my

forum Join the

Google Apps Script Community