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.

Demo

 

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 the
Property 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 open
function 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. For how to add new places, enter info, move or remove places, or about geocoding – see the previous demo, Sheets and Maps collaboration with cache – effex demo – it works the same way. 
 
You’ll notice that the info window contains a new icon – this takes you in to streetview for the marker, all of which have now been automatically geocoded.
 
 
 
You’ll notice that we’ll initially be looking at the marker in streetview, and it will be somewhere in the image. To learn about the code to do that, see Set initial streetview orientation towards a map marker..
 
 
You can zoom  (+/- on the keyboard) or move around to get exactly the view you want. I’ve chosen this one. The view can be saved using the camera icon.
 
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);
  }
  
}

Notice that we have a link against the address we just snapped.

 

 
This link can be used to ask the streetview image API to recreate the scene you just snapped. It’s also decoded and used when its sent to the store to recreate the default view as the one you snapped when you enter the map app again. For the code for how to do this, see Convert field of view to zoom and back again
 
Getting it into other platforms
 
Now this data is in Ephemeral Exchange, you can pull it or modify it in any app that knows how to speak to the store, as described in Sheets and Maps collaboration with cache – effex demo, but being able to recreate images in this way means we can do some cooler things. 
For more like this, see Ephemeral Exchange.

Docs

Let’s finish off setting up the streetview images I want for the other addresses first of all, so my data looks like this.
 

I want to create a table in Docs with some of this info, including the images – so my initial document looks like this (it’s available here), where the table headings specify which fields I want to pull.

I’ve created some menu items with this

function onOpen(){
  var ui = DocumentApp.getUi();
  
  ui.createMenu("effex")
  .addItem("update in store", "createInStore")
  .addItem("pull from store", "pullFromStore")
  .addItem("generate new keys", "generateKeys")
  .addToUi();
}

In this case I’ll be using the key generated originally by the spreadsheet and being used by the Maps app.

var alias = "macdo";
var updater = "uxk-a10-b2kdijqnlmfc";
 
Now I can pull the data
 
 
And here’s the result
 
 
 
Slides
 
Another nice possibility is to create slides based on this enriched data. For an example of this see Merging data with a slides template. I’m using the app there to create a slide deck of all these places, with the data pulled from the store as usual. 
 
This time, I’ve created a webapp that accepts parameters about the effex keys to use, and where to find a template deck to base the finished deck on.

 

And here’s the  generated result viewed in the Slides editor.

For more like this, see Ephemeral Exchange