Forum Discussion

pbiftw92's avatar
pbiftw92
Frequent Visitor
3 years ago

Get OAuth Custom Connector to check for valid token before initiating login flow again

I posted separately about getting my Netsuite custom connector to work in Power Query/Power BI and FINALLY got it working, but now I'm running into an issue where every single time I edit a query or refresh, it prompts me to log in again (go to the Netsuite log in form, etc.)

 

And due to how the API pages, I have almost 30 different queries with unions and joins to get the complete datasets. So basically, every time I "Close & Apply" or "Refresh", I have to login 30+ times for each query.

 

Below is the code in my .pq file:

 

 

[Version = "1.0.0"]
section NetsuiteConnector;

client_id = Text.FromBinary(Extension.Contents("client_id.txt"));
client_secret = Text.FromBinary(Extension.Contents("client_secret.txt"));
redirect_uri = "https://localhost";
token_uri = {my_token_uri};
authorize_uri = {my_authorize_uri};
logout_uri = "https://login.microsoftonline.com/logout.srf";
state1="ykv2XLx1BpT5Q0F3MRPHb94j";

// Login modal window dimensions
windowWidth = 720;
windowHeight = 1024;


[DataSource.Kind="NetsuiteConnector", Publish= "NetsuiteConnector.Publish"]
shared NetsuiteConnector.Contents = (url as text, query as text) =>
    let 
        headers = [#"Prefer" = "transient"],
        body = Json.FromValue([q = query]),
        source = Json.Document(Web.Contents(url,[Headers = headers, Content = body]))
    in
        source; 

// Data Source Kind description
NetsuiteConnector= [
    TestConnection = (dataSourcePath) => { "NetsuiteConnector.Contents", dataSourcePath },
    Authentication = [
        OAuth = [
            StartLogin=StartLogin,
            FinishLogin=FinishLogin,
            Refresh=Refresh,
            Logout=Logout
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

// Data Source UI publishing description
NetsuiteConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = NetsuiteConnector.Icons,
    SourceTypeImage = NetsuiteConnector.Icons
];

// Helper functions for OAuth2: StartLogin, FinishLogin, Refresh, Logout
StartLogin = (resourceUrl, state, display) =>
    let
        authorizeUrl = authorize_uri/*& "?" & Uri.BuildQueryString([
            
            redirect_uri = redirect_uri,
            client_id = client_id,
            response_type = "code",           
            state = state1,
            scope = "rest_webservices"
        ])
        */
    in
        [
            LoginUri = authorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = 720,
            WindowWidth = 1024,
            Context = null
        ];

FinishLogin = (context, callbackUri, state) =>
    let
        // parse the full callbackUri, and extract the Query string
        parts = Uri.Parts(callbackUri)[Query],
        // if the query string contains an "error" field, raise an error
        // otherwise call TokenMethod to exchange our code for an access_token
        result = if (Record.HasFields(parts, {"error", "error_description"})) then 
                    error Error.Record(parts[error], parts[error_description], parts)
                 else
                    TokenMethod("authorization_code", "code", parts[code])
    in
        result;

Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);

Logout = (token) => logout_uri;


// Exchange code for access token

TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            grant_type = "authorization_code",
            redirect_uri = redirect_uri,
            client_id = client_id,
            client_secret = client_secret
        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),

        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Content-type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
            ],
            ManualStatusHandling = {400} 
        ]),
        body = Json.Document(tokenResponse),
        result = if (Record.HasFields(body, {"error", "error_description"})) then 
                    error Error.Record(body[error], body[error_description], body)
                 else
                    body
    in
        result;

Value.IfNull = (a, b) => if a <> null then a else b;

GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
    let
        prefix = Value.IfNull(scopePrefix, ""),
        addPrefix = List.Transform(scopes, each prefix & _),
        asText = Text.Combine(addPrefix, " ")
    in
        asText;
        

NetsuiteConnector.Icons = [
    Icon16 = { Extension.Contents("NetsuiteConnector16.png"), Extension.Contents("NetsuiteConnector20.png"), Extension.Contents("NetsuiteConnector24.png"), Extension.Contents("NetsuiteConnector32.png") },
    Icon32 = { Extension.Contents("NetsuiteConnector32.png"), Extension.Contents("NetsuiteConnector40.png"), Extension.Contents("NetsuiteConnector48.png"), Extension.Contents("NetsuiteConnector64.png") }
];

 

 

A sample query looks like this:

 

 

let
    result = NetsuiteConnector.Contents("https://{my_netsuite_ site}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000","SELECT * FROM transaction WHERE recordtype = 'invoice'")
in
    result

 

 

 

The tokens should only expire every hour, which I think is fine if I have to re-authenticate once an hour, but having to re-authenticate 30 times every time I change something in the query won't work. Not to mention I would eventually like to set up scheduled refresh in PBI service, so thinking about how to do that too.

 

Is there a way to implement in the pq logic of the custom connector a way to check if the current token is valid before initiating the whole OAuth flow again?

