Bearer Token Expires before GET Request Completes

[sherodkeen]

I have a couple of functions and a query that converts some data from an api to a table using power query. I am able to successfully get data for most endpoints in the API, but there are a few endpoints where the dataset is so larger that the Bearer Token expires before the GET request is complete. Because of this, the Dataflow refresh is failing.  The token is good for 30minutes. I've tried to figure out how to set the code so that it generates a new token, but it seems that it's just using the same token over and over and it eventually expires. 

Any ideas on making sure the queries are using a valid token? (The API does not have a "refresh_token" option). 

() => let
    token_url = tokenUrl,        
    body = "Client_id="&clientKey&"&Client_secret="&clientSecret&"&Grant_type="&grantType,
    // body = "Client_id="&clientKey&"&Client_secret="&clientSecret&"&Grant_type=refresh_token",
    Source = Json.Document(Web.Contents(token_url,
    [
        Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
        Content = Text.ToBinary(body)
    ]
    )
    ),
    accesstoken = Source[access_token],
    token = try accesstoken otherwise Text.Combine({accesstoken[error]?, accesstoken[error_description]?, "Bad     authToken"}, " / ")
in
  token

(Offset as number, endpoint as text, ext as text)=>
let
    // token_url = tokenUrl,        
    // body = "Client_id="&clientKey&"&Client_secret="&clientSecret&"&Grant_type="&grantType,
    // Source = Json.Document(Web.Contents(token_url,
    // [
    //     Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
    //     Content = Text.ToBinary(body)
    // ]
    // )
    // ),
    // token = Source[access_token],
    data = Json.Document(Web.Contents(baseUrl,
    [
        // Headers = [#"Authorization"="Bearer "&GetToken(),#"Content-Type"="application/json"],
        Headers = [#"Authorization"="Bearer "&GetToken(),#"Content-Type"="application/json"],
        RelativePath=relativePath&"/"&ext&"/"&endpoint,
        Query = [#"limit"=LimitOffset,#"offset"=Number.ToText(Offset)]
    ]
    )
    ),
    // #"Data" = data
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

let
  Source = List.Generate(()=> [Result = fApi(0,endpoint,ext), offset=0, endpoint="studentSectionAssociations", ext="ed-fi"],
		                        each not (Table.IsEmpty([Result])),
                                each [Result = fApi([offset], [endpoint], [ext]), offset=[offset]+Number.FromText(LimitOffset), endpoint=[endpoint], ext=[ext]],
                                each [Result]),
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Column1"}, {"Column1.1"}),
  #"Expanded Column1.1" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.1", {"id", "sectionReference", "studentReference", "beginDate", "endDate"}, {"id", "sectionReference", "studentReference", "beginDate", "endDate"}),
  #"Expanded sectionReference" = Table.ExpandRecordColumn(#"Expanded Column1.1", "sectionReference", {"localCourseCode", "schoolId", "schoolYear", "sectionIdentifier", "sessionName"}, {"localCourseCode", "schoolId", "schoolYear", "sectionIdentifier", "sessionName"}),
  #"Expanded studentReference" = Table.ExpandRecordColumn(#"Expanded sectionReference", "studentReference", {"studentUniqueId"}, {"studentUniqueId"}),
  #"Changed column type" = Table.TransformColumnTypes(#"Expanded studentReference", {{"id", type text}, {"localCourseCode", type text}, {"schoolId", Int64.Type}, {"schoolYear", Int64.Type}, {"sectionIdentifier", type text}, {"sessionName", type text}, {"studentUniqueId", type text}, {"beginDate", type date}, {"endDate", type date}})
in
  #"Changed column type"

[Jan_Hob]

@v-yalanwu-msft  I am looking for assistance with the same issue. Has anyone managed to solve this?

[sherodkeen]

Anyone have any suggestions?

[v-yalanwu-msft]

Hi, @sherodkeen ;

You can check if these related post can is useful to you.

Solved: OAuth Refresh token has expired after 90 days - Microsoft Partner Community

Refresh the access token in Power BI embedded analytics | Microsoft Learn

Troubleshooting refresh scenarios - Power BI | Microsoft Learn

Access & Refresh Tokens | Online Help - Zoho CRM

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sherodkeen]

Unfortunatly, none of the links you provided help in this specific case. Is there anything I can modify in my code to ensure that it is using a new token for each api call? 

[sherodkeen]

I'm attempting now to try and put the GetToken() function as a parameter in the table query in the List.Generation part of the function. Hoping this will retrigger the try for a token, which should be new each time the query is ran. 

[sherodkeen]

I'm still struggling with this one. Looks like everytime the loop runs in the List.Generate(), the call to my custom GetToken() function is just regiving the loop the same token. I was hoping to get a new token on each loop so that I wouldn't run into the 30 min expiration that each token has. 

let
  Source = List.Generate(()=> [Result = fApi(0,endpoint,ext,accesstoken), offset=0, endpoint="staffs", ext="ed-fi", accesstoken = GetToken()],
		                        each not (Table.IsEmpty([Result])),
                                each [Result = fApi([offset], [endpoint], [ext], [accesstoken]), offset=[offset]+Number.FromText(LimitOffset), endpoint=[endpoint], ext=[ext], accesstoken = GetToken()],
                                each [accesstoken])
in
Source

(Offset as number, endpoint as text, ext as text, accesstoken as text)=>
let
    data = Json.Document(Web.Contents(baseUrl,
    [
        Headers = [#"Authorization"="Bearer "&accesstoken,#"Content-Type"="application/json"],
        RelativePath=relativePath&"/"&ext&"/"&endpoint,
        Query = [#"limit"=LimitOffset,#"offset"=Number.ToText(Offset)]
    ]
    )
    ),
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

() => let
    token_url = tokenUrl,        
    body = "Client_id="&clientKey&"&Client_secret="&clientSecret&"&Grant_type="&grantType,
    Source = Json.Document(Web.Contents(token_url,
    [
        Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
        Content = Text.ToBinary(body)
    ]
    )
    )
in
  Source[access_token]

[sherodkeen]

@v-yalanwu-msft Do you have any further ideas for help?