Power Query call to google.webmaster.api , Post, request problem

Question

I call the google.webmasters.api via Power-Query(M) and managed to configure the oath2 and made my first successfull call to get & list. Now i try to call the /searchAnalytics/query? which is working only with Post. This always responds in a 400 error. Formating of the Query or the Url is not working correctly.

Here some additional Infomations:

Power Query - Reference

Google Webmaster Api - Reference

PowerBi Community

format Date different:

body = "{ ""startDate"": ""2019-01-01"", ""endDate"": ""2019-02-02"" }",

to

body = "{ ""startDate"": ""2019/01/01"", ""endDate"": ""2019/02/02"" }",

let
    body = "{ ""startDate"": ""2019-01-01"", ""endDate"": ""2019-02-02"" }",
    AccessTokenList = List.Buffer(api_token),
    access_token = AccessTokenList{0},
    AuthKey = "Bearer " & access_token,
    url = "https://www.googleapis.com/webmasters/v3/sites/https%3A%2F%2Fxxxxxxxxx.xxx/searchAnalytics/query?",
    Response = Web.Contents(url, [Headers=[Authorization=AuthKey, ContentType="application/json", Accept="application/json"], Content=Text.ToBinary(body) ]),
    JsonResponse = Json.Document(Response)
in
    Response

getting a 400 and is shows as 400 call in Gooogle-Api Overview

Any Ideas whats wrong?

Thx

score 0 · Answer 1 · answered Apr 13 '19 at 13:56

Ensure request headers are valid. Server expects Content-Type header, not ContentType.

The documentation (https://developers.google.com/webmaster-tools/search-console-api-original/v3/searchanalytics/query#try-it) suggest requests should be something like:

POST https://www.googleapis.com/webmasters/v3/sites/[SITEURL]/searchAnalytics/query HTTP/1.1

Authorization: Bearer [YOUR_ACCESS_TOKEN]
Accept: application/json
Content-Type: application/json

{}

So seems like main takeaways are:

HTTP POST method must be used
- Web.Contents documentation (https://learn.microsoft.com/en-us/powerquery-m/web-contents) suggests including the Content field in the options record to change request from GET to POST.
URL must be valid
- You haven't provided your actual URL, so you'll have to validate it for yourself. I would get rid of the trailing ? in your url (as you aren't including a query string -- and even if you were, you should pass them to the Query field of the options record instead of building the query string yourself).
Headers (Authorization, Accept, Content-Type) should be valid/present.
- Build your headers in a separation expression. Then pass that expression to the Headers field of the options record. This gives you the chance to review/inspect your headers (to ensure they are as intended).
Body should contain valid JSON to pass to the API method.
- Creating valid JSON via manual string concatenation is liable to error. Using Json.FromValue (https://learn.microsoft.com/en-us/powerquery-m/json-fromvalue) seems a better approach.

All in all, your M code might look something like:

let
    // Some other code is needed here, in which you define the expression api_token
    AccessTokenList = List.Buffer(api_token),
    access_token = AccessTokenList{0},
    AuthKey = "Bearer " & access_token,
    requestHeaders = [Authorization = AuthKey, #"Content-Type" = "application/json", Accept = "application/json"],
    parametersToPost = [startDate = "2019-01-01", endDate = "2019-02-02"], // Can include other parameters here e.g. dimensions, as mentioned in Search Console API documentaton.
    jsonToPost = Json.FromValue(parametersToPost, TextEncoding.Utf8), // Second argument not required (as is default), but just be explicit until you've got everything working.
    url = "https://www.googleapis.com/webmasters/v3/sites/https%3A%2F%2Fxxxxxxxxx.xxx/searchAnalytics/query", // Uri.EscapeDataString function can be use for URL encoding
    response = Web.Contents(url, [Headers=requestHeaders, Content=jsonToPost])
in
    response

Untested (as I don't have an account or API credentials).

Power Query call to google.webmaster.api , Post, request problem

1 Answers1