Creating your own scriptDB environment

Update Nov2017
UPDATE: NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives.
Here’s what you need.
This is written from the perspective of A VBA API for scriptDB, but most of it also applies to scriptDB API for JavaScript
Page Content hide
1 Using oAuth2?
2 Making a scriptDb dispatcher
3 Making a handler.

Using oAuth2?

Note that this is not yet implemented for scriptDB API for JavaScript, and this paragraph applies to A VBA API for scriptDB.
If you are providing write access to scriptDB from VBA, or have some non-public data, you’ll want to protect it with oAuth2. This will ensure that only those whom you have allowed access to your handler can access it. You’ll need to go to the Google Cloud Console and create a project and read up on Google Oauth2 VBA authentication for how all that works. Once you have your client ID and client secret, you can register your PC for future oAuth2 authentication like this, substituting your client ID and client secret.
getGoogled("drive", , "xxxxxxxx.apps.googleusercontent.com", "xxxxxxxxxxxxx").tearDown

If you have already used oAuth2 from Excel Liberation on this computer, perhaps for some other scope – let’s say “analytics”, then you just need to do this

getGoogled("analytics", , , , , "drive")
If you have already registered this computer for oAuth2 with drive scope, you don’t need to do anything

Making a scriptDb dispatcher

Let’s say you want a scriptDB for testing, and another for production. We’ll use the dispatcher scriptDB as the testing one, and create a special one for production.
  • create new script, lets call it dbProduction. The only thing it needs is this. Save a version of it and grab the project properties code.
function showMyScriptDb() {
      return ScriptDb.getMyDb();
}
  • create your dispatcher script. Let’s call it dbDispatcher, with this code. Add the resource for the dbProduction library, save a version and grab the project properties key. You’ll need it for the handler.
function getDb(library) {
  if (library)  {
    return eval((library + "."  + "showMyScriptDb"))();
  }
  else {
    return ScriptDb.getMyDb(); 
  }
}


Making a handler.

Let’s say this is a fully function handler (no restriction on the types of things it can do). You’ll need a library reference to your dispatcher, and also to the cScriptDbCom library.  Let’s call this script dbHandler. Notice that i’ve parameterized the name of the dispatcher, so you’ll need to set that up or hardcode it.
You’ll need this code
// this is a handler for cScriptDbCom requests.
// requests to this script should be authenticated with oAuth2
// 2 further authentication parameters are expected in the head of each request, modelled on the parse.com API
// {"X-scriptDb-Application-Id":"somekey","X-scriptDb-REST-API-Key":"someotherkey"}
// these can be used by the caller to decide whether to allow the type of access being requested

var dispatcher = "dbDispatcher";
function doGet(e) {
    e.requestType = "GET";
    var c = new scriptDbCom.cScriptDbCom ( e,eval(dispatcher +".getDb")(e.parameter.library));
    return ContentService
            .createTextOutput(JSON.stringify(c.requestResult()))
              .setMimeType(ContentService.MimeType.JSON);  

}
function doPost(e) {
    e.requestType = "POST";
    var c = new scriptDbCom.cScriptDbCom ( e,eval(dispatcher +".getDb")(e.parameter.library));
    return ContentService
            .createTextOutput(JSON.stringify(c.requestResult()))
              .setMimeType(ContentService.MimeType.JSON);  
  
}
function workAround() {
  var x = ScriptDb.getMyDb();
  x.count({});
}

You’ll also need a reference to the dbDispatcher you created and a reference to library resource

ML7nE0jnmV4tCqTYKNkIykai_d-phDA33

Just like all GAS apps, it will need authorization before publishing it. Run the workAround function to do that. You’ll notice it does something pointless with scriptDB. The reason for this is, I think, is that since all scriptDB operations are delegated to a library, whatever that authorization process is that GAS does is not aware that the libraries are planning to do scriptDB things, and it will fail as a web app. I’ve no idea why, but I stumbled across this little quirk and wasted a fair bit of time on it.
Now save a version, publish your webapp, and grab the webapp url – you have all you need to start writing VBA apps.

For help and more information join our forum, follow the blog or follow me on Twitter