Forum Discussion

jracer007's avatar
jracer007
Helper IV
8 years ago

Dynamic url connection

Hello, I have a list of more than 200 url to which I must connect to obtain data. Is there any way to do this quickly without having to connect again and again?
Need Help
jracer007's avatar
jracer007
Helper IV
8 years ago

ImkeF Expression.Error: Evaluation resulted in a stack overflow and cannot continue.

  • MarkLaf's avatar
    MarkLaf
    Super User
    8 years ago

    What custom function are you trying to use? Did you confirm it works with just one instance before applying to all? Does the custom column step time out even with something simple like =Json.Document(Web.Contents([url]))  ?

  • ImkeF's avatar
    ImkeF
    Community Champion
    8 years ago

    I second what MarkLaf has said: To help you, we need to see the exact details for your function/query.

    • jracer007's avatar
      jracer007
      Helper IV
      8 years ago

      I have tried the function with a parameter but I still get the same error.

       

      This is my data funete, it contains all the urls in JSON: 

      = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vSwdO2Z5bz5axku2XRhWtLHhkVwTC2zNCcelMFxO_d5iXFNQeiKzNsjwSq6DOSLSf4xgqbekRw9Rvyu/pub?output=xlsx"), null, true)

       

      And this is my invoked custom function with one url : 

      = () => let
        Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vSwdO2Z5bz5axku2XRhWtLHhkVwTC2zNCcelMFxO_d5iXFNQeiKzNsjwSq6DOSLSf4xgqbekRw9Rvyu/pub?output=xlsx"), null, true),
        #"Hoja 1_Sheet" = Source{[Item="Hoja 1",Kind="Sheet"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(#"Hoja 1_Sheet",{{"Column1", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
        #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"links", type text}})
    in
        #"Changed Type1"

       

      This is the content desired of each url: 

      • MarkLaf's avatar
        MarkLaf
        Super User
        8 years ago

        I believe ImkeF was talking about using a function on each url, assuming there were multiple get & transform steps you needed to do on each. The function you've set up is simply accessing the Google sheet and promoting headers - it's not doing anything with a url.

         

        Since all the urls are pointing to a json doc, I don't think you need to try anything that fancy. I think the following should work for you:

         

        let
    Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vSwdO2Z5bz5axku2XRhWtLHhkVwTC2zNCcelMFxO_d5iXFNQeiKzNsjwSq6DOSLSf4xgqbekRw9Rvyu/pub?output=xlsx"), null, true),
    #"Hoja 1_Sheet" = Source{[Item="Hoja 1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Hoja 1_Sheet", [PromoteAllScalars=true]),
    GetJson = Table.AddColumn(#"Promoted Headers", "data", each Json.Document(Web.Contents([links]))),
    ExpandJson = Table.ExpandRecordColumn(GetJson, "data", {"id", "url", "user_id", "value", "item_id", "item_type", "created_at", "updated_at", "Column9"})
in
    ExpandJson

         

        Note that this is the aproximate equivalent of using the UI to:

        1. Load in the workbook and select Hoja 1_Sheet
        2. "Use First Row as Headers" button to promote headers
        3. Add a custom column (in Add Column ribbon) with "data" as the name and "Json.Document(Web.Contents([links]))" as the custom formula
        4. Expand custom column and extract all attributes

        Does that help?