Fusion and blister lists

Of course Google Fusion is an ideal source for useful public tables to use for validation purposes. In Flight data from Fusion I used a fusion table to provide airline information for the visualization. Here's one of those same tables showing up here as a validation list for Google Apps Script.

Using the Blister API and the Fusion API

As introduced in Using the API to manage lists we can directly create a  blister list from Google Apps Script without the need to create a spreadsheet to populate it. In this case we'll take the data straight out of fusion. I've created a general purpose fusion data getter that just needs a couple of parameters - we'll look at that later.

Creating the blister list

Just a one liner, needing the key of the fusion table, along with the name to give to the blister.  You don't have to do anything, since it's done once and shared out, but here's the code behind if you are interested.

Displaying the list in a sheet.

As usual, we can dump the whole thing in a sheet, like this.


Which gives us a list that starts like this

Applying as validation

In the example spreadsheet I've added this list as a datavalidation that will be applied when the sheet is opened. Now all the validations being applied in this spreadsheet looks like this. 

And the data entry sheet looks like this

Looking up the name in column B from the entered shortcode using

Fusion API code

Here's the code creating a blister from any public fusion table. The only unusual thing here is that I'm using my private scriptDB, where I keep various keys, from which to retrieve my Google developer key. If you want to use this code, you'd need to replace the section below with however you store your own private keys. For more on using scriptDB as a lockbox, see this post.

  // this gets my developer key from my private scriptdb- replace with your own
  // for how, see http://excelramblings.blogspot.co.uk/2012/09/using-google-apps-scriptdb-as-lockbox.html
  var stuff = mcpher.getMyStuff("fusionDeveloperKey",myStuff.myStuffDb());
  if (stuff.status.code != "good") throw (JSON.stringify(result));
  var developerKey = stuff.result.myStuff.consumerKey;

All comments, suggestions, assistance, good lists are welcome as I develop this capability. You can get me on Google plus, Twitter or this forum.

For help and more information join our forum,follow the blog or follow me on twitter .