Forum Discussion

numersoz's avatar
numersoz
Helper III
7 years ago
Solved

Power Query Open ID and OAuth 2.0 Rest API

Hello, I have a RESTful API where it has two-factor authentication. First, it used my username and password to get a Bearer authentication key using OpenID. After this with the authentication key,...
Need Help
Show and Tell
  • numersoz's avatar
    numersoz
    7 years ago
    let
    token_url = "tokenurl.com",    
    api_base_url = "apiurl.com",
    qry_str = "?myparameter",

   body="grant_type=password&client_id=CLIENTID&client_secret=PASSWORD&username=EMAIL&password=PASSWORD",
   Source  = Json.Document(Web.Contents(token_url,
   [ 
     Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
     Content=Text.ToBinary(body)
   ]
   )
   ),
    token = Source[access_token],

    data= Json.Document(Web.Contents(api_base_url&qry_str,
   [ 
     Headers = [
                #"Authorization"="Bearer "&token,#"Content-Type"="application/json"]
     
   ]
   )
   ),  
in
    data

    I finally got it working with this code. Thanks for the support.

tonmcg's avatar
tonmcg
Resolver II
7 years ago

Without knowing the structure the API requires of your authorization request and response, here's something that should get you started:

let 
    token_url = "https://myurl.com/", //This gets the access token
    api_base_url = "https://anotherurl.com", //This uses the token to get the data
    RO_user = "[email protected]",
    RO_password ="mypassword",
    client_id = "xxx-data-api",
    client_secret = "Y",

    qry_str = "pass_this_info_to_get_what_you_want",
    data = [grant_type = "password", username = "RO_user", password = "RO_password"],

    access_token_response = Web.Contents(
        token_url, 
        [
            Headers=
                [
                    Accept="application/json"
                ],
            Query = [client_id = client_id, client_secret = client_secret],
            Content = Json.FromValue(data)
        ]
    ),
    token = Json.Document(access_token_response)[access_token],

    api_url = Text.Combine({api_base_url,"?",qry_str}),
    api_headers = [#"Content-Type" = "application/json", Authorization = "Bearer " & token, #"Keep-Alive" = "1"],
    response = Web.Contents(
        api_url, 
        [
            Headers = api_headers
        ]
    )
in
    response

If the API requires the client_id and client_secret to be passed via the request header, change the access_token_response variable to this:

access_token_response = Web.Contents(
        token_url, 
        [
            Headers=
                [
                    Accept="application/json",
                    client_id = client_id, 
                    client_secret = client_secret
                ],
            Content = Json.FromValue(data)
        ]
    ),

By the way, is the Keep-Alive parameter in your request header required?

Lastly, if this doesn't work off the bat, can you share the structure of the authorization response, especially how the authorization token looks like?

numersoz's avatar
numersoz
Helper III
7 years ago

Thanks for the reply. The client_id and client_secret go to the body, not the header. I was able to get the first part working. I am able to get the access token from the first post request. With the get request, I am now having problems getting authentic. Please see my M code below:

 

let
    token_url = "token_url.com",    
    api_base_url = "api_base_url.com",
    qry_str = "get_this_data",

   body="grant_type=password&client_id=THIS_IS&client_secret=Y&[email protected]&password=PASSWORD",
   Source  = Json.Document(Web.Contents(token_url,
   [ 
     Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
     Content=Text.ToBinary(body)
   ]
   )
   ),
    token = Source[access_token],

    data= Json.Document(Web.Contents(api_base_url&qry_str,
   [ 
     Headers = [
                #"Authorization"="Bearer "&token,#"Content-Type"="application/json",#"Keep-Alive"="1"]
     
   ]
   )
   )
in
    data

In this code, Source is returning the token successfully. But when I pass it on to the next one, I get an authentication error for the result of data. It says access is forbidden. (It works on Python). Maybe I am doing the second part wrong? I don't think keep_alive is necessary.

 

 

  • tonmcg's avatar
    tonmcg
    Resolver II
    7 years ago

    Can you provide a screenshot of the error you're receiving? And can you remove the Keep-Alive parameter?

    Here are a few things to check:

    1. What's the data type of the token variable? Is it a text value?
    2. Is the Url for the endpoint constructed correctly? Generally, REST-ful APIs require query parameters to be appended to the endpoint via a question mark ('?'), like api_base_url.com?get_this_data. I don't see a question mark anywhere in your Url.
    3. What data format does the endpoint expect? I suggest adding Accept = "application/json" to the 'Headers' record.

     

    • numersoz's avatar
      numersoz
      Helper III
      7 years ago
      let
    token_url = "tokenurl.com",    
    api_base_url = "apiurl.com",
    qry_str = "?myparameter",

   body="grant_type=password&client_id=CLIENTID&client_secret=PASSWORD&username=EMAIL&password=PASSWORD",
   Source  = Json.Document(Web.Contents(token_url,
   [ 
     Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
     Content=Text.ToBinary(body)
   ]
   )
   ),
    token = Source[access_token],

    data= Json.Document(Web.Contents(api_base_url&qry_str,
   [ 
     Headers = [
                #"Authorization"="Bearer "&token,#"Content-Type"="application/json"]
     
   ]
   )
   ),  
in
    data

      I finally got it working with this code. Thanks for the support.