Results 1 to 6 of 6

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

  1. #1
    Seeker marcob8986's Avatar
    Join Date
    Dec 2020
    Posts
    7
    Articles
    0
    Excel Version
    365

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:
    Code:
     
    let
        Origine = Excel.CurrentWorkbook(){[Name="ApiKey"]}[Content],
        Column1 = Origine{0}[Column1]
    in
        Column1
    3. Create a new blank query with this M-code:
    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.
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,483
    Articles
    43
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    166
    Articles
    0
    Excel Version
    Office 365
    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.

  4. #4
    Seeker marcob8986's Avatar
    Join Date
    Dec 2020
    Posts
    7
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Ken Puls View Post
    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
    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?

  5. #5
    Seeker marcob8986's Avatar
    Join Date
    Dec 2020
    Posts
    7
    Articles
    0
    Excel Version
    365
    Well, I actually found a solution via VBA, I'll share it here for everyone else reading here:

    Code:
    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

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,483
    Articles
    43
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •