Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Connecting to an API that uses OAuth with Power Query

Hi All,

 

I'm trying to ingest data from this API: https://api.nsw.gov.au/Product/Index/22

 

The site lets you test the endpoints in the browser and I get a valid response using the test credentials.

 

I'm able to get the access token in Power Query.  But the next request which uses the access token doesn't seem to work.  Power BI Desktop tells me the credentials used are not valid... 

 

M script below... any help would be much appreciated.

 

 

 

 

//https://api.nsw.gov.au/Product/Index/22
//Visit the site above and click on 'Sandbox and doc'
//This will present test credentials to try the endpoint

let
    testAPIKey = "1MYSRAx5yvqHUZc6VGtxix6oMA2qgfRT",
    testAPISecret = "BMvWacw15Et8uFGF",

    // Concatenates the test Key & Secret and converts to base64
    authKey = "Basic " & Binary.ToText(Text.ToBinary(testAPIKey & ":" & testAPISecret),0),

    url = "https://api.onegov.nsw.gov.au/oauth/client_credential/accesstoken",
    // Make a POST request to obtain a bearer token
    GetJson = Web.Contents(url,
        [
            Headers = 
                [
                #"Authorization"=authKey,
                #"Content-Type"="application/json"
                ],
            Content = Text.ToBinary("grant_type=client_credentials") 
        ]
    ),

    // Get the bearer token from the response
    AccessToken = Text.From(GetJson),

    GetJsonQuery = 
    Web.Contents("https://api.onegov.nsw.gov.au/FuelPriceCheck/v2/fuel/prices",
        [
        Headers=
            [
            #"Authorization"="Bearer " & AccessToken, 
            #"Content-Type"="application/json; charset=utf-8",
            #"apikey"=testAPIKey,
            #"Accept"="application/json",
            #"transactionid"="1",
            #"requesttimestamp"= DateTime.ToText(DateTime.LocalNow(), "dd/MM/yyyy hh:mm:ss tt")
            ]
        ]
    ),
    FormatAsJsonQuery = Json.Document(GetJsonQuery)
in
    FormatAsJsonQuery

 

 

 

  • Hi Anonymous 

    That NSW Govt website is way too hard to use.  It keeps giving me errors just trying to access it, like this

    When I generate an access token on the website and then use it from the same website it tells me the access token is invalid.

     

    Generate an Access Token

     

    Make an API Call

     

    Check the Response

     

    If the Access Token the site itself is generating is being rejected then there's not much hope.  I'm getting the same errors in Postman.

    If I was you I'd be asking the website support what the issue is at their end.

    Regards

    Phil

5 Replies

  • Hi Anonymous 

    That NSW Govt website is way too hard to use.  It keeps giving me errors just trying to access it, like this

    When I generate an access token on the website and then use it from the same website it tells me the access token is invalid.

     

    Generate an Access Token

     

    Make an API Call

     

    Check the Response

     

    If the Access Token the site itself is generating is being rejected then there's not much hope.  I'm getting the same errors in Postman.

    If I was you I'd be asking the website support what the issue is at their end.

    Regards

    Phil

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you very much for your help Philip.  I'll reach out to their support.

  • Hi Anonymous 

    I'm getting the same error but I can't see anything wrong with your code.

    I even signed up for an acount with the website and created myown key/secret and can get an Access Token OK, but auth to the actual API is failing.

    I'll check it in Postman and see what is happening.

    regards

    Phil

  • UFP's avatar
    UFP
    New Member

    Hi,

     

    Allthough this thread is old. Thanks for the almost working example in this post.

    I have replaced

    AccessToken = Text.From(GetJson)

    with

    AccessToken = Json.Document(GetJson)[access_token]
     
    Passing this AccessToken as the Bearer works for me.
     
    Hope this helps others in the same situation,
    Udo