如何为Power BI正确构建Clio Power Query自定义连接器——令牌处理及Client ID/Secret动态配置技术咨询

Hey there! Let's work through your two Clio Power Query connector issues step by step. I've built a few OAuth-based connectors before, so here's how I'd fix this:

1. 实现正确的令牌处理逻辑 #

Your current TokenMethod has mixed-up parameters and missing logic for distinguishing between initial authorization and token refreshes. Let's restructure it to properly handle both authorization_code (first-time login) and refresh_token (token renewal) flows, and ensure the related functions call it correctly.

修正后的令牌处理代码 #

section Clio;

// 先定义凭证（后面会替换为动态加载）
client_id_code = "", // 暂时留空，后面替换
client_secret_code = "",
OAuthBaseUrl = "https://app.clio.com/api/v4";

[DataSource.Kind="Clio", Publish="Clio.Publish"] shared Clio.Contents = (optional message as text) => let 
    _message = if (message <> null) then message else "(no message)", 
    a = "Hello from Clio: " & _message 
in a;

// Data Source Kind description
Clio = [
    Authentication = [
        OAuth = [
            StartLogin = StartLogin,
            FinishLogin = FinishLogin,
            Refresh = Refresh // 必须指定Refresh函数，让PQ自动刷新令牌
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

// Data Source UI publishing description
Clio.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = Clio.Icons,
    SourceTypeImage = Clio.Icons
];

StartLogin = (resourceUrl, state, display) => let
    AuthorizeUrl = OAuthBaseUrl & "/oauth/authorize?" & Uri.BuildQueryString([
        response_type = "code",
        client_id = client_id_code,
        redirect_uri = "https://app.clio.com/oauth/approval",
        state = state
    ])
in [
    LoginUri = AuthorizeUrl, // 直接用构造好的授权URL，不是主页
    CallbackUri = "https://app.clio.com/oauth/approval",
    WindowHeight = 1080,
    WindowWidth = 720
];

FinishLogin = (context, callbackUri, state) => let
    Parts = Uri.Parts(callbackUri)[Query]
in TokenMethod("authorization_code", Parts[code]);

//Token Handling - 修正版
TokenMethod = (grantType as text, optional tokenValue as text) => let
    // 根据授权类型构建请求参数
    query = if grantType = "authorization_code" then
        [
            client_id = client_id_code,
            client_secret = client_secret_code,
            grant_type = grantType,
            code = tokenValue,
            redirect_uri = "https://app.clio.com/oauth/approval" // 必须和StartLogin一致
        ]
    else if grantType = "refresh_token" then
        [
            client_id = client_id_code,
            client_secret = client_secret_code,
            grant_type = grantType,
            refresh_token = tokenValue
        ]
    else error "Unsupported grant type",
    ManualHandlingStatusCodes = {400,403},
    Response = Web.Contents(OAuthBaseUrl & "/token", [
        Content = Text.ToBinary(Uri.BuildQueryString(query)),
        Headers = [
            #"Content-type" = "application/x-www-form-urlencoded",
            #"Accept" = "application/json"
        ],
        ManualStatusHandling = ManualHandlingStatusCodes
    ]),
    Parts = Json.Document(Response)
in 
    // 处理错误响应
    if (Parts[error]? <> null) then 
        error Error.Record(Parts[error], Parts[message]?) 
    else 
        Parts;

// 修正Refresh函数，让PQ能自动刷新过期令牌
Refresh = (resourceUrl, refreshToken) => TokenMethod("refresh_token", refreshToken);

Clio.Icons = [
    Icon16 = { Extension.Contents("Clio16.png"), Extension.Contents("Clio20.png"), Extension.Contents("Clio24.png"), Extension.Contents("Clio32.png") },
    Icon32 = { Extension.Contents("Clio32.png"), Extension.Contents("Clio40.png"), Extension.Contents("Clio48.png"), Extension.Contents("Clio64.png") }
];

关键调整点： #

• Added Refresh to the OAuth configuration in the Clio data source kind, so Power Query automatically uses it to renew tokens when they expire
• Restructured TokenMethod to accept grantType and the corresponding token value (code or refresh token)
• Ensured the redirect_uri is included in the authorization code request (required by Clio's API)
• Fixed the StartLogin function to return the full authorization URL instead of the Clio homepage

2. 动态加载Client ID和Client Secret（避免硬编码） #

Hardcoding credentials is a security risk and makes maintenance harder. Here are the most reliable methods to load them dynamically:

方案1：使用扩展资源文件（.resx） #

This is the standard approach for Power Query connectors.

1. Add a Resources.resx file to your connector project
2. Add two key-value pairs in the file:
   • ClientId: Your Clio Client ID
   • ClientSecret: Your Clio Client Secret
3. Replace the hardcoded credentials with:

   client_id_code = Extension.LoadString("ClientId"),
   client_secret_code = Extension.LoadString("ClientSecret"),
   

方案2：使用环境变量 #

Great if you need to share the connector across machines without modifying files:

1. Set system environment variables:
   • CLIO_CLIENT_ID = Your Client ID
   • CLIO_CLIENT_SECRET = Your Client Secret
2. Load them in your code:

   client_id_code = Environment.GetVariable("CLIO_CLIENT_ID"),
   client_secret_code = Environment.GetVariable("CLIO_CLIENT_SECRET"),
   

方案3：加载本地配置文件（仅限 Desktop use） #

If you need to store credentials in an external JSON file (note: this won't work in Power BI Service):

1. Create a ClioConfig.json file with:

   {"ClientId": "your-client-id", "ClientSecret": "your-client-secret"}
   

2. Load it in your code:

   Config = Json.Document(File.Contents("C:\Path\To\ClioConfig.json")),
   client_id_code = Config[ClientId],
   client_secret_code = Config[ClientSecret],
   

内容的提问来源于stack exchange，提问作者user2753415