NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives.
I leave this article here for interest.
In Using scriptDB, I covered some usages of scriptDB,along with a method of siloing data to keep it organized. Since scriptDb gives the possibility of shared persistent storage here’s a usage that might be useful for tracking things.
ScriptDB for tracking
In Apps Script timer collection i covered how to measure how long certain activities took, but that was mainly for performance tuning. If you want to track activities, then you need some kind of persistent storage accessible from multiple places. You could for example, track who is opening what workbook.
Siloing
Using the idea of siloing the scriptDB, we can set aside a slice of it to track when certain events start and finish using Google Apps Script. Here’s an example of logging a couple of events, and how long it took.
function testDb () { // this is where im centrally tracking all activities mcpher.trackInitialize (getPrivateDb()); // start doing something var record1 = mcpher.trackArrival("doing something with " + mcpher.WorkbookName()); //....do something inside that var record2 = mcpher.trackArrival("making tea" ); //... mcpher.trackDeparture(record2); //finished doing something mcpher.trackDeparture(record1); // report on it mcpher.trackReportAll ("logger"); }
Reporting
We can report on tracking activity easily like this, to a worksheet named ‘logger’ function
function reportEverything() { mcpher.trackInitialize (getPrivateDb()); mcpher.trackReportAll ("logger"); }
Clearing
Similarily, clearing out all tracking data is straightforward.
function deleteEverything() { mcpher.trackInitialize (getPrivateDb()); mcpher.trackDeleteAll(); }
Centralizing
One of the advantages that scriptDB gives us is that we can access the same db from multiple places. In the example, we use a function getPrivateDb()
to tell us which Db is to be used. To use the one private to the current spreadsheet script, you can create a function as follows
function getPrivateDb() { // returns the scriptDB Im going to use privately for my account return ScriptDb.getMyDb(); }
However, much more usefully, you can use a shared DB by creating a script library , lets call it myLibrary, and create getPrivateDb() there. By adding a reference to myLibrary in each of your spreadsheets, you can get access to the same scriptDb. I recommend you create a getPrivateDb() in each spreadsheet script, which return the DB of a shared scriptDB as follows.
function getPrivateDb() { // returns the scriptDB that is shared return myLibrary. getPrivateDb (); }
Where’s the code?
All shared code can be found in the mcpher google apps script library which is publicly available as follows References Project Key : MEQ3tE5y5_cTOAgUbUKSIAiz3TLx7pV4j Source code: https://script.google.com/d/1hhJ8M6z99XccL8WRq2d24-pWGwhq8EfYNaQIQV0CEe5gE1HbBoF4X9W_/edit Module: track
Summary
Take a look at how the From VBA to Google Apps Script for more like this. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.