Dynamic API URL that constantly change - Authorization needed possibly

[ftg]

Hi

We have a dynamic API URL that constantly changes. example:

https://xxxxxxxxxxxxxxxxxxx/8f02695b-41ec-4a13-bd69-02bb347bd0b9/Export_Job_API_Product_Export.json

https://xxxxxxxxxxxxxxxxxxx/8f02785b-31ec-9a13-bx69-0xbsdfb45zd59/Export_Job_API_Product_Export.json

I use a python script to obtain this URL currently and with each run, the URL will change.

I am able to add this API URL into the "Web" Source and the data will pull, however, I would like to make this dynamic so that the python script will run and pull down the new URL, and then that URL will be inserted into the Web field automatically.

I have tried Web.Content([URL]) and Json.Document(Web.Content([URL]) but it returns no results.

Here is an example of what I've currently tried:

Once I've pulled the API the advanced editor gives me this:

I'd like to insert the URL [df] into the Advanced editor so that it constantly pulls through the new updated URL, but if I do, I get this error

I feel like I'm missing something very simple but can't figure out what is it. 

I also looked at parameters but I can't seem to choose the [df] table as one.

Please let me know if there is a solution to this.

By the way, the reason we run a python script is that the API is technically an export of sorts, so we call the export and then iterate through the API waiting until the export is complete. Once it's complete, we then pull the URL. 

[PC2790]

Can you share the code of the specific step wher eths error is coming?

Looks like something wrong with the each statement that you are using somewhere

[ftg]

Here is the python script we use to get the URL:

import requests
import json
import time
import pandas as pd

#Ontap

export_url = "https://xxxxx/exportjob/api/"
token = ""
headers = {}
export_type = "product"
export_value = ""
filter_date = ""
ftg_one_token = "xxxxx"
org_id = xxxxx

optional_filters = {}

optional_filters = {
    "organisation_id": org_id,
    "archive__exact": False,    
    "display_on_web__exact": True
}

export_types = {
    "product": {
        "content_type": 352,
        "data_mapping": 1032
    },
    "archived_products": {
        "content_type": 352,
        "data_mapping": 1032,
        "extra_filter_rules": '''{
            "archive": true
        }'''
    },
    "non_archived_products": {
        "content_type": 352,
        "data_mapping": 1032,
        "extra_exclude_rules": '''{
            "archive": true
        }'''
    },
    "product_erp_info_with_stock_total": {
        "content_type": 1081,
        "data_mapping": 1028
    },
    "erp_location_info": {
        "content_type": 1083,
        "data_mapping": 1031,
    },
}

headers = {
    "Authorization": "Token {}".format(ftg_one_token)
}

export_value = export_type
export_type = export_types[export_type]
   

payload = {
    "title": "Export Job",
    "content_type": export_type['content_type'],
    "data_mapping": export_type['data_mapping'],
    "status": "queued",
    "extra_filter_rules": export_type.get('extra_filter_rules', ''),
    "extra_exclude_rules": export_type.get('extra_exclude_rules', ''),
    "organisation": 1,
    "created_by": 1,
}

payload["extra_filter_rules"] = json.dumps(optional_filters)

response = requests.request(
    "POST", export_url, headers=headers, data=payload)
export_job = response.json()

while export_job['status'] in ['queued', 'not_queued', 'running']:            
    time.sleep(1)
    response = requests.request(
        "GET", export_url + str(export_job['id']), headers=headers)
    export_job = response.json()
       
result_urls = [file['url'] for file in export_job['get_instance_documents_json']]
df = pd.DataFrame(result_urls)

From this I get the following in Power BI:

I then want to be able to insert that URL into a New Source via Web function which I have done here by pasting it in:

I am then able to get the JSON, however, I need to be able to update the URL dynamically from the python script into the Web source:

(Sorry about the bad screen shot)

Basically I would Like the following code to change from:
let
Source = Json.Document(AzureStorage.BlobContents("https://xxxx/Export_Job_API_Product_Export.json")),

To this:

let
Source = Json.Document(AzureStorage.BlobContents("[0] ***<< this is URL from python script***)),

Does that make sense?