使用Excel Power Query对.Net Odata Web Api进行身份验证

I am trying to use Power Query to download an Odata Feed that I created using .net Web Api 2 and the OData v4 nuget package.

我正在尝试使用Power Query下载我使用.net Web Api 2和OData v4 nuget包创建的Odata Feed。

I'm trying to access an Odata feed that requires authentication. When I edit the authentication type in power query, I'm never seeing the authentication key come through in the request.

我正在尝试访问需要身份验证的Odata Feed。当我在电源查询中编辑身份验证类型时，我从未在请求中看到身份验证密钥。

How do you configure Power Query to use a specific type of authentication?

如何配置Power Query以使用特定类型的身份验证？

Bonus: I'm using OAuth, so how would I configure power query to send in a header with auth data that includes "Authorization Bearer:token_here"

额外奖励：我正在使用OAuth，那么如何配置电源查询以使用包含“授权承载：token_here”的auth数据发送标头

2 个解决方案

#1

Web API credentials are for putting a secret value into to the URL query (i.e. your API key for some website).

Web API凭据用于将秘密值放入URL查询（即某些网站的API密钥）。

There's currently no way to add your own Bearer token in Power Query from the credential dialog.

目前无法从凭证对话框中在Power Query中添加您自己的Bearer令牌。

It's less secure and can't be refreshed, but you can hardcode your credential directly using OData.Feed's Header parameter:

它不太安全且无法刷新，但您可以使用OData.Feed的Header参数直接对您的凭证进行硬编码：

= OData.Feed("http://localhost/", null, [Headers = [Authorization = "Bearer token_here" ] ])

(Alternatively, it might be easier to configure your server to accept Basic auth, which is supported in Power Query.)

（或者，可能更容易将服务器配置为接受Power Query中支持的Basic身份验证。）

#2

This is how it should be done and this is tested and working. Token is always regenerated.

这是应该如何完成的，并且经过测试和运行。令牌总是重新生成。

let
GetJson = Json.Document(Web.Contents("https://myservice.azurewebsites.net/oauth/token",
     [
         Headers = [#"Accept"="application/json",
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
         Content = Text.ToBinary("login=MYUSERNAME&password=MYPASSWORD&grant_type=password")
     ])),
    access_token = GetJson[access_token],
    AccessTokenHeader = "Bearer " & access_token,
JsonTable =  Json.Document(Web.Contents(

  "https://myservice.azurewebsites.net/odata/Cities",
  [
   Query=[ #"filter"="", #"orderBy"=""],
   Headers=[#"Authorization" = AccessTokenHeader ]
  ])),
#"Cities" = Table.FromRecords(JsonTable[value])
in
    #"Cities"

// Note, when setting privacy credential, set it to "Organizational", and not private and surely not public.

//注意，在设置隐私凭证时，请将其设置为“组织”，而不是私密且肯定不公开。

#1