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, it is using it through OAuth 2.0 Bearer Token to get the actual data. 

I need to connect to this API. I have working code in Python. Maybe someone can help me convert this to use in the Advanced Editor? I have checked multiple posts but I don't know M language so it is hard for me to get this converted exactly.

 

#This is my python code and it works

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 = requests.post(token_url, data=data, verify=False, 
allow_redirects=False, auth=(client_id, client_secret))

tokens = json.loads(access_token_response.text)

api_url = format(api_base_url+qry_str)

api_headers = {'Content-Type': 'application/json', 'Authorization': 'Bearer ' + tokens['access_token'],
               'Keep-Alive': '1'}

response = requests.get(api_url, headers=api_headers, verify=False)



 

 

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.

5 Replies

  • tonmcg's avatar
    tonmcg
    Resolver II
    7 years ago

    Does this API require the client_id and client_secret to be query parameters or placed within the request header?

  • 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.