Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pbiftw92
Frequent Visitor

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 9
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?
 

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

ams1_0-1679983783181.png

Set credential

ams1_2-1679983865647.png

Test connection

ams1_1-1679983837973.png

 

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:

ams1_3-1679984184208.png

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

Hi @pbiftw92 

 

My 4th edit of this post 😊: please confirm the OAuth Flow that you have to use and share the link to the documentation:

 

Is this the relevant link?

https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_158074210415.html

 

I think we're mixing up the Flows and therefore the auth doesn't work and therefore "The name 'Extension.CurrentCredential' wasn't recognized".

 

Usually when you see client_id and client_secret, that's usually the Client Credentials Flow which is DIFFERENT than Authorization Code Grant flow that you seem to be using

 

 

pbiftw92
Frequent Visitor

No I'm using authorization code flow: https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_158074210415.html

 

I think I tried client credentials but couldn't get it to work.

Thanks @pbiftw92 

 

First of all you should make sure that the flow you're using is enabled in Netsuit admin.

 

Second, IF you're using Authorization Code Grant Flow, then from the documentation (https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_158081952044.html) we see that the 2nd step POST request has to have the following x-www-form-urlencoded parameters:

  • code
  • redirect_uri
  • grant_type = authorization_code
  • code_verifier (i think this is not needed)

BUT from your code (also below), I see you're passing:

  • code
  • redirect_uri
  • grant_type = authorization_code
  • client_id?
  • client_secret?

 

 

 

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;

 

 

 

 

Sooo, as I see in the documentation, you should remove client_id and client_secret from the queryString and instead place them in the Headers, in the "Authorization" key clientid:clientsecret Base64 encoded -> quote "The client authentication method used in the header of the request follows the HTTP Basic authentication scheme. For more information, see RFC 7617. The format is clientid:clientsecret. The string value is Base64 encoded. "

 

They provide an example request:

 

 

 

POST /services/rest/auth/oauth2/v1/token HTTP/1.1
Host: <accountID>.suitetalk.api.netsuite
Authorization: Basic base64blabla (!!! YOU ARE MISSING THIS !!!)
Content-Type: application/x-www-form-urlencoded

code=bla_bla&redirect_uri=bla_bla&grant_type=authorization_code&code_verifier=?!

 

 

 

Something like:

 

TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            grant_type = "authorization_code",
            redirect_uri = redirect_uri
        ],
        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",
                "Authorization" = "Basic " & Binary.ToText(Binary.FromText(client_id & ":" & client_secret, TextEncoding.Utf8), BinaryEncoding.Base64)
            ],
            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;

 

 

Please mark this as answer if it helped.

@pbiftw92 any updates on this?

pbiftw92
Frequent Visitor

This method you suggested works with a slight modification, namely instead of 

 

TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            grant_type = "authorization_code",
            redirect_uri = redirect_uri
        ],
        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",
                "Authorization" = "Basic " & Binary.ToText(Binary.FromText(client_id & ":" & client_secret, TextEncoding.Utf8), BinaryEncoding.Base64)
            ],
            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;

 

I did 

 

TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            grant_type = "authorization_code",
            redirect_uri = redirect_uri
        ],
        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",
                #"Authorization" = "Basic " & Binary.ToText(Text.ToBinary(client_id & ":" & client_secret), BinaryEncoding.Base64)
            ],
            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;

 

This does work in the sense that it returns results, but unfortunately it still has the same issue as the method I was using before where it requires reauthentication every time a query is changed, duplicated or refreshed and doesn't test the current token first before initiating the flow again.

 

Were you suggesting to combine this change in method with the Extension.CurrentCredential() thing? I can try that next and update if this change makes any difference (e.g. eliminates the errors I was getting previously with Extension.CurrentCredential()).

 

And yes this is all enabled for my role in the Netsuite UI, otherwise none of these methods would return results at all and would give 404 etc. (I went through that whole journey previously 😀) ... 

 

My general thinking is that the API calls are working fine, but it's actually the M logic that I have implemented that is causing the issue. I borrowed that and repurposed from other OAuth examples I found online, but maybe this Netsuite flow requires specific logic to get it to work. Like for example, I need to add an explicit check/reference to the current token or something...

ams1
Super User
Super User

Hi @pbiftw92 

 

If you look at https://learn.microsoft.com/en-us/power-query/handling-authentication#accessing-the-current-credenti..., 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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors