topic Re: Custom Connector with Token Refresh in Developer

Custom Connector with Token Refresh

Anonymous — Fri, 04 Feb 2022 17:51:12 GMT

Hi all,

I need to create a Custom Connector to query Prisma Cloud REST API (sadly there isn't already a connector), the platform takes a username and password JSON POST to /login and returns a JWT token, that token then needs to be added to the header when calling for data.

Some of the reports I need create are many pages long and the token only lasts around 8 minutes so I need to set a timer to extend the token around the 5 minute mark, to do this I need to issue a GET to /auth_token/extend that will return a new token I can use for another 5 minutes or until my paginator has complete.

Once I have my token I can POST my query to /v2/alert which will keep returning json.nextPageToken (that needs to be added to payload.pageToken) until the load is complete

I've written a connector for DOMO which is pure JavaScript but I'm new to PowerBI and the M language, I'm assuming this is two complex for the magic functions to help me, any tips would be much appreciated.

Many thanks in advance.

Re: Custom Connector with Token Refresh

lbendlin — Sun, 06 Feb 2022 01:15:12 GMT

That seems all doable. What do you need help with?

Re: Custom Connector with Token Refresh

Anonymous — Sun, 06 Feb 2022 10:30:38 GMT

Not writing a connector before the areas I would need help with is.

1. Authentication function getting the username and password from the user to get a token, then updating the token after a time (or number of data calls if easier)

2. How to create a while loop or similar to keep getting data until the next page is blank.

the trippin example uses the magic functions so I wasn't sure how to do this manually. I'm a PHP/JavaScript guy so it's getting my head around the new layout (sorry), I have been looking at the tutorials.

Re: Custom Connector with Token Refresh

lbendlin — Sun, 06 Feb 2022 14:33:47 GMT

I wouldn't worry about #1 just yet, you can sprinkle that in later. For #2 you can choose between List.Generate, List.Accumulate, and recursive function calls.

Re: Custom Connector with Token Refresh

Anonymous — Mon, 07 Feb 2022 13:20:49 GMT

Sadly I'm struggling to get out of the gate lol

The authentication is working correctly and returns me a token, which I've stored in token

loginPayload = [ username = Extension.CurrentCredential()[Username], password = Extension.CurrentCredential()[Password] ], loginBody = Json.Document(loginPayload), responseLogin = Web.Contents("https://api.company.io/login", [ Headers = [#"Content-Type" = "application/json"], Content = loginBody ]), token = Json.Document(responseLogin)[token],

I now want to use this to query the endpoint:

responseAlert = Web.Contents("https://company.io/v2/alert", [ Headers = [ #"Content-Type" = "application/json", #"x-redlock-auth" = token ], Content = alertBody ]), a = Json.Document(responseAlert)

However Power Query keeps complaining "The parameter is expected to be of type Text.Type or Binary.Type", I tried wrapping Record.Field and Text.From but I cannot seem to be able to use my extracted JSON value as a header later down the line, any ideas?

Re: Custom Connector with Token Refresh

V-lianl-msft — Wed, 09 Feb 2022 09:29:30 GMT

Hi @Anonymous ,

Hope these will help you:

https://community.powerbi.com/t5/Desktop/The-parameter-is-expected-to-be-of-type-Text-Type-or-Binary-Type/m-p/49345

https://radacad.com/exception-reporting-in-power-query-and-power-bi-part-2-catching-error-rows-for-all-columns-in-the-table-section-1

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.