Deprecated

scriptDB is a javaScript object database for Google Apps Script project. Here are a few key facts

  • Every project can have its own scriptDB
  • There are no Tables (this is a noSQL database). Instead it is up to you to use data attributes to separate your data into ‘silos’.
  • Querying is done ‘by example’
  • There are various limits and quotas
  • The data is persistent and accessible ‘across projects’
  • Using shared libraries can be tricky, since the library would use it’s own project’s scriptDB rather than the one of the running project.

Here is a summary of few of its uses for this site

Example of use

The Rest to Excel library is a good candidate to use the scriptDB to store its library data for both Excel and GAS. However, since I will be using the scriptDB for many things, I wanted to first formalize how I was going to approach data siloing, since that could get tiresome in the future.


I’m using a couple of new classes – cScriptDbSilo and cScriptDbSiloItem to automate that. Here’s a test example that uses 2 differentt scriptDb – a local one, and the one associated with the mcpher shared libraryScriptDB also makes a useful lockBox.

function testThings(){
  // delete everything from library and also from local
  mcpher.scriptDbSilo().remove();
  mcpher.scriptDbSilo().remove(undefined,ScriptDb.getMyDb());
  // add some stuff
  mcpher.scriptDbSilo("some library stuff").save({stuff:"library stuff"});
  mcpher.scriptDbSilo("some local stuff",ScriptDb.getMyDb()).save({stuff:"local stuff"});
  mcpher.scriptDbSilo("some local stuff").save({stuff2:"more local stuff"});
  // show it
  mcpher.DebugPrint(mcpher.scriptDbSilo("some library stuff").query().next());
  var results =  mcpher.scriptDbSilo("some local stuff").query();
  while (results.hasNext() ) {
    mcpher.DebugPrint(results.next());
  }
  // delete it
  mcpher.scriptDbSilo("some library stuff").remove(); 
  mcpher.scriptDbSilo("some local stuff").remove(); 
}

That gives this result

{"siloId":"some library stuff","stuff":"library stuff"}
{"siloId":"some local stuff","stuff":"local stuff"} 
{"siloId":"some local stuff","stuff2":"more local stuff"}

 

Walkthrough

  • Delete everything in the mcpher  scriptDb

mcpher.scriptDbSilo().remove();

  • Delete everything in the local scriptDb. Note that since the DbSilo classes exist in the mcpher library, they will be by default operating on the mcpher scriptDb – so we need to pass the DB to use – in this case the local one (ScriptDb.getMyDb());

mcpher.scriptDbSilo().remove(undefined,ScriptDb.getMyDb());

  • Add some stuff to the mcpher scriptDb. I’ll use the key I’ve given below now to reference any data associated with this particular silo. The underlying classes take care of tweaking the stored objects to silo them.

mcpher.scriptDbSilo("some library stuff").save({stuff:"library stuff"});

  • Add some stuff to the local scriptDb. Just like the delete example, we need to specify that this is going on in the local DB this time. However now that we have created the silo named “some local stuff”, the associated DB will now be stored and will not need to be mentioned again as per the 2nd example.

mcpher.scriptDbSilo("some local stuff",ScriptDb.getMyDb()).save({stuff:"local stuff"});mcpher.scriptDbSilo("some local stuff").save({stuff2:"more local stuff"});

  • Do a query of the results in the library scriptDb

mcpher.DebugPrint(mcpher.scriptDbSilo("some library stuff").query().next());

  • Do a query of the local stuff, in this case we need to loop to show the multiple results

  var results =  mcpher.scriptDbSilo("some local stuff").query();  while (results.hasNext() ) {    mcpher.DebugPrint(results.next());  }

  • delete the data just added

  mcpher.scriptDbSilo("some library stuff").remove();   mcpher.scriptDbSilo("some local stuff").remove(); 

Silos

Using a Silo manager like this enforces some standardization on how you partition data in this ‘tableless’ database. You can have as many silos as you want, each identified by some unique key. 

Changing the default scriptDb

If the default scriptDb you want to use is not the one associated with the mcpher library, then you can change it by an initial call like this:

mcpher.createDbSilos(ScriptDb.getMyDb());


or later by changing the xDb property

mcpher.scriptDbSilo().xDb = ScriptDb.getMyDb();

Deleting data in Silos

You are only able to delete data that you created. Items are written with a time stamp and the user who wrote it. Deleting checks that the user ID matches before allowing deletion. I’ll also be updating this shortly to restrict shared library to read only. 

The cScriptDbSilo code

You can use the mcpher library mentioned in  Creating a Google Apps Script Library to access this and to see the code.  A snapshot of the code is in this gist and reproduced below.

Code

The code is in the mcpher library. See here for how to incorporate it into your project.

Summary

Take a look at how the From VBA to Google Apps Script for more like this.