Parse JSON Response from Google Analytics API V3

[Oscar Martinez]

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]

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]

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]

Normally you would setup the rest query as a rest library entry according to this recipe

http://ramblings.mcpher.com/Home/excelquirks/json/rest

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

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

http://ramblings.mcpher.com/Home/excelquirks/downlable-items

in the tab called 'oscar'

the code is in the restlibraryexamples module

bruce

[Oscar Martinez]

Thanks for your help Bruce.

I think I'll try the rest library.

[Oscar Martinez]

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]

what would you expect your output table to look like?

--

[Oscar Martinez]

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]

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

the code is in the latest version of cdataset.xlsm