jcalamante commented 8 years ago

I can authenticate no issues but I am having issues passing Json to get the response, I have turned customFormatting to False

Hoping someone can assist

Here's my code (note I have removed my Api/client keys

`Option Compare Database
'First sub is for website id and dates
'Second Logs into GA with Jarrods API authentication
'Third sub(function) is the Ga query request, modify the auerystringParams
'Fourth Sub outputs the info where you want it

Public Sub RunRequest()
WebHelpers.EnableLogging = True

OutputAnalytics Client.Execute(AnalyticsRequest(15600344, Date - 1, Date))

'AnalyticsRequest 15600344, Date - 1, Date
End Sub

Property Get Client() As WebClient

Set Client = New WebClient

Client.EnableAutoProxy = True
Client.TimeoutMs = 30000 ' 30 seconds
Client.BaseUrl = "https://www.googleapis.com/auth"

    Dim auth As New GoogleAuthenticator
    auth.Setup ClientId:="", _
        ClientSecret:=""
    auth.AddScope "analytics.readonly" ' -> https://www.googleapis.com/auth/analytics
    auth.ApiKey = ""
    auth.Login

Set Client.Authenticator = auth

End Property

 ' Desired request: GET https://www.googleapis.com/analytics/v3/data/ga
'                        ?ids=ga:12345
'                        &start-date=2008-10-01
'                        &end-date=2008-10-31
'                        &metrics=ga:visits,ga:bounces
' Client.BaseUrl = "https://www.googleapis.com/analytics/v3/"

Public Function AnalyticsRequest(ProfileId As String, StartDate As Date, EndDate As Date) As WebRequest
'Modify this function to pull out different information

    Set AnalyticsRequest = New WebRequest

    With AnalyticsRequest
    .Resource = "data/ga"
    .Method = WebMethod.HttpGet

    .AddQuerystringParam "ids", "ga:" & ProfileId
    .AddQuerystringParam "start-date", Format(StartDate, "yyyy-mm-dd")
    .AddQuerystringParam "end-date", Format(EndDate, "yyyy-mm-dd")
    .AddQuerystringParam "metrics", "ga:visits,ga:bounces"
    End With

End Function

Public Sub OutputAnalytics(Response As WebResponse)
MsgBox Response.StatusCode

    If Response.StatusCode = Ok Then
    MsgBox "hi"
        Debug.Print Response.Data("totalsForAllResults")("ga:visits")
        Debug.Print Response.Data("totalsForAllResults")("ga:bounces")
    Else
        MsgBox "Error: " & Response.Content
    End If

End Sub

And the errors

VBA-Web: Loaded auto-proxy for https://www.googleapis.com:443:
Server = httpgw-tll.kmtltd.net.au:8080
Bypass List = 
WebClient.PrepareHttpRequest: SetProxy: httpgw-tll.kmtltd.net.au:8080
--> Request - 4:49:05 PM
GET https://www.googleapis.com/auth/data/ga?ids=ga%3A******&start-date=2016-03-16&end-date=2016-03-17&metrics=ga%3Avisits,ga%3Abounces&key=********User-Agent: VBA-Web v4.0.22 (https://github.com/VBA-tools/VBA-Web)
Content-Type: application/json
Accept: application/json
Content-Length: 0

ERROR - WebHelpers.ParseByFormat: 11000, An error occurred during parsing
10001: Error parsing JSON:
<HTML><HEAD
^
Expecting '{' or '['
ERROR - WebHelpers.ParseByFormat: 11000, An error occurred during parsing
10001: Error parsing JSON:
<HTML><HEAD
^
Expecting '{' or '['
<-- Response - 4:49:05 PM
404 Not Found
Cache-Control: private, max-age=0
Date: Thu, 17 Mar 2016 05:49:05 GMT
Transfer-Encoding: chunked
Content-Type: text/html; charset=UTF-8
Expires: Thu, 17 Mar 2016 05:49:05 GMT
Accept-Ranges: none
Server: GSE
Vary: Accept-Encoding
X-Content-Type-Options: nosniff
X-Frame-Options: SAMEORIGIN
X-XSS-Protection: 1; mode=block
Alternate-Protocol: 443:quic,p=1
Alt-Svc: quic=":443"; ma=2592000; v="31,30,29,28,27,26,25"

<HTML>
<HEAD>
<TITLE>Not Found</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Not Found</H1>
<H2>Error 404</H2>
</BODY>
</HTML>

(TH: updated formatting)

Sophist-UK commented 8 years ago

Please ignore my previous comment which I have deleted.

According to the Google Analytics API documentation for REST you need to have a Authorization: Bearer {oauth2-token} header and I don't see that in the request debug. Reading further down, the key query parameter is Oauth1.0a, and reading the GoogleAuthenticator code it appears that if you specify the key it uses that, otherwise if you do an OAuth2 login then it adds the header. The code at the beginning of the login method checks to see if you have specified an ApiKey and if so it doesn't do a login. Also, according to the example at the beginning of GoogleAuthenticator.cls, you should either set the auth.ApiKey or call auth.Login but not both.

So my guess is that you need to remove the line which sets the ApiKey and see what happens.

jcalamante commented 8 years ago

Thanks for your comments, I had tried removing the apiKey and also removing the login - but going through the login code it only checks if APIkey exisit's the function.

Although you deleted your last comment I had already tried adding in .RequestFormat = WebFormat.FormUrlEncoded which was also required on the response format.

adding that in seems to have got me 1 step closer where the Json error's no longer come but, but I'm still getting 404 not found issue.

I think it is something to do with the base Url or scopes, any other thought?

Thanks heaps for your help

See below: ` --> Request - 8:53:06 PM GET https://www.googleapis.com/auth/analytics/v3/data/ga?ids=ga%3A15600344&start-date=2016-03-19&end-date=2016-03-20&metrics=ga%3Avisits,ga%3Abounces Authorization: Bearer ya29.qwLb0RaU73PX6Zi2OXxHkfsnsvVZz9zaS5MYro1uJs2BXOtRFb6cU-WanAWK8KxMNQ User-Agent: VBA-Web v4.0.22 (https://github.com/VBA-tools/VBA-Web) Content-Type: application/x-www-form-urlencoded;charset=UTF-8 Accept: application/x-www-form-urlencoded;charset=UTF-8 Content-Length: 0

