cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
numersoz
Helper III
Helper III

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 = "johndoe@domain.com" 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)



 

 

1 ACCEPTED SOLUTION

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.

View solution in original post

5 REPLIES 5
tonmcg
Resolver II
Resolver II

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 = "johndoe@domain.com",
    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?

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&username=myemail@email.com&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.

 

 

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.

 

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.

View solution in original post

tonmcg
Resolver II
Resolver II

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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors