API calls with Pagination

Good Morning,

Anonymous

I've got it partially working (Its intersting since i have to embed the api key in the url, which Ibelieve I got it working, but instead of getting 100 results, i know have 0, any thoughts?

let
 iterations = 10,          // Number of iterations
 url = 
  "https://xxxxxxxxxxxxx.okta.com/api/v1/logs, [Headers=[Authorization=""SSWS xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"", ContentType=""application/json""]]",
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url)),
    data = try Source[data] otherwise null,
    next = try Source[paging][next] otherwise null,
    res = [Data=data, Next=next]
   in
    res,
 
 GeneratedList =
  List.Generate(
   ()=>[i=0, res = FnGetOnePage(url)],
   each [i]<iterations and [res][Data]<>null,
   each [i=[i]+1, res = FnGetOnePage([res][Next])],
   each [res][Data])
 in
  GeneratedList

Hi Anonymous

Here's some proof-of-concept code that might help. It doesn't use the HTTP response headers for pagination (since they're only available in a custom Data Connector such as https://github.com/gabrielsroka/gabrielsroka.github.io/blob/master/Okta.pq), so it makes repeated calls to /logs. It works, but would need more work to make it respect rate limits and to wait, maybe using Function.InvokeAfter(), etc.

See the comments and variables below for more info.

let
    startDate = #date(2018, 11, 1), // Change this.
    baseUrl = "https://XXXXXXXXXXXXXXXXXXXXX.oktapreview.com", // Change this.
    token = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX", // Change this.

    iterations = 2, // Max rate limit for "/logs" is 120 calls per minute.
    limit = 2, // For "/logs", this must between 1 and 1000. Set it lower while debugging.

    logsUrl = orgUrl & "/api/v1/logs",
    headers = [
        Authorization = "SSWS " & token, 
        Accept = "application/json", 
        #"Content-Type" = "application/json", 
        #"User-Agent" = "excel-power-query/2016 Windows/10.0.17134.345"
    ],
    iso = "s",

    // Fetch 1 day at a time. If you have more than 1000 events per day, you'll 
    // have to use a more granular time-window, eg, 1 hr at a time, or 1 minute at a time.
    GetByDateRange = (i) =>
        let
            since = Date.ToText(startDate + #duration(i, 0, 0, 0), iso),
            until = Date.ToText(startDate + #duration(i + 1, 0, 0, 0), iso),
            query = [limit = Text.From(limit), since = since, until = until],
            logs = Json.Document(Web.Contents(logsUrl, [Headers = headers, Query = query]))
        in
            logs,

    GeneratedList = List.Generate(
        () => [i = 0, res = GetByDateRange(i)],
        each [i] < iterations,
        each [i = [i] + 1, res = GetByDateRange(i)],
        each [res]),

    table1 = Table.FromList(GeneratedList, Splitter.SplitByNothing(), {"log"}),
    table2 = Table.ExpandListColumn(table1, "log"),
    fieldNames = {"displayMessage", "eventType", "published", "severity"},
    Logs = Table.ExpandRecordColumn(table2, "log", fieldNames)
in
    Logs