Forum Discussion
How to refresh access token with custom connector (OAuth2)?
Hi v-hashadapu rajendraongole1
I tried generating new access token from the refresh token but issue with that is i am not able to save the new access token as Credentials so that i can use the new access token instead generating it everytime.
There is no method in power query which saves the new credentials.
Also one more question when the access token or refresh token gets expired it does not ask me for re-authentication. powerbi keeps on using old token.
shared GetValidToken = () =>
let
// Fetch the stored credentials from Power BI
tokens = Extension.CurrentCredential(),
accessToken = tokens[access_token],
refreshToken = tokens[refresh_token],
expiresAtText = tokens[expires],
// expiration time (text format)
// Convert expiration time from Text to DateTime
expiresAt = DateTime.FromText(expiresAtText),
// Get the current time
currentTime = DateTime.LocalNow(),
// Check if the token has expired
isTokenExpired = expiresAt <= currentTime,
// If expired, refresh the token
newTokens =
if isTokenExpired then
let
// Call the refresh token method to get a new access token
refreshedTokens = TokenMethod("refresh_token", refreshToken),
// "dan" is placeholder for code_verifier
newAccessToken = refreshedTokens[access_token],
// New access token
newRefreshToken = refreshedTokens[refresh_token],
// New refresh token
newExpiresAt = refreshedTokens[expires]
// New expiration time
// Store the new tokens in Power BI credentials
in
Extension.CurrentCredential(
[
access_token = newAccessToken,
refresh_token = newRefreshToken,
expires = newExpiresAt
]
)
else
tokens,
// Return the existing tokens if not expired
// Return the valid access token
validAccessToken = newTokens[access_token]
in
validAccessToken;
- rajendraongole1
Super User
Hi ArvindBhatt - You can store the refreshed tokens in a static variable (as shown in your GetValidToken function) during the session.
shared GetValidToken = () =>
let
tokens = Extension.CurrentCredential(),
accessToken = tokens[access_token],
refreshToken = tokens[refresh_token],
expiresAtText = tokens[expires],
expiresAt = DateTime.FromText(expiresAtText),
currentTime = DateTime.LocalNow(),
isTokenExpired = expiresAt <= currentTime,refreshedTokens =
if isTokenExpired then
TokenMethod("refresh_token", refreshToken)
else
tokens,validAccessToken = refreshedTokens[access_token]
in
validAccessToken;When tokens expire, you can force reauthentication by returning an HTTP 401 error in your connector.
if isTokenExpired then
error Error.Record("TokenExpired", "Access token has expired. Please reauthenticate.")Hi rajendraongole1
in above code when token is expired and we get new access token but we are not saving it anywhere as there is no function i think to save the credentials.
Extension.CurretCredentials() function will return old access token.
and about thisif isTokenExpired then error Error.Record("TokenExpired", "Access token has expired. Please reauthenticate.")
how user will re-athenticate, will it got to data source setting clear the credential and then again reconnect the connector?
or this is there any other way. (I'm new to the power bi things sorry if this is a basic question)
v-hashadapuCommunity Support
Hi ArvindBhatt , thank you for reaching out to the Microsoft Fabric Community Forum.
- Continue using the GetValidToken function to check for token expiry and refresh it when necessary.
- Use a static variable within your Power Query function to store the new tokens. Static variables persist across the lifetime of the query session.
Try this:
shared GetValidToken = () =>let
// Define a static variable to store tokens
cachedTokens = if IsNull(cachedTokens) then null else cachedTokens,
// Check if cached tokens exist and are valid
tokens =
if IsNull(cachedTokens) or DateTime.FromText(cachedTokens[expires]) <= DateTime.LocalNow() then
let
// Fetch new tokens using the refresh token
refreshedTokens = TokenMethod("refresh_token", cachedTokens[refresh_token]),
// Update the cached tokens
newCachedTokens = [
access_token = refreshedTokens[access_token],
refresh_token = refreshedTokens[refresh_token],
expires = refreshedTokens[expires]
],
// Save the new tokens in the static variable
_ = (cachedTokens <- newCachedTokens),
// Use the new tokens
tokensToUse = newCachedTokens
in
tokensToUse
else
// Use the existing cached tokens if they are still valid
cachedTokens,
// Extract the access token from the tokens to be used
validAccessToken = tokens[access_token]
in
validAccessToken;
When the token expires, you can return an HTTP 401 error to prompt the user for reauthentication.
if isTokenExpired thenerror Error.Record("TokenExpired", "Access token has expired. Please reauthenticate.")
In case of reauthentication, yes, the user will need to go to the Data Source Settings in Power BI, clear the old credentials, and reconnect the connector with new credentials.
If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.
- v-hashadapu
Hi ArvindBhatt , thank you for reaching out to the Microsoft Fabric Community Forum.
rajendraongole1 provided the correct answer. Please try it. If you still have any queries related to this issue or the answer doesn't work, please share the details.
If it does, please consider accepting his answer, so others with similar queries can find it easily.
Thank you.
