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.

Page Content hide
1 And here is the required code.
2 The library
3 Share with your network

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. 

Public Function getDataFromFusion(sheetName As String, _
                        Optional developerKey As String = vbNullString, _
                        Optional tableKey As String = vbNullString, _
                        Optional sql As String = vbNullString)
    Dim where As Range, job As cJobject, jo As cJobject
    
    If developerKey = "" Then developerKey = getFusionKey()
    If tableKey = "" Then tableKey = "1pvt-tlc5z6Lek8K7vAIpXNUsOjX3qTbIsdXx9Fo"
    If sql = "" Then sql = "select * from " & tableKey
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    With restQuery(sheetName, "fusiondata", developerKey, , , , , False, , , , , , , , , sql)
        ' fusion tables carry their row and column names
        ' get rid of any data on sheet
        If Not .dset.headingRow.where Is Nothing Then .dset.headingRow.where.Worksheet.Cells.ClearContents
        ' now column headings
        Set where = Range(sheetName & "!a1")
        For Each job In .jObject.child("columns").children
            where.Offset(, -1 + job.childIndex).value = job.value
        Next job
        ' now row values
        For Each job In .jObject.child("rows").children
            For Each jo In job.children
                where.Offset(job.childIndex, -1 + jo.childIndex).value = jo.value
            Next jo
        Next job
        .teardown
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
                        

End Function

For more on this , see Rest to Excel library and Flight data from Fusion