Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Error while "Refreshing" custom connector in Power BI Service

Hi, I made a custom connector for QuickBooks Online(Because Power BI default connector was not returning values from "Invoice" and Invoice related tables). I am able to get the data and publish in Power BI Desktop. I have included it in the enterprise gateway and able to see my report in Power BI Service, but on refresh from service I am recieving this error.
Underlying Error Message: "Value cannot be null: Parameter name: access Token Table: Invoice"

DM_ErrorDetailNameCode_UnderlyingHResult: -2147467261
I am pasting my OAuth Codes for reference.

// Data Source Kind description
PBIServiceRefresh = [
    TestConnection = (dataSourcePath) => { "PBIServiceRefresh.Contents" },
    Authentication = [
        OAuth = [
            StartLogin = StartLogin,
            FinishLogin = FinishLogin,
            Refresh = Refresh,
            Logout = Logout
        ]
    ],
    Label = "QBO Invoice Connector"    
];
// StartLogin function definition
StartLogin = (resourceUrl, state, display) =>
    let
        authorizeUrl = authorize_uri & Uri.BuildQueryString([
            client_id = client_id,  
            redirect_uri = redirect_uri,
            state = state,
            scope = "com.intuit.quickbooks.accounting",
            response_type = "code",
            response_mode = "query",
            login = "login"    
        ])
    in
        [
            LoginUri = authorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = 720,
            WindowWidth = 1024,
            Context = null
        ];
// FinishLogin function definition
FinishLogin = (context, callbackUri, state) =>
    let
        parts = Uri.Parts(callbackUri)[Query],
        result = if (Record.HasFields(parts, {"error", "error_description"})) then 
                    error Error.Record(parts[error], parts[error_description], parts)
                 else
                    TokenMethod("authorization_code", "code", parts[code])
    in
        result;
// Refresh and Logout functions
Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);
Logout = (token) => logout_uri;
// TokenMethod definition
TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            grant_type = "authorization_code",
            redirect_uri = redirect_uri,
            client_id = client_id,
            client_secret = client_secret
        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),
        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Content-type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
            ],
            ManualStatusHandling = {400} 
        ]),
        body = Json.Document(tokenResponse),
        result = if (Record.HasFields(body, {"error", "error_description"})) then 
                    error Error.Record(body[error], body[error_description], body)
                 else
                    body
    in
        result;

Using this connector I am able to refresh in PowerBI Desktop.
Can anyone tell me what could be the problem?

Thanks in advance.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Your error is that you did not give your client offline access inside your scope

     

    According to microsoft document 

     

    https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-permissions-and-consent

     

     

    You need to grant offline_access to your scope so that it can recieve refresh tokens. So update your GetScopeString below

    GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
    let
    prefix = Value.IfNull(scopePrefix, ""),
    addPrefix = List.Transform(scopes, each prefix & _),
    asText = Text.Combine(addPrefix, " "),
    finalText = Text.Combine({"offline_access", asText}, " ")
    in
    finalText;

     

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support

    Hi Anonymous 

    First check if you use the On-premises data gateway and your On-premises data gateway is July 2018 version.

     

    Second please follow this guide step by step

    https://docs.microsoft.com/en-us/power-bi/service-gateway-custom-connectors

     

    Lastly, please pay attention to the consideration:

    • For custom connectors to work with the On-premises data gateway, they need to implement a “TestConnection” section in the custom connector’s code. This is not required when using custom connectors with Power BI Desktop. You can have one that works with the Desktop, but not with the gateway for this reason. Please refer to this documentation on how to implement a TestConnection section.

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.