Custom Data Connector: Refreshing JWT Handling

Anonymous · ‎07-07-2021

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";