Parse.com as a substitute for ScriptDB in Google Apps Script

As you will know by now, ScriptDB is now deprecated. A year or so ago, I published some articles on alternatives to ScriptDB on the desktop liberation site, one of which was Parse.com. This has turned out to be one of the most popular topics, and one which I get a lot of questions of about.

I don’t do many posts on this blog anymore, see this post for why, but I thought I may as well use this, the 200th post, to summarize the status of the parse.com API shared library for Google Apps Script, and quickly show how to get your data from scriptDB to Parse.com.

There are 2 methods of using this library from Google Apps Script.

  • Directly, where you would be sure that parse.com is the backend you would want to use
  • Through a database abstraction library, which provides standardized access to a growing number of backend databases
I would recommend the latter, since it has more features, is easier to use, gives the flexibility to move or to mix database backends without changing your code (and we can learn the lesson from ScriptDB how valuable that can be), and is able to deal with constraints in an abstracted way.  It also automatically provides caching, and does exponential backoff on on rate limited services. It does not yet handle execution time quotas on GAS, but I have some tunkerings for that in the works. Finally, there is also a simple rest API hosted as a Google Apps Script webapp giving complete access to all this from other languages and platforms.
How it works
For Parse.com you need to sign up and get a couple of API keys. You need to store them in a userproperty store, and pass them to whichever of the library options you choose. Let’s look at the data abstraction option first. As an example, I’ll show how to migrate your data from scriptDB below.

function migratingFromScriptDb() {

  var tableName = ‘VBAParseCustomers’;
  var enums = cDataHandler.dhConstants.DB;
  var userStore = PropertiesService.getUserProperties();

 // get the data from scriptDB – i have  a scriptDB driver, so Im using that.
 // you may want to read all your scriptDB data some other way to get your objects into an array.

  var dbHandler = new cDataHandler.DataHandler (tableName,enums.SCRIPTDB,undefined,’mydb’,ScriptDb.getMyDb()); 
  var theResult = dbHandler.query ();
  if (theResult.handleCode <0) throw (JSON.stringify(theResult));
  
  var theData = theResult.data;

  // to parse.com – good choice for all
  deleteAndCopy ( theData , tableName,”mp”, enums.PARSE, JSON.parse(userStore.getProperty(“parseKeys”)));

}  

function deleteAndCopy (data,tableName, id , type , ob ) {

  // get a handler
  var handler = new cDataHandler.DataHandler (tableName,type,undefined,id,ob);
  // delete existing data
  var result = handler.remove();
  if (result.handleCode <0) throw (JSON.stringify(result));
  
  // save the new data
  var result = handler.save(data);
  if (result.handleCode <0) throw (JSON.stringify(result));
  
  return handler;

}
Although both versions use batching, using the direct version gives you more control of how the batching happens, but it does mean you need to manage it yourself. Here’s the deleteAndCopy() using the direct parseCom library. In either case, the parse.com database created is identical. 
function deleteAndCopyDirect (data,tableName, id , type , ob ) {
  // get a handler
  var userStore = PropertiesService.getUserProperties();
  var parseCom = cParseCom.getParsed(tableName,JSON.parse(userStore.getProperty(“parseKeys”)));
  
  // delete everything
  var w = parseCom.deleteObjects().flush();
  if (!w.isOk()) {
      throw (“failed to delete objects:” + w.browser().url() + “:” + w.browser().status() + “:” + w.browser().text());
  }
  
  // write new stuff
  parseCom.batch(true);
  data.forEach(function(d) {
    parseCom.createObject(d);
  });
  var w = parseCom.flush().batch(false);
  if (!w.isOk()) {
      throw (“failed to create objects:” + w.browser().url() + “:” + w.browser().status() + “:” + w.browser().text());
  }

}
Here’s a snip from the resultant parse.com table

Performance

Result from a) read 100 records from scriptDB, b) delete 100 in Parse then c) create 100 new ones. 
Execution succeeded [3.746 seconds total runtime]

Authentication

In the documentation on the direct library, I refer to using encryption for storing your API keys in the property store. I found that the encryption was taking a long time under caja (as much as 10 seconds), and in any case with the new property stores in GAS it’s really not necessary. I recommend therefore that you store your credentials in the property store for your script as in the examples above, and don’t use the encryption referred to in the documentation, although I’ll leave that method there for backward compatibility.

Other operations

Querying, updating, getting by id, counting and removing are all supported in both versions. For the library keys and details, read more here 

(function() { var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true; po.src = ‘https://apis.google.com/js/plusone.js’; var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s); })();

Author: bm082975

Leave a Reply

Your email address will not be published. Required fields are marked *