6

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

Kye
  • 5,919
  • 10
  • 49
  • 84

1 Answers1

2

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

OpenSBR
  • 31
  • 2
  • 1
    Could you expand your answer to explain how to do the same thing if the API token must be passed as a header? I have tried `[Headers=[ApiKeyName="Authorization"]]` but I get the same error as the OP. – Geoff May 09 '19 at 15:43