Forum Discussion

sherodkeen's avatar
sherodkeen
Frequent Visitor
3 years ago

Bearer Token Expires before GET Request Completes

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"

 

7 Replies