Deprecated
NOTE – as of 20 Nov 2017, Ephemeral exchange is migrating to a Firestore/ Firebase cloud function back end. See <a href=”https://ramblings.mcpher.com/ephemeral-exchange/migrating-from-app-engine-to-cloud-functions”>Migrating from App Engine to Cloud function</a>

It’s pretty easy to use data from Sheets to drive an App that enriches that data using the Maps API. But let’s say that you want to use this same app in multiple circumstances – say to enrich data from Excel, from Docs, from Sheets – from other sources. You can use the Ephemeral exchange as a cache to act as a dropbox between multiple platforms. This article will look at the Maps application that’ll be used in a number of demos. Note that this service is still in preview, so any URLS mentioned here will change in the future.

Demo

[youtube https://www.youtube.com/watch?v=Lxh180oqRNc?rel=0&wmode=opaque]

Where to get it

The app = effex-demo-markers –  is available  on github and includes the Ephemeral exchange library for JavaScript.

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.

What it does

This app gets its data from the Effex store, and will allow you to enrich it using Google Maps.

  • Plot markers with infowindows on a map from given data.
  • If lat and lng not present in the input data, then it will geocode the given address.
  • You can enter info to describe the marked place. If you change the address, it will be freshly geocoded.
  • Add new markers by rightclicking somewhere on the map.
  • If no address is given, reverse geocoding will try to figure it out.
  • You can drag markers around. If you do this, the new position will be gecoded.
  • You can remove markers to delete data.

When done, you can update the store and any other apps interested in that data can now pick it up. Here’ what the app looks like with an info window active  to allow changes. 

How to communicate what data is to be used.

The app needs to know the key of some data in cache along with an authorized updater key to be able to read and make changes to it.The Url looks like this appurl?updater=updaterKey&item=itemAliasOrId You can try accessing the app live here, but the data or keys it will use are mine and will be expired. That’s why you need to create your own – but more of that later.

How it updates the cache store with changes

The update Store button will use the effex API to write the enriched data to the store using the same keys as was passed to it. Other processes which have delegated the enrichment to this app can now pick up the results by accessing the store using the effex API.

How to create data to be enriched

Well it depends on which platform(s) you want to be able to collaborate, but for this example, I’m going to use Google Sheets.

Getting a Boss Key

The first step is to get a Boss key. This allows you to create keys for reading, writing and updating the store. You can get one over on the console area of the Ephemeral Exchange site. I recommend you spend a little time running through the tutorial too – it doesn’t take long. Here’s the dialog

Libraries for Apps Script

The effex API is a REST API so you can construct the calls yourself using URLFetch or you can use the Apps Script library for Ephemeral exchange, where these have been wrapped up into a simple interface. Alternatively, there is a library for Sheets (cAppsMapsEffex) which has further encapsulated common things you’d want to do from a sheet that I’ve created for this demo – behind the scenes it uses the Apps Script library for Ephemeral exchange. Depending on how much work you want to do, it’s up to you which you choose, but this example uses  cAppsMapsEffex. Here are the references for these

19rhki6VDTWk4v1RDb6u1d5E-nNaQq8sXCnRnFzUpp4V4lmZ9Z6R_PP9n (cEffexApiClient) – github

1mTo3A6LQEPKAFyVlRxoMgL9n4FTFi_-Lk1V_kiR47LJLZIsa-gzwg_97 (cAppsMapsEffex) – github

The data sheet

My input sheet looks like this – the demo is expecting to find these columns. I’ve already pushed and pulled this data once, where it populated the lat/lng and clean address for me. 

The local code

I’m using the property store for my keys, as described in Sheets and Maps collaboration with cache – effex demo. I need a boss key, and use the API to generate the other keys I’m likely to need, and store them in theProperty store like this/** * makeing some keys to use for effex * @return {object} the keys */function generateKeys () {    // store all the keys in the script properties  var props = PropertiesService.getScriptProperties();    // the boss key is already here  var bossKey = props.getProperty("effexboss");    // make some keys and store them  var keys =  ec.makeKeys (bossKey);    props.setProperty("effexkeys", JSON.stringify (keys));    return keys;} Here’s my keys – I’ll use these for everything.  I’ve also added some stuff to UI menu to make it easier to call up. // add to menu on openfunction onOpen () {  var ui = SpreadsheetApp.getUi();  ui.createMenu("effex")  .addItem("create in store", "createFromSheet")  .addItem("update store", "updateFromSheet")  .addItem("pull from store", "pullSheet")  .addToUi();}

Pushing data

We’re ready to push the sheet data to the store.  The only decision you need to make here is whether you want an alias for this data (you’ll be able to use the same url parameters in the maps app every time you push (until the alias expires), or whether you want to use the data item’s id each time (each time you push an item, a new id is created but the alias keeps track of the new item id for you). My example uses an alias (“effex-demo-markers”). If you prefer to use the native ids, just omit that argument. There’s 2 variations – when creating a new item in the store, I use the keys from the properties service, and create a brand new item. If updating, I use a given updater key, and update an existing data item.

function createFromSheet(){

pushItems (true)

}

function updateFromSheet(){

pushItems (false)

}

/**

* push active sheet to effex

*/

function pushItems(create) {
// we’ll need keys

var props = PropertiesService.getScriptProperties();

var keys = JSON.parse (props.getProperty(“effexkeys”));

// the values to push

var sheet = SpreadsheetApp.getActiveSheet();

var values = sheet.getDataRange().getValues();

var alias = dataAlias || sheet.getName();

// objectify data and push it

if (create) {

var result = ec.pushDataForUpdate (ec.objectify(values) , keys, alias);

// need to change the updater key to use what just happened

updater = keys.updater;

}

else {

// updating an existing item

var result = efx.update(ec.objectify(values), alias, updater || keys.updater);

}

if (!result.ok) {

throw ‘failed to push data ‘ + JSON.stringify (result);

}

// just show what happened

var ui = SpreadsheetApp.getUi();

if (ui) {

ui.alert (“markers url:”+demoUrl +”?updater=” + result.key + “&item=” + result.alias);

}

} That ends with a convenience reminder of how to run the maps app. 

Updating data

Now I head over to the Maps app, and see my data marked, but I want to add a couple of new places. I’ve added the Taj Mahal and the Acropolis. You can add a new place by right clicking somewhere on the map. Just changing the address will move the marker to the right place if you don’t exactly drop the marker accurately.  Now hitting “Update store” will send those updates to the store. 

Pulling updates to the sheet

Finally we can pull in those changes to the sheet  /** * pull and populate active sheet from effex */function pullSheet () {   // pull data for given item using the updater key  var props = PropertiesService.getScriptProperties();  var keys = JSON.parse (props.getProperty(“effexkeys”));    // and write to current sheet  var sheet = SpreadsheetApp.getActiveSheet();    // the alias will be the sheet name  var alias = dataAlias || sheet.getName();    var result = ec.pullFromEffex (updater || keys.updater , alias);  if (!result.ok) {    throw ‘failed to pull data ‘ + JSON.stringify (result);  }    // now turn data into sheet shaped values  var values = ec.unObjectify (result.value);     sheet.clearContents();  if (values.length) {    sheet.getRange (1,1,values.length,values[0].length).setValues (values);  }  }

And here is the result

Housekeeping

You’ll probably want to add those functions to the menu onOpen

// add to menu on open
function onOpen () {
  SpreadsheetApp.getUi()
  .createMenu("effex")
  .addItem("push this sheet to store", "pushSheet")
  .addItem("pull this sheet from store", "pullSheet")
  .addToUi();
}

Which gives this


Notes for Apps Script developers, and comparison with Apps Script CacheService

If you are Apps Script developer, you probably use the CacheService. You can use Effex instead of that if you want, but here are the main differences.

 Apps Script  Effex
 Scope  To a user, document, or script. Cannot be used across multiple scripts  Can be used across any platforms, so has no scope boundaries.
 Performance  Fast – about 30ms, integrated into Apps Script platform Depending on your location, about 200ms. Runs on Google infrastructure, but is not Apps Script.
 Rate limiting  The CacheService seems to be free of rate limits  Since you would use UrlFetchApp to access it from Apps Script, you will be using UrlFetch quota and would hit UrlFetchApp rate limits well before the Effex rate limits kicked in.
 Maximum item size  100k max  The free tier , 512k. Other tiers not yet available
 Lifetime  6 hours max  The free tier, 12 hours max. Other tiers not yet available.
 Analytics  none  Full usage data by operation and size to the individual key level

In summary, for small caching requirements that are limited to within a single Apps Script, the apps script caching is a better choice. If you need to extend across multiple scripts, or share with other platforms you could consider Effex.

Effex-demo-markers code

The app  is available on github and includes the Ephemeral exchange library for JavaScript. You are welcome to fork it and play with it as you wish. If you would like to collaborate on enhancing it, make a PR.  If you do make your own version of this, you’ll notice a file called yourMapsApi.js. Its purpose is to specify the API key for Google Maps. Make a copy of this file to mapsApiKey.js, get your own Google Maps key, and add it there.

function getMapsApiKey () {
  return 'your maps api key goes here';                 /// Put your Google maps api key here
};

Summary

I hope this gives an indication of how a simple exchange mechanism can delegate and compartmentalise tasks between disconnected systems. Of course you could more fully integrate platforms, but then they become single use. I can re-use this exact same App with Excel, Docs, Word, Powerpoint or anything else that needs maps data enriched, and I can use the same sheet mechanism to send this data to other Apps for different kinds of enrichment. I’ll be posting more examples of apps like this, and I’d love to hear what you’ve built – especially if you’d like to post something here about it.

For more like this, see Ephemeral Exchange Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.