Hello,
I am currently working on developing a Custom Data Connector which calls a Security API for a refreshable JWT that expires in 30 minutes.
This token is passed to the header of the data API request. The data API is paginated by incrementing the "page" variable in the URL by 1. I achieve this in a loop to return all my results in one table.
The data API request returns an updated JWT token in the response header, but as I understand it there is no way for me to access that using Value.Metadata for security reasons.
I am trying to figure out how I can refresh my JWT or include it in my pagination loop so the token does not expire while making my calls to the data API.
This API does not adhere to OAuth standards.
Any help would be greatly appreciated.
section test_help;
[DataSource.Kind="test_help", Publish="test_help.Publish"]
shared test_help.Contents = (optional message as text) =>
//getPages function
let getPages = (Page as number) as table =>
//security API url
let url = "https://sapif.callminer.net/security/getToken",
//Authentication credentials passed to body of feapif
body = "{""Username"": ""fake"", ""Password"": ""fake"", ""ApiKey"": ""fake""}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
//endDate Variable to capture current date time in UTC
endDate = DateTimeZone.LocalNow(),
//date search GET Request
Host = "https://feapif.callminer.net/api/v2/export/datesearch",
//custom date search path with DateTimeZone.ToText for enddate and page parameter for pagination
//score ID 58 for MS and 55 for WC
Path = "?startDate=2021-05-01T12%3A00%3A00.000Z&stopDate="&DateTimeZone.ToText((endDate)) & "&page="& Number.ToText(Page) &"&records=50&clientCaptureDate=true&scoreIds=58,55",
Source = [
//JWT auth token passed to Data API header
token = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json; charset=utf-8"], Content = Text.ToBinary(body) ] )),
Source = Json.Document(Web.Contents(Host,
[RelativePath=Path,Headers=[#"Content-Type"="application/json; charset=utf-8", Authorization="JWT " & token]]))
],
Source1 = Source[Source],
#"Converted to Table" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error) ,
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"RecordInfo", "Contact", "Attributes", "Measures", "Others", "Sections", "Categories", "Scores", "ScoreComponents"}, {"Column1.RecordInfo", "Column1.Contact", "Column1.Attributes", "Column1.Measures", "Column1.Others", "Column1.Sections", "Column1.Categories", "Column1.Scores", "Column1.ScoreComponents"}),
#"Expanded Column1.RecordInfo" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.RecordInfo", {"Id", "RowNumber", "TotalRowCount"}, {"Column1.RecordInfo.Id", "Column1.RecordInfo.RowNumber", "Column1.RecordInfo.TotalRowCount"})
in #"Expanded Column1.RecordInfo",
//Pagination loop
//Define function
Source = try List.Generate( ()=>
//try getPages (initialize page variable at 1), if records returned are not null increment page +1 if null stop. Convert list of records to table.
[Result = try getPages(1) otherwise null, Page = 1],
each [Result] <> null,
each [Result = try getPages([Page]+1) otherwise null, Page = [Page]+1],
each [Result]
)
otherwise {"1","null","null"},
//try catch block to capture empty lists/records
#"Converted to Table" = try Table.FromList(Source, Splitter.SplitByNothing(), 1, null, ExtraValues.Error) otherwise Table.FromList({"1"}, Splitter.SplitByNothing(), 1, null, ExtraValues.Error) ,
#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Column1.RecordInfo.Id", "Column1.RecordInfo.RowNumber", "Column1.RecordInfo.TotalRowCount", "Column1.Contact", "Column1.Attributes", "Column1.Measures", "Column1.Others", "Column1.Sections", "Column1.Categories", "Column1.Scores", "Column1.ScoreComponents"}, {"Column1.RecordInfo.Id", "Column1.RecordInfo.RowNumber", "Column1.RecordInfo.TotalRowCount", "Column1.Contact", "Column1.Attributes", "Column1.Measures", "Column1.Others", "Column1.Sections", "Column1.Categories", "Column1.Scores", "Column1.ScoreComponents"})
in #"Expanded Column1";
