Forum Discussion

Grant_Reid's avatar
Grant_Reid
Frequent Visitor
3 years ago
Solved

REST API Data Source refreshes in Desktop but not in Service

I’ve been tasked to roll out a solution provided by a third party. Everything works perfectly in Desktop and the expected number of records are returned. However when published to the Service I get t...
Need Help
  • Grant_Reid's avatar
    Grant_Reid
    3 years ago

    Hi All

     

    Many thanks Imke for helping me resolve this. Working code below.

    let
    // Build the URL for the API call
    #"Header" = Binary.ToText(Text.ToBinary(#"User Email" & ":" & #"API Token")), 
    mainURL = "https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info", 
    startQuery = [#"page[first]" = "50"], 
    /*  Description: Fetches one page of data
        url - URL to fetch data
      Output Parameter:
        ret - record consisting of two elements
        retData - data for current page
        retNext - URL for next page
  */
    getOnePage = (query) as record =>
        let
            // Get the specified URL and parse the JSON response
            devicePage = Json.Document(
                Web.Contents(
                    mainURL, 
                    [
                        Query = query, 
                        Headers = [
                            #"Authorization" = "Basic " & #"Header", 
                            #"content-type"  = "application/json"
                        ]
                    ]
                )
            ), 
            // Extract the data and links fields and return it
            deviceData = try devicePage[data] otherwise null, 
            next = try devicePage[links][next] otherwise null, 
            ret = [retData = deviceData, retNext = next]
        in
            ret, 
    // Fetch each page until there are no more pages by following the next link. Append subsequent pages.  
    deviceList = List.Generate(
        // Fetch the first page
        () => [ret = getOnePage(startQuery)], 
        // Stop when there is no more data
        each try [ret][retData] <> null otherwise false, 
        // Get the next page using the next link - numbers in the query parameters must be formatted as text, hence the gymnastics
        each [
            ret = getOnePage(
                Record.FromTable(
                    Table.TransformColumnTypes(
                        Record.ToTable(Uri.Parts([ret][retNext])[Query]), 
                        {{"Value", type text}}
                    )
                )
            )
        ], 
        // Return only the data
        each [ret][retData]
    ),
    Custom1 = deviceList
in
    Custom1

     

Grant_Reid's avatar
Grant_Reid
Frequent Visitor
3 years ago

Hi Imke

Thank you very much for your prompt response.

I plugged in your modified code as I received it. Unfortunately I did not get the expected result. There still seems to be an issue with the List.Generate operation.

I tried your suggestion of using "page[after]=" & [ret][retNext] as function arguments. Not sure I put this in the correct place though - see code below.

 

/* Description: Fetches the device inventory for the sites that the user is authorized on.
  Input Parameter:
   Request for inventory list
  Output Parameter:
    Device Inventory - Table of devices data for the sites that the user is authorized on.
*/

let
  // Build the URL for the API call
  #"Header" = Binary.ToText(Text.ToBinary(#"User Email" & ":" & #"API Token")),
  #"BaseURL" = "https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info",
  #"Query" = "page[first]=1000",
  //startUrl = "https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info?page[first]=1000",
 // startUrl = #"BaseURL" & #"Query", 

  /*  Description: Fetches one page of data
        url - URL to fetch data
      Output Parameter:
        ret - record consisting of two elements
        retData - data for current page
        retNext - URL for next page
  */
  getOnePage =
    (query) as record =>
    let
        // Get the specified URL and parse the JSON response
        devicePage = Json.Document(
        Web.Contents(
          #"BaseURL",
           [
             Query = query,
             Headers=[
               #"Authorization" = "Basic " & #"Header",
               #"content-type" = "application/json"
               ]
            ]
        )
    ),
       
       
        // Extract the data and links fields and return it
        deviceData = try devicePage[data] otherwise null,
        next = try devicePage[links][next] otherwise null,
        ret = [retData=deviceData, retNext=next]
    in
        ret,

  // Fetch each page until there are no more pages by following the next link. Append subsequent pages.  
  deviceList = List.Generate(
    // Fetch the first page
    ()=> [ret = getOnePage("page[first]=1000")],
    // Stop when there is no more data
    each [ret][retData]<>null,
    // Get the next page using the next link
    //each [ret = getOnePage([ret][retNext])], // maybe you have to use "page[after]=" & [ret][retNext] as function arguments instead
    each [ret = getOnePage("page[after]=" & [ret][retNext])],
    // Return only the data
    each [ret][retData])
in
    deviceList

 

Hope you can help. Am at my wits end with this. Probably in over my head.

Kind Regards - Grant