Using script db as a deaddrop to pass data between GAS, JS and Excel

60 %
40 %
Information about Using script db as a deaddrop to pass data between GAS, JS and Excel
Technology

Published on February 4, 2014

Author: BruceMcPherson1

Source: slideshare.net

Description

Here's how to use Google Apps Script ScriptDB as an automated short term data relay for feedback between web sites, Excel and Google Apps Script.

ScriptDB as a deaddrop box Passing data between VBA, GAS and JS

what is this about? • • • • A short term message store to pass data between VBA, GAS and JavaScript Uses the scriptDB API for each of these platforms Segregate conversations by the use of keys Uses cookies and registry for authentication purposes

scriptDB API COMPONENTS Same as using scriptDB as a nosql database Your ScriptDB dispatcher Your Your Your code code VBA/JS code Registry /cookie /local storage simple noSql VBA /JS API encrypted oauth2 credentials oauth2 / rest Your Your code Multiple code ScriptDB Your GAS webapp Handler (s) GAS Library API

Deaddrop components Your messageid Your Your Your code code VBA code simple noSql Your Your code Your code JS code Message handler JS/GAS/VBA scriptDB API environment Short lifetime data Short lifetime data Short lifetime data deaddrop Registry ScriptDB credentials Scriptdb cookie/ local storage

DeadDrop class Related message concepts can be identified by a class name of your choosing. You can have as many classes as you want Each conversation creates an instance of a class with a unique key A class instance has a strict lifetime of 24 hours The instance expires and is deleted 24 hours after creation

deadDrop handle You get a handle to a deadDrop class like this The latest instance for a class getDeadDrop(yourClass, scriptDBEntry) A new instance for a class getDeadDrop(yourClass, scriptDBEntry,true) An existing specific class instance getDeadDrop(yourClass, scriptDBEntry,,deadDropKey)

deadDrop class Entry A deaddrop class entry is stored in the registry or as a cookie. A class entry knows which scriptDBEntry to use Before deadDrop can be used, it must be registered to use the scriptDBEntry. This is a one time operation, shared with other scriptDB usage. The scriptDB entry knows how to access scriptDB, which library and which handler to use, and is subject to permissions allowed by the scriptDB entry

scriptDB registration - VBA Private Sub firstTimescriptdbMessages() Dim scriptdbCom As cScriptDbCom Set scriptdbCom = New cScriptDbCom With scriptdbCom.init(, _ "messages", _ ,_ "messagesKey", _ "xliberation", _ False, _ "scriptDBMessages", _ False, _ "https://script.google.com/macros/s/AKfycbzvnq2IZu3JpngnuVxfnPAZYPooVBTULkUyiLFnItfvRxY0NrI/exec") .tearDown End With End Sub

scriptDB registration - JS function firstTimeMessages() { // full access to scriptDBPrimer database, oAuth not required - uses scriptdbrequesthandler new cScriptDbCom().setScriptCredentials( { endPoint : gasHandlerEndPoints.scriptdbrequesthandler, restAPIKey : 'messagesKey', scopeEntry : 'rest', credentialsEntry: 'messages', clientKey:'xliberation', library: 'scriptDBMessages', needDebug: false, needOauth: false } ); }

Public deaddrop For testing you may use the credentials for the ‘messages’ scriptDB’ entry Remember that each class instance expires 24 hours after creation If you find all this useful, you should create your own scriptDb environment

Usage example In googleMapping.xlsm, I create a web page that plots Excel data on a Google Map. The use case is to retrieve data interactively through the map, and update the source Excel sheet.

How it’s done In VBA, when the mapping app is created, a new deaddrop conversation is initiated, and the key is passed to the app. With getdeaddrop(yourClass, "messages", True) With .scriptDb Set job = JSONParse("{'subject':'" & subject & "','info':'xliberation public data for testing'}") .createObject(job).flush job.tearDown End With addDeadDrop = .key End with

User interaction // Infobox is instrumented to collect data and write to deaddrop using scriptDB API. // capture change event - what we'll do here is to update dead drop The deadrop key is passed to the web app and we get a scriptDb handle for it mcpherAddEvent(ci, "change", function(e) { // we have a change - record the update via the dead drop if (pDb) { pDb = getScriptDb(pParams.deaddrop,"messages"); pDb.createObject ({uniqueId: cj.uniqueId, type:'comment', title:cj.title, cj:cj.childIndex, comments:e.srcElement.value}); // empty batch - we'll do it immediately - it'll happen asynch anyway pDb.finalFlush() .done(function(data) { // nothing to do }) .fail(function(data){ alert ("failed to flush to deaddrop"); }); } }, false, true);

Clearing The log class googleMapping googleMapping googleMapping key googleMapping395633308428428 googleMapping395937493918511 googleMapping397080577155843 registered 2/4/2014 11:07 2/4/2014 11:58 2/4/2014 15:08 processed 2/4/2014 12:18 2/4/2014 12:18 Each time a web app is created, this log is updated. Later processing retrieves any data from the deaddrop for each key and updates the original spreadsheet with the data from the deadDrop messsage, marks this as processed and deletes the messages from the deaddrop With getdeaddrop(dr.cell("class").toString, "messages", False, dr.cell("key").toString) ' now we can get all the message data for this With .scriptDb .getObjectsByQuery Set data = .jObject.child("results") Set subject = data.find("subject") With ds.load(subject.toString) …..etc….

The results Ive used the {subject:xxx} to determine the original sheet, the {uniqueId:xxx} and the {comments:xxxx} to update the comments field in the original sheet with data collected by the generated web app.

How to try it Download googleMapping.xlsm Set to enable deaddrop in the geoCodingParameters sheet use custom enable deaddrop deaddrop test TRUE custom code to use if true, then deaddrop conversation will be enabled and lgged - the custom code selected should match this Register your computer to the test drop box locally (on the venues tab) Plot on Google Map, click on some places, add data Process the deadDrop Log Check the comments column on the venues sheet

Next steps Read all about the scriptDB API Read more about deadDrop Contribute to our forum with use case ideas. Build your own scriptDb environment and applications

Add a comment

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...

Related pages

Using script db as a deaddrop to pass data between GAS, JS ...

... to pass data between GAS, JS and Excel; Using script db as a deaddrop to pass data between GAS, JS ... Using script db as a deaddrop to pass data ...
Read more

ScriptDB as a dead drop - Desktop Liberation

Using script db as a deaddrop to pass data between GAS, JS and ... the original Excel data. ... of Google Apps Script, javaScript and VBA using ...
Read more

How to pass arrays between javaScript ... - Desktop Liberation

How to pass arrays between javaScript and VBA. ... Serializing Excel data for Google motion charts. ... desktop liberation by ...
Read more

Enabling collaboration between public agencies and using ...

... presented at the GIS Forum MENA where he discussed how Atkins is using geospatial data ... Enabling collaboration between public agencies and using ...
Read more

Finding new Customers using D&B and Excel Power Query ...

Data & Analytics; Design; Devices & Hardware; Economy & Finance; Education; Engineering; ... Share Finding new Customers using D&B and Excel Power Query.
Read more

[214]CX Server OPC Script Using Excel - Documents

[214]CX Server OPC Script Using Excel. by oooperrodelmalooo. on Nov 29, 2014. Report Category: Documents. Download: 0 Comment: 0. 115. views ...
Read more

Creating and Managing a Budget Using Data Excel - Education

Share Creating and Managing a Budget Using Data Excel. Embed ...
Read more

Desktop Liberation - mcpher.com

How to pass arrays between ... Going Gas: from VBA to Apps Script. Featuring ... automatically building data driven web based apps from Excel data, ...
Read more