API Expiring Bearer Token - Check if expired???

[AUaero]

Hello,

I've got a function that requests a bearer token from a secure API.  The token expires one week from issuance.  I'd like to be able to save the token in a table and only refresh the table if the token has expired.  Here's my function:

let
    Source = () => let
        url = "https://www.foobar.com/token",
        un = "foo@bar",
        pw = "password123",
        body = "grant_type=password&userName=" & un & "&password=" & pw,
        GetJson = Web.Contents(
            url,
            [
                Headers = [
                    #"Content-Type" = "application/x-www-form-urlencoded"
                ],
                Content = Text.ToBinary(body)
            ]
        ),
        FormatAsJson = Json.Document(GetJson)
    in
        FormatAsJson
in
    Source

The function returns the following in Power Query:

So here's the question - Is there a way only request a token if the current date is greater than the expiration date of the token?

Thanks!

[lbendlin · Accepted solution]

You cannot store the expiration date in Power Query. It simply has no memory. What you can do, however, is write it into a SQL server database table (or any other db environment) via  the mythical NativeQuery function

https://docs.microsoft.com/en-us/powerquery-m/value-nativequery

And then you could retrieve that data point as part of your regular process.

There are some examples here in the forum of people abusing that function for all kinds of complex ETL processes. Highly entertaining, highly questionable.

Another alternative would be to ignore the expiry date and just ask for a new token regardless.

[AUaero]

I'm still learning M, so the NativeQuery functionality is news to me.  You've just opened the door to all kinds of questionable practices.  🤣

So the reason I'm even trying to figure this out is this:  I wrote the script to request the token as a function so that I can call it from my other queries when I hit an API endpoint, such as:

let
    AccessToken = GetToken,
    url = "https://www.foobar.com/api/order",
    GetJson = Json.Document(
        Web.Contents(
            url, 
            [   
                Headers = 
                [
                    Authorization=AccessToken, 
                    #"Content-Type"="application/json; charset=utf-8"
                ]
            ]
        )
    ),
    ToTable = Table.FromList(GetJson, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandRecordColumn(ToTable, "Column1", Record.FieldNames(ToTable{0}[Column1]))    
in
    Expand

The only reason I've got it as a function is because if I just have the token request as a query, Power Query complains with the Formula.Firewall error and asks me to rebuild the data combination.  For some reason, if it's a function instead of a query it works fine.

This is fine except for I've got to hit the endpoint hundreds of times to query additional information.  I don't want to spam the API with token requests.  Any idea on working around the formula.firewall issue?

[lbendlin]

The formula firewall is there for exactly that reason - to prevent you from having data bleed between data sources. That's why re-using the token in other queries is so difficult.

In your function you can emulate that memorizing of the token in the way we discussed.  First query the database if a token is present with an expiry time in the future.  If yes, serve that token. If no, get a new token, save it to the database (via NativeQuery), and then serve it .