Tracking activity with ScriptDB

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.

 

Page Content hide
1 ScriptDB for tracking
2 Siloing
3 Reporting
4 Clearing
5 Centralizing
6 Where’s the code?
7 Summary

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.