Fusion data to Excel

In Flight data from Fusion I showed how to get a large amount of data from various Google Fusion tables into a javascript app. Since the Fusion API is just a REST API, you can use the Rest to Excel library to get Fusion Data into Excel very easily.  As usual, you can find these examples in the downloadable cDataSet.xlsm. To generate an empty workbook with just the modules you need, see How to update modules automatically in VBA and Module reference list. 

Here's an example - I'm taking all carrier codes and airline names from a Google Fusion Table. Below is a small clip from the result.


And here is the required code.

Public Sub testFusion()
    getDataFromFusion "Fusion", getFusionKey(), "1pvt-tlc5z6Lek8K7vAIpXNUsOjX3qTbIsdXx9Fo"
End Sub

"Fusion" is the name of the sheet to write the result to, the second argument is a string with your fusion developer key ( I use a function to retrieve mine from a scriptdb lockbox) - you really need to get your own, and the 3rd argument is the ID for the fusion table I want to retrieve all the data for. There is a 4th optional argument, where you can specify specific Sql if you want to play around with the data before retrieving it.

The library

This mainly uses the Rest to Excel library, and here is the library entry

        With .add("fusiondata")
            .add "restType", erSingleQuery
            .add "url", "https://www.googleapis.com/fusiontables/v1/query?key="
            .add "results", ""
            .add "treeSearch", True
            .add "ignore", vbNullString
            .add "append", "&sql="
        End With

The data that is returned from Fusion includes a description of the columns, so we can use that to write the new Excel table. Here's a snippet of the beginning of the JSON response. 
{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "carrier",
  "name",
  "link"
 ],
 "rows": [
  [
   "JP",
   "Adria Airways",
   ""
  ],
  [
   "RE",
   "Aer Arann Express",
   ""
  ],
That's pretty straightforward to unravel as follows. 
For more on this , see Rest to Excel library and Flight data from FusionFor help and more information join our forum,follow the blog or follow me on twitter .




Comments