Formula.firewall trying to call a web API with dynamic parameter

[marcob8986]

Hi there,
I'm finally writing here beacuse I've been struggling two full days on this and I haven't got the hang of it yet, so I hope to find some help here.

My desired outcome: being able to call a web API which needs authentication reading the key string from an Excel cell, letting the user to put in his own key.

I'v used this API several times for my own sake with my own personal key "hardcoded" in the source url, so everything is working.
This is the full URL:

https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=400&convert=EUR&CMC_PRO_API_KEY=6d70b042-1df4-4e46-8877-e0db4ed4f25d

After the "CMC_PRO_KEY=" goes the parameter I need to get from Excel itself.

My first attempt has been:
1. Name the range as "ApiKey"
2. Load this range in Power Query with this M-code:

let
    Origine = Excel.CurrentWorkbook(){[Name="ApiKey"]}[Content],
    Column1 = Origine{0}[Column1]
in
    Column1

3. Create a new blank query with this M-code:

let
    Origine = Json.Document(Web.Contents("https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=400&convert=EUR&CMC_PRO_API_KEY=" & ApiKey)),
    data = Origine[data],
    #"Conversione in tabella" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Tabella Column1 espansa" = Table.ExpandRecordColumn(#"Conversione in tabella", "Column1", {"name", "symbol", "quote"}, {"name", "symbol", "quote"}),
    #"Tabella quote espansa" = Table.ExpandRecordColumn(#"Tabella Column1 espansa", "quote", {"EUR"}, {"EUR"}),
    #"Tabella EUR espansa" = Table.ExpandRecordColumn(#"Tabella quote espansa", "EUR", {"price"}, {"price"})
in
    #"Tabella EUR espansa"

Everythinh worked perfectly fine to me until I "distributed" the file to my team: everyone was getting formula.firewall errors or OLE.DB errors and so on.
I then realized I had PowerQuery set to ignore privacy levels and they didn't. Ignoring privacy levels in their pc is not a solution so I started to loook all over the web and I landed on Ken post here, but I can't seem to find a solution for my problem.

Can some of you guys help me out?
Attached the bare file with the connection which works if privacy is ignored and doesm't otherwise.

 

[Ken Puls]

Hey there, are you sure you want to leave your API key in plain text?

Unfortunately, the formula firewall is there to prevent you from combining two data sources together for one call, and that's exactly what you're doing here. So basically, you have two options:
1) Turn off privacy to avoid the formula firewall all together, or
2) Use an official Parameter to hold your APIKey inside the Query Editor

I'm assuming that you're probably trying to set this up so that the users can enter their own key, and Excel doesn't have an easy way to update a parameter without going in to the power query editor, but it will allow you to avoid the firewall.

To create a parameter:

• Open Power Query
• Go to Manage Parameters - NewParameter
• Name it and drop your API Key into the Current Value area
• Plumb that in to your query instead of the Excel.Workbook call

Then you need to leave instructions to your users that they will need to open Power Query, select Manage Parameters -> Edit and update their key.

This is one of those tricky ones that just can't be avoided, unfortunately.

 

[pinarello]

Just by the way!

Since the use of functions that require an API key can cause costs (e.g. Google distance matrix), I have stored my API keys in the personl.xlsx. Using VBA and PQ I can read them out if needed. So I can pass on folders containing macros or PQ queries without having to delete API keys first.

 

[marcob8986]

Hey there, are you sure you want to leave your API key in plain text?

Unfortunately, the formula firewall is there to prevent you from combining two data sources together for one call, and that's exactly what you're doing here. So basically, you have two options:
1) Turn off privacy to avoid the formula firewall all together, or
2) Use an official Parameter to hold your APIKey inside the Query Editor

I'm assuming that you're probably trying to set this up so that the users can enter their own key, and Excel doesn't have an easy way to update a parameter without going in to the power query editor, but it will allow you to avoid the firewall.

To create a parameter:

• Open Power Query
• Go to Manage Parameters - NewParameter
• Name it and drop your API Key into the Current Value area
• Plumb that in to your query instead of the Excel.Workbook call

Then you need to leave instructions to your users that they will need to open Power Query, select Manage Parameters -> Edit and update their key.

This is one of those tricky ones that just can't be avoided, unfortunately.

Many thanks Ken. I'm kinda relieved, I was expecting one of your easy answers and then I would have needed to hide myself for at least a week:bounce:
For the record: the API KEY I posted will be disabled after closing this post

This is one way to do it, many thanks again.

Now the next question: since the file needs to be completely password protected, users won't be able to access Power Query editor. My file is already macro enabled, is there a way of editing the parameter via VBA? So I could unprotect everythin, edit the parameter reading a cell, protect everything again, and then run all my queries?

 

[marcob8986]

Well, I actually found a solution via VBA, I'll share it here for everyone else reading here:

Dim userKey As String
Dim paramName As String

paramName = "MyAPIKey"
 
userKey = Range(ThisWorkbook.Names("UserKey")).Value
 
ThisWorkbook.Queries.Item(paramName).Formula = """" & userKey & """ meta [IsParameterQuery=true, Type=""Any"", IsParameterQueryRequired=true]"

As simple as that.
Many thanks for getting on the right path to both of you guys

 

[Ken Puls]

Sorry, I avoided my computer over the weekend.

Yes, you can get to the .Formula for any power query object (query, function or parameter) and modify them, or even create them on the fly as well.