Sheets and Maps collaboration with cache - effex demo

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.


Where to get it

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


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


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've created a few container bound scripts to manage the data. You can just copy these as is into your own sheet, but first let's look at managing the cache keys. 

I've decided to store my keys in the Properties service, so the first step is to manually add your boss key to the project's script properties.

Once that's done, you can generate some keys to access the store. You can modify the lifetime of the keys when you create them. I'm just taking the default lifetime. It really depends on whether this is a one off session or one that you'll want to prolong over a period.

The library I'm using knows how to make a bunch of keys for me, so all I have to do is to store them in the properties service for later.
var ec = cAppsMapsEffex.AppsMapsEffex;
var demoUrl = "";
 * making 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.

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.
 * push active sheet to effex
function pushSheet () {

  // we'll need keys
  var props = PropertiesService.getScriptProperties();
  var keys = JSON.parse (props.getProperty("effexkeys"));

  // the values to push
  var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  // objectify data and push it
  var result = ec.pushDataForUpdate (ec.objectify(values) , keys, "effex-demo-markers");
  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 () {

  // we'll need keys
  var props = PropertiesService.getScriptProperties();
  var keys = JSON.parse (props.getProperty("effexkeys"));

  // pull data for given item using the updater key
  var result = ec.pullFromEffex (keys.updater , "effex-demo-markers");
  if (!result.ok) {
    throw 'failed to pull data ' + JSON.stringify (result);
  // now turn data into sheet shaped values
  var values = ec.unObjectify (result.value);
  // and write to current sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  if (values.length) {
    sheet.getRange (1,1,values.length,values[0].length).setValues (values);

and here's the result


You'll probably want to add those functions to the menu onOpen
// add to menu on open
function onOpen () {
  .addItem("push this sheet to store", "pushSheet")
  .addItem("pull this sheet from store", "pullSheet")

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 platformDepending 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


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.