9 Replies

  • ams1's avatar
    ams1
    Responsive Resident

    Hi pbiftw92 

     

    If you look at https://learn.microsoft.com/en-us/power-query/handling-authentication#accessing-the-current-credentials, you'll see that "M data source functions that have been enabled for extensibility will automatically inherit your extension's credential scope" -> meaning that your "NetsuiteConnector.Contents" that uses Web.Contents should automatically inherit the credentials... and you're saying it doesn't automatically inherit the credentials.

     

    Not sure why 😊, but as a quick workarround  (mentioned in the above link), you can try and access the credentials record using Extension.CurrentCredential(), extract the_key_you_need_from_here and add it to the the_header_field_you_need_for_authorization:

     

     

    [DataSource.Kind="NetsuiteConnector", Publish= "NetsuiteConnector.Publish"]
    shared NetsuiteConnector.Contents = (url as text, query as text) =>
        let 
            apiKey = Extension.CurrentCredential()[the_key_you_need_from_here]
            headers = [
                #"Prefer" = "transient",
                "the_header_field_you_need_for_authorization" = apiKey 
            ],
            body = Json.FromValue([q = query]),
            source = Json.Document(Web.Contents(url,[Headers = headers, Content = body]))
        in
            source; 

     

     

    Maybe you can also look at: https://learn.microsoft.com/en-us/power-query/handling-authentication#data-source-paths

     

    Please mark this as answer if it helped.

     

  • pbiftw92's avatar
    pbiftw92
    Frequent Visitor

    This sounds promising, but unfortunately I can't get it to even recognize that function name Extension.CurrentCredential(). Also, I found that the AccessToken I need is stored in Properties in the current credential, not "Key", but first thing is simply getting that method Extension.CurrentCredential() to work in the first place. This is what I tried:

     

    [DataSource.Kind="NetsuiteConnector", Publish= "NetsuiteConnector.Publish"]
    shared NetsuiteConnector.Contents = (url as text, query as text) =>
        let
            atoken = Extension.CurrentCredential()[Key],
            headers = [#"Prefer" = "transient",#"Authorization" = Text.Combine("Bearer ", atoken)],
            body = Json.FromValue([q = query]),
            source = Json.Document(Web.Contents(url,[Headers = headers, Content = body]))
        in
            source;
     
    And I'm getting this error: 
     
    "The name 'Extension.CurrentCredential' wasn't recognized.  Make sure it's spelled correctly."
     
    Any ideas?
     
    • ams1's avatar
      ams1
      Responsive Resident

      Hi pbiftw92 

       

      Initially I also got the exact same error in Visual Studio Code SDK, but I did some things (?) and eventually it worked with another query that used Extension.CurrentCredential -> https://github.com/microsoft/DataConnectors/blob/master/samples/DataWorldSwagger/DataWorldSwagger.pq

       

      [DataSource.Kind="DataWorldSwagger", Publish="DataWorldSwagger.Publish"]
      shared DataWorldSwagger.Contents = () =>
          let
              credential = Extension.CurrentCredential(),
              token = if (credential[AuthenticationKind] = "Key") then credential[Key] else credential[access_token],        
              headers = [ Authorization = "Bearer " & token ],
              navTable = OpenApi.Document(Web.Contents("https://api.data.world/v0/swagger.json"), [ Headers = headers, ManualCredentials = true ])
          in
              credential; // <---- changed here to see that it works

       

      The things I did I think were:

      Update

      Set credential

      Test connection

       

      I think that error is caused by missing credentials (try to Set credential!).

       

      But when when i try to do Set credential with your code, I get:

      As I don't have the URLs setup etc, there are a lot of moving parts that don't allow me to easily debug.

       

      Please mark this as answer if it helped.

       

      • pbiftw92's avatar
        pbiftw92
        Frequent Visitor

        Yeah unfortunately still getting the same error in spite of trying the steps you outlined.

         

        Here's the updated code, it's the same logic really, but I figured I'd change it to be more like the DataWorld example you shared just in case that was the issue:

         

        [DataSource.Kind="NetsuiteConnector", Publish= "NetsuiteConnector.Publish"]
        shared NetsuiteConnector.Contents = (url as text, query as text) =>
            let 
                credential = Extension.CurrentCredential(),
                token = if (credential[AuthenticationKind] = "Key") then credential[Key] else credential[access_token],        
                headers = [ Authorization = "Bearer " & token,#"Prefer" = "transient"],
                body = Json.FromValue([q = query]),
                source = Json.Document(Web.Contents(url,[Headers = headers, Content = body]))
            in
                credential; 

         

         

        And I tried clearing the credentials, setting them again and then running the test connection but it still returns:

         

        "Details": "The name 'Extension.CurrentCredential' wasn't recognized.  Make sure it's spelled correctly."
         
         
        This to me looks more like this function was deprecated or somehow I'm not calling it correctly because it doesn't seem to be making it past 
        credential = Extension.CurrentCredential() without erroring.