Forum Discussion

saikishore's avatar
saikishore
New Member
8 years ago
Solved

Passing Variables in API Post Request JSON Body

I'm using the google business API to get the driving directions insights

(auth as text, locid as text) =>
let
    AuthKey = "Bearer "& auth,
    url = "https://mybusiness.googleapis.com/v3/accounts/123456/locations:reportInsights",
    body = "{""locationNames"":[locid,],
""drivingDirectionsRequest"": {""numDays"":""NINETY"",},}",
    Source = Json.Document(Web.Contents(url,[
             
         Headers = [#"Authorization"=AuthKey ,
                    #"Content-Type"="application/json"],
         Content = Text.ToBinary(body) 
             ]   
        ))
in
    Source

This code throws up an bad request error.

This method works well if I hardcode the locid data as text into the query but fails when i use the locid variable to run the query for multiple entries.

Is there any way to declare a global variable in JSON body text??

 

  • ImkeF's avatar
    ImkeF
    8 years ago

    Hi saikishore,

    you don't use the parameter "locid" as text in your function at all. If your expression in step "body" is supposed to do this: It will not work. If locid is a single text string, you have to concatenate it like so:

     

    "{""locationNames"":"& locid &",#(lf)""drivingDirectionsRequest"": {""numDays"":""NINETY"",},}"

15 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    8 years ago

    Hi saikishore,

    you don't use the parameter "locid" as text in your function at all. If your expression in step "body" is supposed to do this: It will not work. If locid is a single text string, you have to concatenate it like so:

     

    "{""locationNames"":"& locid &",#(lf)""drivingDirectionsRequest"": {""numDays"":""NINETY"",},}"
    • ericOnline's avatar
      ericOnline
      Post Patron
      7 years ago

      Hello ImkeF, v-yuta-msft, 

       

      The title of this post extactly matches my question. 

       

      How can I pass key : value pairs from a table rather than 1 by 1?

       

      Here is my example API call (works great):

       

      let
    url = "https://company.com/api/path",
    headers = [
        "apikey" = thisisnotreallymyapikey
        "accept" = application/json,
        "Content-Type" = application/json
    ],
    content = "{
        ""key"": {
            ""nestedKey"": value1,
            ""nestedKey"": value2
        },
        ""key"": """",
        ""key"": ""value3"",
        ""key"": ""value4"",
        ""key"": value5,
        ""key"": [
                  {""key1"": ""value6"", ""key2"": ""value7""}
        ]
    }",
    webdata = Web.Contents(url, [Headers=headers,Content = Text.ToBinary(content)]),
    response = Json.Document(webdata)
in
    response 

       

      I need to have a table of key:value pairs that my query can hit iteratively until complete.

      Example: 

      • Query refers to a table
      • Pulls all key:value pairs accordingly
      • Sends POST call to API
      • Saves data to a table
      • Repeats
      • Appends data to a table until all calls have been made.

        I don't know how to go about this. Any advice you can give is much appreciated.


      Thank you

      • ImkeF's avatar
        ImkeF
        Community Champion
        7 years ago

        Just to make sure to understand your request:

         

        1) For me a recursive action means that it picks up something from a previous action in order to continue.

        I cannot spot that in your example. So could it be that you mean "iteratively" instead?: Go through a table with parameters and pass one row of it into each API-call?

         

        2) With some additional escape signs and indizes, your "content" can be parsed out as a nested Record like so: 

         

         

        Will this structure always be the same for every key-value-pair combination that has to be passed trough (there are 9 different "Key"-fields in there currently)?

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi saikishore,

     

    There may be something wrong the types of parameter you have predefined. Please check if type in power query matches type in JSON. The types of variables in M are like below:

    { any; none; null; logical; number; time; date; datetime; datetimezone; duration; text; binary; type; list; record; table; function; anynonnull }

     

    For example, you can change type of locid with number and try again.

     

    Best Regards,

    Jimmy Tao

  • akashgera's avatar
    akashgera
    Helper I
    5 years ago

    Hi @ImkeF, @v-yuta-msft


    I am using REST API to fetch the data from 3rd party using POST method, I have used the same parameters in Header, Body in postman, and it is giving me the data in JSON format, but when I am using the same thing in power query, it is giving me an error: Response Msg: Invalid APi credentials

    Response code:11

    response : NULL
    Please have a look in my power query below and help me what is wrong in this :-

    let
    ApiOwner= "XXXXXXXX",
    ApiKey="XXXXXXXXXXXXXXXXXXXXXXXXX",
    url = "https://dentalkart.vineretail.com/RestWS/api/eretail/v3/sku/inventoryStatus",
    header= [#"Authorization" = ApiKey & ApiOwner,
    #"Content-Type" = "application/x-www-form-urlencoded"],

    RequestBody=
    "{
    ""skuCodes"":""[]"",
    ""fromDate"":""18/06/2021 15:40:30"",
    ""toDate"":""23/06/2021 15:40:30"",
    ""pageNumber"":""1"",
    ""dropShipFlag"":""no"",
    ""locCode"":""
    }",

    webdata = Web.Contents(url, [Content = Text.ToBinary(RequestBody), Headers=header]),
    response = Json.Document(webdata)

    in
    response

     

     

     


    Any kind of help would really be appreciated !