In messing around with scriptDB, I covered some usages of scriptDB when it first came out, along with a method of siloing data to keep it organized. Here’s a usage that might be useful for tracking things – You could for example, implement this to track who is opening what workbook.
Tracking
The Idea here is that you use your scriptDb (either one linked to a particular workbook, or a shared one you use for centralized to tracking) to record when certain events start and finish using Google Apps Script. Here’s an example of logging something that happened, 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()); // ....... //finished doing something mcpher.trackDeparture(record1); }
Reporting
Because the data for tracking is siloed from any other data in your scriptDb, we can report on tracking activity easily like this, to a worksheet named ‘logger’
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 DB
In the example shown, I’m using a scriptDB associated with the current workbook – I’ve created a function to return my scriptDB.
function getPrivateDb() { // returns the scriptDB Im going to use privately for my account return ScriptDb.getMyDb(); }
Ideally, though, we might want to record all activity centrally. In this case we would pick a scriptDB to host the data lets call it centralizedDb – and then create a script file in that project with the getPrivateDb() function, and create a library of that project using the manage versions command from the file menu. Then in each spreadsheet that you want to track things in, you would reference centralizedDb. Your code would now look like this and every spreadsheet would access the same scriptDb.
function reportEverything() { mcpher.trackInitialize (centralizedDb.getPrivateDb()); mcpher.trackReportAll ("logger"); }
This tracker code can be found in the mcpher google apps script library which is publicly available here
References
Project Key :
MEQ3tE5y5_cTOAgUbUKSIAiz3TLx7pV4j
Source code
https://script.google.com/d/1hhJ8M6z99XccL8WRq2d24-pWGwhq8EfYNaQIQV0CEe5gE1HbBoF4X9W_/edit
Tracker code
You can find the code in the track module of the above library and in the gist below.