<-- Response - 8:53:07 PM 404 Not Found Cache-Control: private, max-age=0 Date: Sun, 20 Mar 2016 09:53:07 GMT Transfer-Encoding: chunked Content-Type: text/html; charset=UTF-8 Expires: Sun, 20 Mar 2016 09:53:07 GMT Accept-Ranges: none Server: GSE Vary: Accept-Encoding X-Content-Type-Options: nosniff X-Frame-Options: SAMEORIGIN X-XSS-Protection: 1; mode=block Alternate-Protocol: 443:quic,p=1 Alt-Svc: quic=":443"; ma=2592000; v="31,30,29,28,27,26,25"

Not Found

Error 404

`

This is compared to when I use the same get query input into the OAuth 2.0 playgroup - only change I had to make is the ":" vs "%3A"

GET /analytics/v3/data/ga?ids=ga:15600344&start-date=2016-03-19&end-date=2016-03-20&metrics=ga:visits,ga:bounces HTTP/1.1 Host: www.googleapis.com Content-length: 0 Authorization: Bearer ya29.qwJWyYDYaS9MN03MzoRxIwsl_ci4rtmlCeVwR-wPegkKKH2isw6lkU6O_8Mb-EzY2A HTTP/1.1 200 OK Content-length: 959 X-xss-protection: 1; mode=block Content-location: https://www.googleapis.com/analytics/v3/data/ga?ids=ga:15600344&start-date=2016-03-19&end-date=2016-03-20&metrics=ga:visits,ga:bounces X-content-type-options: nosniff Expires: Sun, 20 Mar 2016 10:06:33 GMT Vary: Origin,X-Origin Server: GSE Etag: "RTuqIyS1Tt5XEFy6I77L5pEAbZQ/v-LxawSnoXTGeP2bBIZg3sJq3HA" Cache-control: private, max-age=0, must-revalidate, no-transform Date: Sun, 20 Mar 2016 10:06:33 GMT X-frame-options: SAMEORIGIN Content-type: application/json; charset=UTF-8 { "kind": "analytics#gaData", "rows": [ [ "269076", "77401" ] ], "containsSampledData": false, "profileInfo": { "webPropertyId": "UA-7745282-1", "internalWebPropertyId": "14784257", "tableId": "ga:15600344", "profileId": "15600344", "profileName": "www.kmart.com.au", "accountId": "7745282" }, "itemsPerPage": 1000, "totalsForAllResults": { "ga:visits": "269076", "ga:bounces": "77401" }, "columnHeaders": [ { "dataType": "INTEGER", "columnType": "METRIC", "name": "ga:visits" }, { "dataType": "INTEGER", "columnType": "METRIC", "name": "ga:bounces" } ], "query": { "max-results": 1000, "start-date": "2016-03-19", "start-index": 1, "ids": "ga:15600344", "metrics": [ "ga:visits", "ga:bounces" ], "end-date": "2016-03-20" }, "totalResults": 1, "id": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:15600344&metrics=ga:visits,ga:bounces&start-date=2016-03-19&end-date=2016-03-20", "selfLink": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:15600344&metrics=ga:visits,ga:bounces&start-date=2016-03-19&end-date=2016-03-20" }

Sophist-UK commented 8 years ago

I have not used GoogleAuthenticator, but looking at the Login code it does not appear to match exactly the Google OAuth2 instructions.

For example, Google says use https://accounts.google.com/o/oauth2/v2/auth but VBA-Web uses https://accounts.google.com/o/oauth2/auth. Similarly the URLs for getting the token are wrong too.

So, it may be that the GoogleAuthenticator code needs some updating.

Finally for performance reasons I would suggest that you disaggregate your code and keep the client, request and authorisation objects in module-level variables - as this will avoid the need to get authorisation codes and tokens if you re-run the code.

P.S. It would help a lot if you could preview your posts and ensure that code blocks are nicely formatted.

Sophist-UK commented 8 years ago

GET /analytics/v3/data/ga?ids=ga:15600344&start-date=2016-03-19&end-date=2016-03-20&metrics=ga:visits,ga:bounces
HTTP/1.1 
Host: www.googleapis.com 
Content-length: 0 
Authorization: Bearer ya29.qwJWyYDYaS9MN03MzoRxIwsl_ci4rtmlCeVwR-wPegkKKH2isw6lkU6O_8Mb-EzY2A 

HTTP/1.1 200 OK 
Content-length: 959 
X-xss-protection: 1; mode=block 
Content-location: https://www.googleapis.com/analytics/v3/data/ga?ids=ga:15600344&start-date=2016-03-19&end-date=2016-03-20&metrics=ga:visits,ga:bounces X-content-type-options: nosniff 
Expires: Sun, 20 Mar 2016 10:06:33 GMT 
Vary: Origin,X-Origin 
Server: GSE 
Etag: "RTuqIyS1Tt5XEFy6I77L5pEAbZQ/v-LxawSnoXTGeP2bBIZg3sJq3HA" 
Cache-control: private, max-age=0, must-revalidate, no-transform 
Date: Sun, 20 Mar 2016 10:06:33 GMT 
X-frame-options: SAMEORIGIN 
Content-type: application/json; charset=UTF-8

{
    "kind": "analytics#gaData", 
    "rows": [ [ "269076", "77401" ] ], 
    "containsSampledData": false, 
    "profileInfo": {
        "webPropertyId": "UA-7745282-1", 
        "internalWebPropertyId": "14784257", 
        "tableId": "ga:15600344", 
        "profileId": "15600344", 
        "profileName": "www.kmart.com.au", 
        "accountId": "7745282" 
        }, 
    "itemsPerPage": 1000, 
    "totalsForAllResults": { 
        "ga:visits": "269076", 
        "ga:bounces": "77401" 
        }, 
    "columnHeaders": [
         { 
            "dataType": "INTEGER", 
            "columnType": "METRIC", 
            "name": "ga:visits" 
            }, 
        { 
            "dataType": "INTEGER", 
            "columnType": "METRIC", 
            "name": "ga:bounces" 
            } 
        ], 
    "query": { 
        "max-results": 1000, 
        "start-date": "2016-03-19", 
        "start-index": 1, 
        "ids": "ga:15600344", 
        "metrics": [ "ga:visits", "ga:bounces" ], 
        "end-date": "2016-03-20" 
        }, 
    "totalResults": 1, 
    "id": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:15600344&metrics=ga:visits,ga:bounces&start-date=2016-03-19&end-date=2016-03-20", 
    "selfLink": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:15600344&metrics=ga:visits,ga:bounces&start-date=2016-03-19&end-date=2016-03-20" 
    }

jcalamante commented 8 years ago

Apologies will make sure I structure in a more formatted way. Thanks for your suggestion I'll have a search through the GoogleAuthenticator code and see if I can resolve, will post up my resolution if I work it out.

Sophist-UK commented 8 years ago

P.S. In your latest debug output you have set the response format to FormUrlEncoded but you said you had also set the response format - but clearly the response you are expecting is JSON (see above) so this is incorrect.

However I think it is still the OAuth2 which is the problem.

Sophist-UK commented 8 years ago

Sorry - upon reflection there is a Token in the VBA-Web request, so it seems likely that the OAuth2 did work.

Sophist-UK commented 8 years ago

Ok - I think it is as you have indicated because VBA-Web is UrlEncoding the query parameters, and in particular converting the ':' to '%3A'.

Compare the URL in your request with the example on the Google Analytics API Reference page.

Have you tried .RequestFormat = WebFormat.PlainText?

I have a PR #213 submitted but not yet merged which does some stuff in this area, however this only addresses spaces and not colons.

jcalamante commented 8 years ago

Using .RequestFormat = WebFormat.PlainText cleared the Json errors the same way changing to FormUrlEncoded did. I also tried temporarily modifying webhelper.UrlEncode to include char 58 in the unencoded characters, without any additional success. I'm pretty confident the Auth is ok as it get a response from the api but there is something wrong in the Get statement which is getting the path lost.

In the google playgroud the url I use is: https://www.googleapis.com/analytics/v3/data/ga?ids=ga:15600344&start-date=2016-03-19&end-date=2016-03-20&metrics=ga:visits,ga:bounces

It then sends the below GET /analytics/v3/data/ga?ids=ga:15600344&start-date=2016-03-19&end-date=2016-03- 20&metrics=ga:visits,ga:bounces HTTP/1.1 Host: www.googleapis.com Content-length: 0 Authorization: Bearer ya29.qwJWyYDYaS9MN03MzoRxIwsl_ci4rtmlCeVwR-wPegkKKH2isw6lkU6O_8Mb-EzY2A This is different to the structure which is sent from VBA-Web which sends

GET https://www.googleapis.com/auth/analytics/v3/data/ga?ids=ga:15600344&start-date=2016-03-19&end-date=2016-03-`20&metrics=ga:visits,ga:bounces&key=AIzaSyD1_2JhfBM2XfHdJhbLsh9ZAteMMr4TszU User-Agent: VBA-Web v4.0.22 (https://github.com/VBA-tools/VBA-Web) Content-Type: text/plain Accept: text/plain Content-Length: 0

As google seems to only send from '\' on ward and includes a 'Host: www.googleapis.com' there is also no reference to auth or user-Agent but i'm unsure if that is creating the issue

Sophist-UK commented 8 years ago

I doubt it is User-Agent. But the VBA-Web still uses key which is wrong, though at least ':' is not encoded this time.

VBA-tools / VBA-Web

Google Analytics - trouble parsing json -- ms access #214

Not Found

Error 404