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.
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.
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.
As usual, we can dump the whole thing in a sheet, like this.
Which gives us a list that starts like this
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
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.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Things that have been deprecated > GAS lists and validation >