How to specify an API key name in Excel - A web API key can only be specified when a web API key name is provided

asp.net-mvc excel odata powerbi odatacontroller
Kye picture Kye · May 10, 2018 · Viewed 13.1k times · Source

I have a standard MVC API OData enabled service which works under anonymous authentication. I would like to pass a web API key to the application...which works fine when added through code or postman..

Unfortunately my users would prefer to use Excel and I can't I find anywhere in Excel to enter this data...

I've checked Fiddler and Excel is not even trying to send data to the client and I am trapped in one of the errors below.

What is a valid key in Excel and how do it enter it?

enter image description here

enter image description here

Answer

OpenSBR picture OpenSBR · Dec 11, 2018

In Excel, open the Power Query Editor, and insert the code below, or adjust the default code: add ApiKeyName = "ApiKey" as part of the 3 parameter of OData.Feed.

let
    Source = OData.Feed("https://localhost/odata", null, [Implementation="2.0", ApiKeyName = "ApiKey"]),
    Table_table = Source{[Name="Table",Signature="table"]}[Data]
in
    Table_table

Now, you can use the Web API credentials dialog. Your key will be part part of the http query string that is sent to the server: ?ApiKey=abcdefg

See also authenticating with Excel Power Query against .Net Odata Web Api

Related questions

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine (server)

I know that is this question has dozen of answers and posts, but nothing works for me. I have my MVC 5 application and I deploy it to the IIS 7.5 to my server. Application runs great, everything is works until I …

Read More
Generating an excel file with EPPlus is failing

When I try to generate an Excel file using EPPlus, Excel give me the following error message: Excel cannot open the file 'myfilename.xlsx' because the file format or file extension is not valid. Verify the the file has not …

Read More
Upload Excel file and extract data - asp.net mvc 3

I am wondering how do I extract data out of a 2007 excel file? I am using asp.net mvc 3. My plan is to have a upload section that you choose a file and hit upload. I have no clue after …

Read More