If you are a regular on this blog or on the Excel Liberation site you’ll know that I’m a great fan of scriptDb – the cloud base noSql database for Google Apps Script – and like to play around with it. I’ve already released APIs for VBA (Excel) and JavaScript that can access and update scriptDB, along with a Google Apps Script handler which is used by the API to receive and deal with API requests.
So now that we have the capability to write apps in VBA and JavaScript that can use the same data and database and as Google Apps Script, I figured that we could use that to solve a different problem I’ve been thinking about for a while.
Getting data back into Excel from a web app.
On the Excel liberation site there is a capability to generate a mapping app from Excel data. The problem is that there isn’t any way to get any data, for example user entered enrichment, back into Excel.
Using the scriptDB APIs, we can create a temporary transient data exchange like this.
This means that an instrumented web page can collect data like this, and pass this back via a deadDrop key for later processing by a VBA app.
I’m calling this capability a deadDrop. I can’t remember why now, but I’ll stick with it. Some key characteristics are
- Conversations use the scriptDb and partition conversations by a unique key generated by the deadDrop API. Data is subject to the same authentication and permission protocol as already called out in the scriptDB API, in addition to the deadDrop Key which silos data for a specific conversation.
- Keys have a strict lifetime of 24 hours from the time they are first created, after which they are invalid and any data associated with them will be deleted.
- The actual physical scriptDb used is defined by the scriptDB API credentials you associate with your deadDrop key.
- You can use the public scriptDb for testing, and later create your own scriptDb environment locked down to you.
For more on all this, see Excel Liberation. Here’s a primer deck for more info.
