Parse JSON Response from Google Analytics API V3

546 views
Skip to first unread message

Oscar Martinez

unread,
Feb 27, 2013, 5:44:06 PM2/27/13
to excel-r...@googlegroups.com
Hello,

I have been working parsing the JSON response below:

{"kind":"analytics#profiles","username":"XX...@gmail.com","totalResults":240,"startIndex":1,"itemsPerPage":1000,"items":[{"id":"XXXX","kind":"analytics#profile","selfLink":"https://www.googleapis.com/analytics/v3/management/accounts/XXXX/webproperties/UA-XXXX-1/profiles/XXX","accountId":"XXXX","webPropertyId":"UA-XXXX-1","internalWebPropertyId":"XXXX","name":"X 2","currency":"USD","timezone":"America/Los_Angeles","websiteUrl":"-","type":"WEB","created":"xxxx","updated":"xxxx","eCommerceTracking":true,"parentLink":{"type":"analytics#webproperty","href":"https://www.googleapis.com/analytics/v3/management/accounts/XXXX/webproperties/UA-XXXX-1"},"childLink":{"type":"analytics#goals","href":"https://www.googleapis.com/analytics/v3/management/accounts/XXXX/webproperties/UA-XXXX-1/profiles/XXXX/goals"}}]}


This is just a sample.  The actual response is larger.

Any help would be greatly appreciated.

Bruce McPherson

unread,
Feb 28, 2013, 3:24:58 AM2/28/13
to excel-r...@googlegroups.com
Sure.. This is easy enough. What do you want to do with it afterwards? Presumably you got from a rest query. Do you want to automate that too?

Sent from my iPad
--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

Oscar Martinez

unread,
Feb 28, 2013, 1:15:29 PM2/28/13
to excel-r...@googlegroups.com
Yes, I got the data from a rest query.  I want to be able to parse the response into an excel sheet.  The sheet would show the account id, webpropertyID, and the name on the profile.

Bruce Mcpherson

unread,
Feb 28, 2013, 2:25:21 PM2/28/13
to excel-r...@googlegroups.com
Normally you would setup the rest query as a rest library entry according to this recipe


That way, the data would be retrieved and the sheet populated all in one go. 

However, if you want you can pass fixed data you already have. In this example I loaded your data into cell A1, and named 3 columns to match the data you want to extract

this all you need to make it work.

Public Sub testOscar()
    Dim jInput As String
    
    jInput = Range("oscar!a1").value
    restQuery("oscar!a2", , , , "foobar", "items", , True, , , True, jInput).tearDown
    
End Sub

executing that generates this result

Inline images 1


You'll find this in the latest version of cdataset.xlsm , downloadable here

in the tab called 'oscar'

the code is in the restlibraryexamples module
bruce


image.png

Oscar Martinez

unread,
Feb 28, 2013, 2:59:41 PM2/28/13
to excel-r...@googlegroups.com
Thanks for your help Bruce.

I think I'll try the rest library.

Oscar Martinez

unread,
Mar 4, 2013, 5:44:05 PM3/4/13
to excel-r...@googlegroups.com
Would the process you described apply to the query below:

{"kind":"analytics#gaData","id":"https://www.googleapis.com/analytics/v3/data/ga?ids=ga:xxxx&dimensions=ga:date&metrics=ga:visits&start-date=2013-02-01&end-date=2013-02-28&max-results=10000","query":{"start-date":"2013-02-01","end-date":"2013-02-28","ids":"ga:xxx","dimensions":"ga:date","metrics":["ga:visits"],"start-index":1,"max-results":10000},"itemsPerPage":10000,"totalResults":28,"selfLink":"https://www.googleapis.com/analytics/v3/data/ga?ids=ga:xxxx&dimensions=ga:date&metrics=ga:visits&start-date=2013-02-01&end-date=2013-02-28&max-results=10000","profileInfo":{"profileId":"xxx","accountId":"xxxx","webPropertyId":"UA-xxx-x","internalWebPropertyId":"xxx","profileName":"xxxx","tableId":"ga:xxxx"},"containsSampledData":false,"columnHeaders":[{"name":"ga:date","columnType":"DIMENSION","dataType":"STRING"},{"name":"ga:visits","columnType":"METRIC","dataType":"INTEGER"}],"totalsForAllResults":{"ga:visits":"313"},"rows":[["20130201","14"],["20130202","9"],["20130203","6"],["20130204","14"],["20130205","16"],["20130206","6"],["20130207","15"],["20130208","10"],["20130209","7"],["20130210","16"],["20130211","13"],["20130212","7"],["20130213","10"],["20130214","8"],["20130215","7"],["20130216","12"],["20130217","10"],["20130218","13"],["20130219","13"],["20130220","12"],["20130221","6"],["20130222","11"],["20130223","12"],["20130224","19"],["20130225","7"],["20130226","10"],["20130227","12"],["20130228","18"]]}

Bruce Mcpherson

unread,
Mar 4, 2013, 6:16:12 PM3/4/13
to excel-r...@googlegroups.com
what would you expect your output table to look like?


--

Oscar Martinez

unread,
Mar 4, 2013, 6:39:40 PM3/4/13
to excel-r...@googlegroups.com
It would be a table similar to the previous example.

In this case I would want the dates on one column, and the total visits in another column.

Bruce Mcpherson

unread,
Mar 5, 2013, 6:25:36 AM3/5/13
to excel-r...@googlegroups.com
This data comes back as table shaped, with self defining columns and rows. Most rest queries have the data names as kay/value pairs.

In the case below, I'm taking your data from a cell. You could also take it from the rest query directly. 

The code is straightforward, and doesn't need any setup of the output columns since they are embedded in the data. 

This will do it
Public Sub testGoogaWire()
    Dim jInput As String, cj As cJobject, r As Range, cd As cJobject
    
    ' for now, value is in a cell rather than from a rest query
    ' how to process google analytics
    jInput = Range("googa!a1").value
    
    With restQuery("googa!a2", , , , "foobar", "kind", , False, , , True, jInput)

        ' self defining columns in this response
        Set r = .dset.headingRow.where.Resize(1, 1)
        For Each cj In .jObject.child("columnheaders").children
            r.Offset(, cj.childIndex - 1).value = cj.child("name").value
        Next cj
        
        ' data rows have no labels
        For Each cj In .jObject.child("rows").children
            For Each cd In cj.children
                r.Offset(cj.childIndex, cd.childIndex - 1).value = cd.value
            Next cd
        Next cj
        .tearDown
    End With
    
End Sub


and produces this

Inline images 1

the code is in the latest version of cdataset.xlsm

image.png
Reply all
Reply to author
Forward
0 new messages