It is all about Google Wire
- Connect to Google Docs
- Get live data into Excel
- Use data from Gadgets

NOTE: with the new Google Sheets, this method has been largely deprecated. There is a complete revamp of this here Get Data From Google Docs. You can also check out the Sheets apps from Apps built with data abstraction, which can both read and write from Excel to Sheets.
With the growing use of Google Docs and other cloud based systems, we face the problem of how to easily get data from, for example, a Google Docs spreadsheet into Excel. PC based spreadsheets and document processing is not going away time soon.
Google Wire Protocol
The data source URL
google.visualization.Query.setResponse ({version:'0.6',status:'ok',sig:'810325856', table:{cols:[{id:'A',label:'Activate',type:'date',pattern:'M/d/yyyy'} ,{id:'B',label:'Deactivate',type:'date',pattern:'M/d/yyyy'} ,{id:'C',label:'Description',type:'string',pattern:''} ,{id:'D',label:'ID',type:'string',pattern:''} ,{id:'E',label:'Target',type:'string',pattern:''} ,{id:'F',label:'Custom',type:'string',pattern:''} ,{id:'G',label:'Cost',type:'number',pattern:'#0.###############'},{id:'H',label:'Callout' ,type:'string',pattern:''} ,{id:'I',label:'sequence',type:'string',pattern:''}] ,rows:[{c:[,{v:new Date(2007,5,20),f:'6/20/2007'},{v:'Cellphone'},{v:'cell'},{v:'smart'},{v:''},{v:200.0,f:'200'},{v:''},{v:''}]}, {c:[{v:new Date(2006,0,6),f:'1/6/2006'},{v:new Date(2011,2,21), f:'3/21/2011'},{v:'Mac'},{v:'mac'},{v:'iPad'},{v:''},{v:300.0,f:'300'},{v:''},{v:''}]}, {c:[,{v:new Date(2012,2,20),f:'3/20/2012'} ,{v:'PC'},{v:'pc'},{v:'android'},{v:''},{v:250.0,f:'250'},{v:''}, {v:''}]},{c:[{v:new Date(2009,0,1),f:'1/1/2009'},{v:new Date(2011,10,6),f:'11/6/2011'},{v:'Netbook'},{v:'net'},{v:'pc'},{v:'dislike'},{v:150.0,f:'150'},{v:'this was a waste of money'},{v:''}]}, {c:[{v:new Date(2006,0,1),f:'1/1/2006'},{v:new Date(2010,1,1),f:'2/1/2010'},{v:'Windows Phone'},{v:'smart'},{v:'iphone'},{v:''},{v:240.0,f:'240'},{v:'Windows Mobile was so bad'},{v:''}]} ,{c:[{v:new Date(2010,8,2),f:'9/2/2010'}, ,{v:'Tablet'},{v:'tablet'},{v:''},{v:'family'},,{v:''},{v:''}]},{c:[{v:new Date(2003,9,28),f:'10/28/2003'},{v:new Date(2008,5,12),f:'6/12/2008'},{v:'MP3 player'},{v:'mp3'},{v:'ipod'},{v:''}, {v:20.0,f:'20'},{v:''},{v:''}]}, {c:[{v:new Date(2004,9,29),f:'10/29/2004'},{v:new Date(2009,0,1),f:'1/1/2009'},{v:'Personal Video'},{v:'pv'},{v:'ipod'},{v:''},{v:25.0,f:'25'},{v:''},{v:''}]}, {c:[{v:new Date(2008,3,11),f:'4/11/2008'},{v:new Date(2011,5,1), f:'6/1/2011'},{v:'iPod'},{v:'ipod'},{v:'ipad'},{v:'like'},{v:25.0,f:'25'},{v:''},{v:''}]},{c:[{v:new Date(2009,0,4),f:'1/4/2009'},, {v:'iPhone'},{v:'iphone'},{v:'android'},{v:'like'},{v:360.0,f:'360'}, {v:''},{v:''}]},{c:[{v:new Date(2010,10,1),f:'11/1/2010'}, ,{v:'iPad'},{v:'ipad'},{v:'tablet'},{v:'like'},{v:480.0,f:'480'},{v:''},{v:''}]},{c:[{v:new Date(2009,11,12),f:'12/12/2009'}, ,{v:'Android/Chrome'},{v:'android'}, {v:''},{v:''},{v:400.0,f:'400'}, {v:''},{v:''}]} ,{c:[{v:new Date(2005,11,2),f:'12/2/2005'}, {v:new Date(2007,11,11),f:'12/11/2007'},{v:'windows tablet'},{v:'wtab'},{v:'pc'},{v:'dislike'},,{v:'this really sucked'},{v:''}]}]}});
Our task then, is to decode this, and populate an excel sheet on demand with this kind of data.
Data Conversion and Spreadsheet Population
- Leverage the cBrowser object to deal with the http request/response.
- Extract out the {table:[]} part of the google wire response
- Convert it to a more jSon friendly ( change the new Date() javascript construct to a proper date, ‘quote’ the key names etc). We’ll use Regular Expressions for that, then deserialize it using Excel JSON conversion
- Crush it down into a 2 dimensional table. We’ll need to write new something for that.
- Use the Data Abstraction Example to populate our Excel sheet.
Implementation example
Option Explicit Const Url = _ "https://spreadsheets.google.com/a/mcpher.com/spreadsheet/tq? range= A1:H14&key=0At2ExLh4POiZdHBJYnlwaERpYTRZSWl1VEw1TEpQVkE& gid=0" Public Sub googleWireExample() Dim dSet As cDataSet, dsClone As cDataSet, jo As cJobject, cb As cBrowser Dim sWire As String ' get the google wire string Set cb = New cBrowser sWire = cb.httpGET(Url) ' load to a dataset Set dSet = New cDataSet With dSet .populateGoogleWire sWire, Range("Clone!$a$1") If .Where Is Nothing Then MsgBox ("No data to process") Else ' it worked End If End With Set dSet = Nothing Set cb = Nothing End Sub
The http request/response
' get the google wire string Set cb = New cBrowser sWire = cb.httpGET(Url)
httpGet Method
Public Function httpGET(fn As String) As String pHtml = fn Dim oHttp As Object Set oHttp = CreateObject("Microsoft.XMLHTTP") Call oHttp.Open("GET", pHtml, False) Call oHttp.Send("") httpGET = oHttp.ResponseText Set oHttp = Nothing End Function
The populateGoogleWire module
Extracting the table[]
Cleaning up the jSon
' google protocol doesnt have quotes round the key of key value pairs,
' and i also need to convert date from javascript syntax new Date()
s = rxReplace("(new\sDate)(\()(\d+)(,)(\d+)(,)(\d+)(\))", s, "'$3/$5/$7'")
s = rxReplace("(\w+)(:)", s, "'$1':")
Deserializing the cleaned up jSon
' this should return an object as follow ' {table:[ cols:[c:[{id:x,label:x,pattern:x,type:x}] , rows:[ c:[(v:x,f:x}] ]} Set jo = New cJobject Set jo = jo.deSerialize(s, eDeserializeGoogleWire)
Crush down to 2 dimensional table
'need to convert that to cdataset:[{label:"x",,,},{},,,]
'column labels can be extracted then from jo.child("1.cols.n.label") .. where 'n'= column number
Set joc = New cJobject
Set cr = joc.init(Nothing, cJobName).AddArray
For Each jr In jo.Child("1.rows").Children
With cr.add
For Each jc In jo.Child("1.cols").Children
Set jt = jr.Child("c").Children(jc.ChildIndex)
' sometimes there is no "v" if a null value
If Not jt.ChildExists("v") Is Nothing Then
Set jt = jt.Child("v")
End If
If jc.Child("type").toString = "date" Then
' month starts at zero in javascript
astring = Split(jt.toString, "/")
If LBound(astring) <= UBound(astring) Then
v = DateSerial(CInt(astring(0)), CInt(astring(1)) + 1, CInt(astring(2)) + 1)
Else
v = Empty
End If
Else
v = jt.Value
End If
.add jc.Child("label").toString, v
Next jc
End With
Next jr
Push abstracted data to the target worksheet
Set populateGoogleWire = populateJSON(joc, rstart, wClearContents)
When serialized this simplified jSon structure now would be
{"cDataSet":[{"Activate":"","Deactivate":"6/21/2007", "Description":"Cellphone","ID":"cell","Target":"smart", "Custom":"","Cost":"200","Callout":""},{"Activate":"1/7/2006","Deactivate":"3/22/2011", "Description":"Mac","ID":"mac","Target":"iPad","Custom":"", "Cost":"300","Callout":""},{"Activate":"", "Deactivate":"3/21/2012", "Description":"PC","ID":"pc","Target":"android","Custom":"", "Cost":"250","Callout":""},{"Activate":"1/2/2009","Deactivate":"11/7/2011", "Description":"Netbook","ID":"net","Target":"pc", "Custom":"dislike","Cost":"150","Callout":"this was a waste of money"}, {"Activate":"1/2/2006","Deactivate":"2/2/2010", "Description":"Windows Phone","ID":"smart","Target":"iphone", "Custom":"","Cost":"240","Callout":"Windows Mobile was so bad"},{"Activate":"9/3/2010","Deactivate":"", "Description":"Tablet","ID":"tablet","Target":"", "Custom":"family","Cost":"","Callout":""},{"Activate":"10/29/2003","Deactivate":"6/13/2008", "Description":"MP3 player","ID":"mp3", "Target":"ipod", "Custom":"","Cost":"20","Callout":""},{"A
ctivate":"10/30/2004", "Deactivate":"1/2/2009", "Description":"Personal Video","ID":"pv","Target":"ipod", "Custom":"","Cost":"25","Callout":""},{"Activate":"4/12/2008","Deactivate":"6/2/2011", "Description":"iPod","ID":"ipod", "Target":"ipad","Custom":"like","Cost":"25","Callout":""},{"Activate":"1/5/2009","Deactivate":"", "Description":"iPhone","ID":"iphone", "Target":"android","Custom":"like","Cost":"360", "Callout":""},{"Activate":"11/2/2010","Deactivate":"", "Description":"iPad","ID":"ipad", "Target":"tablet","Custom":"like","Cost":"480","Callout":""},{"Activate":"12/13/2009","Deactivate":"", "Description":"Android/Chrome","ID":"android", "Target":"","Custom":"","Cost":"400","Callout":""},{"Activate":"12/3/2005", "Deactivate":"12/12/2007", "Description":"windows tablet","ID":"wtab", "Target":"pc","Custom":"dislike", "Cost":"","Callout":"this really sucked"} ]
}
Summary
These integrated modules are not fully baked, but will continue to be developed. Various of the parameters in the google wire protocol (for example pattern/format) need to be implemented and of course we need more robust data error handling. Please feel free to make any code contributions via our forum if you have used and improved any thing, or indeed to discuss your experiences, make requests or otherwise provide feedback.