using Google Apps ScriptDB as a lockBox

In various posts and articles I’ve been exploring uses of the Google Apps scriptDB datastore. One of the things I’ve been mulling over is how to simplify the whole oAuth experience when dealing with REST queries that need authentication.

When you read up on examples of using oAuth, they are usually server orientated – in PHP, java, python etc. One of the reasons for this is the difficulty in obscuring the various credentials such as your consumer key, consumer secret and so on, when in javaScript (and of course VBA)

For those of us that do not have server based development environments, Google Apps Script comes to the rescue.

  1. Google Apps ScriptDB gives the opportunity to securely store data that can be shared amongst scripts, yet kept private to those without access.
  2. Urlfetch.oauthConfig() deals with the “mess with your head” business of oAuth
  3. The ContentService is able to generate REST like responses.
It occurred to me that all this together would allow for the secure storage of oAuth credentials, a simple method of using them, and a way to do proxy Rest queries independent of the calling language or environment and free of cross domain problems in javaScript and general futziness in non web based platforms such as VBA. This would mean that the calling language would not need an oAuth implementation, and neither would it need to know any of the oAuth credentials.
Using ScriptDB as a lockbox
I’m not going to cover all this in one post, but will start by looking at using scriptDB as a lockbox. In organizing scriptDB into silos, I covered how to systematically encode data so that different kinds of data could live side by side in a shared scriptDB, and also discussed how to share that scriptDB across multiple scripts. Let’s build that out now to create a lockBox content shared across all your Google Apps Script.
We’ll reuse the mcpher library referred to here for many of the utility functions, but you also need to create a script that will have a shared scriptDB across all your scripts and other resources that need access to your lockbox. I created a new script called myStuff, and with ‘Manage versions’, created something I could share across multiple scripts. Naturally I would set the protection level on this to be shared with only me.
The scriptDB associated with this script is the one I’m going to use as my lockbox. That means that any other script that references this as a library can have access to this scriptDB, if they have the db() object. I can do this with this simple function in myStuff.
/**
 * myStuffDb return the shared DB for myStuff
 * @return {ScriptDbInstance} the db
 */
function myStuffDb() { 
  return ScriptDb.getMyDb();
}

 

Next I’ll create some other script, lets call it publicTest, that will use myStuff as a library, in addition to the public shared mcpher.library (i’m going to need that later), which I set up through Manage Resources
Let’s store something in the myStuff scriptDB  – I could create this function in either the myStuff library or in any function that accesses it.
function createlockBoxTest(){
  return mcpher.createStuff ("lockboxtest", myStuff.myStuffDb(),
     {
      site: 'ramblings.mcpher.com',
      name: 'excel liberation',
      myNumber: 1234
    } );
}

And when we access that,

function testGetMyStuff() {
  Logger.log(JSON.stringify(mcpher.getMyStuff("lockboxtest",myStuff.myStuffDb())));
}

we get, with the lockbox data in .result.myStuff

{
    "status": {
        "code": "good",
        "reason": "lockboxtest found in your stuff"
    },
    "result": {
        "siloId": "lockboxtest",
        "help": "for details see ramblings.mcpher.com",
        "timeStamp": 1347535115084,
        "myStuff": {
            "site": "ramblings.mcpher.com",
            "name": "excel liberation",
            "myNumber": 1234
        },
        "userStamp": "bruce@mcpher.com"
    }
}

 

Security
  • Only scripts that are authorized to use your library can have access to your scriptDB as the only way they can find out the db() is through myStuff.myStuffDb()
  • mcpher.getMyStuff() has an additional security check that only allows the creator of the entry to access it.
           if (result.userStamp != Session.getUser().getEmail()) {
             response.status.code = "bad";
             response.status.reason = siloName + "does not belong to you";
           }

 

Accessing your lockbox like a rest query
 
In order to get to this stuff from outside of Google Apps script, you can publish this as below, and get the same result as a rest query
function doGet(e) {

    return ContentService
          .createTextOutput(JSON.stringify(mcpher.getMyStuff(e.parameter ? e.parameter.entry : null, myStuffDb())))
          .setMimeType(ContentService.MimeType.JSON);
          
}
accessing  whateverthegivenlink?entry=lockboxtest will give this
{
    "status": {
        "code": "good",
        "reason": "lockboxtest found in your stuff"
    },
    "result": {
        "siloId": "lockboxtest",
        "help": "for details see ramblings.mcpher.com",
        "timeStamp": 1347535115084,
        "myStuff": {
            "site": "ramblings.mcpher.com",
            "name": "excel liberation",
            "myNumber": 1234
        },
        "userStamp": "bruce@mcpher.com"
    }
}

 

Next steps
This all started as how to do oAuth and rest queries, using google apps script as a proxy. We’ll get to that in the next post.  It should be as simple as this, where your oAuth credentials have been stored in your scriptDB lockbox.
function testOauth() {
  return oAuthProxy("twitterauth",  "http://api.twitter.com/1/statuses/user_timeline.json" ).getContentText()
}

 

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.