Sheets, Maps, Slides, Docs , Maps and Streetview, effex collaboration demo

In the demo Sheets and Maps collaboration with cache - effex demo I showed a maps app that was being used across multiple platforms with the Ephemeral Exchange store as the data exchange mechanism. This demo builds on that and brings in a few extra pieces


Where to get it

The app = effex-demo-markers -  is available  on github and includes the Ephemeral exchange library for JavaScript. You can also get the code for the slides app at Merging data with a slides template


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. It does all the things you can do as described in Sheets and Maps collaboration with cache - effex demo, but also
  • Allows you to use streeview to get images associated with markers ( see Playing around with maps and streetview) for some of the technical details.
  • Builds a link that can be used to recreate the view with the Street maps image API, so the view can be recreated in other apps.
  • Can read a street maps image API and recreate the view in Maps.
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 a streetview open. 

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

For brevity, I use these shortcuts throughout the code
var ec = cAppsMapsEffex.AppsMapsEffex;
var demoUrl = "";
var efx = cEffexApiClient.EffexApiClient;

The data sheet

The source data I used can be found here, and starts like this.

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();
  .addItem("create in store", "createFromSheet")
  .addItem("update store", "updateFromSheet")
  .addItem("pull from store", "pullSheet")

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 ("macdo"). 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);

  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. 


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();
  .addItem("update in store", "createInStore")
  .addItem("pull from store", "pullFromStore")
  .addItem("generate new keys", "generateKeys")

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


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 Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.