The REST API interface is pretty straightforward, but it is JSON. VBA doesn’t natively support JSON, but this library includes cJobect , which deals with all of that for you. This packages all the things you can do from the client in a simple library, and is has much the same call formats as the JavaScript and Apps Script versions.
Where to get it
The code is in the workbook “effex-demo-markers-excel”, which you can get from the effex folder in downloads. It’s also in github, where you’ll also find the documentation for each method, generated automatically by vbagit.
Code
Most of the demos and libraries mentioned in these pages are available in their own github repo, but for convenience there is a consolidated repo where I’ll push all the associated code. If you’d like to contribute, please make a PR there.
Demo
[youtube https://www.youtube.com/watch?v=Lxh180oqRNc?rel=0&wmode=opaque]Before starting
You’ll need to get a boss key. If you want to play with the tests below (or on github). You can get a boss key by visiting the API home, registering, and creating one in the Console. I also recommend you take a look at the tutorial there to get a feel for some of the concepts. The workbook contains a demo of a VBA version of Sheets and Maps collaboration with cache – effex demo, as well as a series of tests. for the demo, the boss key and other access keys should be first be stored in the registry (you’ll find a once off function in the code to do it) first store the boss Function storeBoss() ” the boss key comes from the console ec.setProperty “boss”, JSONParse(“{‘key’:’—-yourbosskey from effex console—-‘}”)End Function then generate a set of keys to play with ‘/**’ * making some keys to use for effex’ * @return {object} the keys’ */ Public Function generateKeys() ‘// the boss key is already here Dim boss As cJobject, keys As cJobject Set boss = ec.getProperty(“boss”) Debug.Assert isSomething(boss) ‘// make the other keys and store them Set keys = ec.makeKeys(boss.toString(“key”)) ec.setProperty “keys”, keys Debug.Print keys.stringifyEnd Function
Examples
This is a series of tests that exercise the common functions available in the library. Check the log file for which Urls the requests are turned into. Note that these tests are exactly the same as the ones in Apps Script library for Ephemeral exchange , except translated in to VBA. Option ExplicitPrivate Sub testEfx() Dim efx As cEffexApiClientVBA, bossKey As String, result As cJobject, _ writers As cJobject, readers As cJobject, updaters As cJobject, _ keys As cJobject, someData As cJobject, data As cJobject, alias As cJobject, _ otherAlias As cJobject, otherData As cJobject, updateAlias As cJobject, _ writerAlias As cJobject ‘// set up client Set efx = New cEffexApiClientVBA efx.setProd().setVerbose (True) ‘// boss key comes from console /// replace this with your own bossKey = “bx——————6” ‘// check service is up Set result = efx.ping() assure isSomething(result) And result.cValue(“ok”), “ping”, result ‘ // give up if its not If (Not result.cValue(“ok”)) Then MsgBox (“service is not up:” + JSONStringify(result)) Exit Sub End If ‘// get some service info Set result = efx.info() assure isSomething(result) And result.cValue(“ok”), “info”, result ‘ // check boss key hasnt expired Set result = efx.validateKey(bossKey) assure isSomething(result) And result.cValue(“ok”), “validate bosskey”, result ‘ // get 1 writer key expiringin in 5 minutes Set writers = efx.generateKey(bossKey, “writer”, JSONParse(“{‘seconds’:300}”)) assure isSomething(writers) And writers.cValue(“ok”) And writers.child(“keys”).children.Count = 1, “writerkeys”, writers ‘ // get 2 reader keys expiringin in 5 minutes Set readers = efx.generateKey(bossKey, “reader”, JSONParse(“{‘seconds’:300,’count’:2}”)) assure isSomething(readers) And readers.cValue(“ok”) And readers.child(“keys”).children.Count = 2, “readerkeys”, readers ‘ // get 1 updater key expiringin in 5 minutes Set updaters = efx.generateKey(bossKey, “updater”, JSONParse(“{‘seconds’:300}”)) assure isSomething(updaters) And updaters.cValue(“ok”) And updaters.child(“keys”).children.Count = 1, “updaterkeys”, updaters ‘ // set the keys up as default so we dont have to bother specifying them later efx.setKeys JSONParse(“{” & _ “‘updater’:'” & updaters.toString(“keys.1”) & _ “‘,’writer’:'” & writers.toString(“keys.1”) & _ “‘,’reader’:'” & readers.toString(“keys.1”) & _ “‘}”) ‘ // more convenient for later Set keys = efx.getKeys() ‘ //—reading and writing Set someData = JSONParse(“{‘name’:’xyz’,’a’:[1,2,3],’b’:2000}”) ‘ // write is a reserved word in vba .. have renamed as xwrite Set data = efx.xwrite(someData) assure isSomething(data) And data.cValue(“ok”), “write-post”, data ‘ // read it back with the same key Set result = efx.read(data.toString(“id”), keys.toString(“writer”)) assure isSomething(result) And result.cValue(“ok”) And sameOb(result.child(“value”), someData), “read”, result ‘ // do it again, but use a GET for writing Set data = efx.xwrite(someData, keys.toString(“writer”), “get”) assure isSomething(data) And data.cValue(“ok”), “write-get”, data ‘ // read it back with the same key Set result = efx.read(data.toString(“id”), keys.toString(“writer”)) assure isSomething(result) And result.cValue(“ok”) And sameOb(result.child(“value”), someData), “read-get”, result ‘ // now do all that with some text data Dim textData As String textData = “some text data” ‘ // write is a reserved word in vba .. have renamed as xwrite Set data = efx.xwrite(textData) assure isSomething(data) And data.cValue(“ok”), “write-post-text”, data ‘ // read it back with the same key Set result = efx.read(data.toString(“id”), keys.toString(“writer”)) assure isSomething(result) And result.cValue(“ok”) And result.toString(“value”) = textData, “read-post-text”, result ‘ // do it again, but use a GET for writing Set data = efx.xwrite(textData, keys.toString(“writer”), “get”) assure isSomething(data) And data.cValue(“ok”), “write-get-text”, data ‘ // read it back with the same key Set result = efx.read(data.toString(“id”), keys.toString(“writer”)) assure isSomething(result) And result.cValue(“ok”) And result.toString(“value”) = textData, “read-get-text”, result ‘ //– assigning readers and updaters Set data = efx.xwrite(someData, keys.toString(“writer”), “post”, _ JSONParse(“{‘readers’:'” + keys.toString(“reader”) + _ “‘,’updaters’:'” + keys.toString(“updater”) + “‘}”)) assure isSomething(data) And data.cValue(“ok”), “write-for-others”, data ‘ // read it back with a reader key Set result = efx.read(data.toString(“id”)) assure isSomething(result) And result.cValue(“ok”) And sameOb(result.child(“value”), someData), “read-reader”, result ‘ // read it back with a reader key Set result = efx.read(data.toString(“id”), keys.toString(“updater”)) assure isSomething(result) And result.cValue(“ok”) And sameOb(result.child(“value”), someData), “read-updater”, result ‘ // update it Set result = efx.update(textData, data.toString(“id”)) assure isSomething(result) And result.cValue(“ok”), “update”, result ‘ // check it took – i’ll just use the reader key Set result = efx.read(data.toString(“id”)) assure isSomething(result) And result.cValue(“ok”) And result.toString(“value”) = textData, “read-updated”, result ‘ //—-work with aliases Set alias = efx.registerAlias(keys.toString(“writer”), keys.toString(“reader”), data.toString(“id”), “somename”) assure isSomething(alias) And alias.cValue(“ok”), “alias”, alias ‘ // read it back with a reader key, using the data alias Set result = efx.read(alias.toString(“alias”)) assure isSomething(result) And result.cValue(“ok”) And result.toString(“value”) = textData, “assign-alias”, result ‘ // write another rec to the same alias Set otherData = efx.xwrite(someData, keys.toString(“writer”), “post”, _ JSONParse(“{‘readers’:'” + keys.toString(“reader”) + _ “‘,’updaters’:'” + keys.toString(“updater”) + “‘}”)) assure isSomething(otherData) And otherData.cValue(“ok”), “write-for-others-alias”, otherData ‘ // assign the alias to the new data Set otherAlias = efx.registerAlias(keys.toString(“writer”), keys.toString(“reader”), otherData.toString(“id”), alias.toString(“alias”)) assure isSomething(otherAlias) And otherAlias.cValue(“ok”) And alias.toString(“alias”) = otherAlias.toString(“alias”), “other-alias”, otherAlias ‘ // read it back with a reader key, using the data alias Set result = efx.read(alias.toString(“alias”)) assure isSomething(result) And result.cValue(“ok”) And sameOb(result.child(“value”), someData), “read-otheralias”, result ‘ // assign and update alias to an updater key Set updateAlias = efx.registerAlias(keys.toString(“writer”), keys.toString(“updater”), otherData.toString(“id”), alias.toString(“alias”)) assure isSomething(updateAlias) And updateAlias.cValue(“ok”) And alias.toString(“alias”) = updateAlias.toString(“alias”), “updater-alias”, updateAlias ‘ // update it Set result = efx.update(textData, alias.toString(“alias”)) assure isSomething(result) And result.cValue(“ok”), “update-alias-write”, result ‘ // check it took – i’ll just use the reader key Set result = efx.read(alias.toString(“alias”)) assure isSomething(result) And result.cValue(“ok”) And result.toString(“value”) = textData, “read-update-alias”, result ‘ // try deleting the underlying data item using the alias.. should fail because we didnt assign an alias to the writer key Set result = efx.remove(alias.toString(“alias”)) assure isSomething(result) And Not result.cValue(“ok”) And result.cValue(“code”) = 404, “remove-should-fail”, result ‘ // now this should work Set writerAlias = efx.registerAlias(keys.toString(“writer”), keys.toString(“writer”), otherData.toString(“id”), alias.toString(“alias”)) assure isSomething(writerAlias) And writerAlias.cValue(“ok”) And alias.toString(“alias”) = writerAlias.toString(“alias”), “writer-alias”, writerAlias Set result = efx.remove(alias.toString(“alias”)) assure isSomething(result) And result.cValue(“ok”), “remove-should-work”, result ‘ // check the underlying is gone Set result = efx.read(alias.toString(“alias”), keys.toString(“writer”)) assure isSomething(result) And Not result.cValue(“ok”) And result.toString(“code”) = 404, “read-should-fail”, result ‘ clean up those objects result.tearDown writerAlias.tearDown updateAlias.tearDown data.tearDown otherData.tearDown alias.tearDown otherAlias.tearDown someData.tearDownEnd SubPrivate Function sameOb(a As cJobject, b As cJobject) As Boolean ‘// compare cjobjects ‘// need to drop the parent as stringify keeps it Dim ac As cJobject, bc As cJobject ‘// clone them & detach them Set ac = a.clone().sever Set bc = b.clone().sever sameOb = JSONStringify(ac) = JSONStringify(bc) ” dont need these any more ac.tearDown bc.tearDownEnd FunctionPrivate Function assure(b As Boolean, message As String, result As cJobject) As Boolean Dim verbose As Boolean, mess As String ‘// by default verbose = True If isSomething(result) Then mess = JSONStringify(result) End If If (Not b) Then Debug.Print “failed:” + message + ” : result : ” + mess End If If (b And verbose) Then Debug.Print “passed:” + message End IfEnd Function
For more like this, see Google Apps Scripts snippets.
Return to Ephemeral Exchange to learn more