OData met OAuth2 in Excel Power Query

Achtergrond

OData is een standaard manier om data via een API op te vragen en te filteren, een beetje zoals zoeken in een online database. Steeds meer OData gegevensbronnen zijn echter beveiligd met OAuth2. Excel Power Query is gebouwd rond het idee dat de connector de authenticatie regelt, maar voor OAuth2 is geen connector beschikbaar in Excel. Dat komt door het token-mechanisme van OAuth2.

OAuth2 draait om tokens. Bij een token endpoint vraag je een token aan met je client ID en client secret, een soort gebruikersnaam en wachtwoord. Dat token stuur je mee naar de API waardoor je toegang krijgt tot de data. Tokens verlopen (bijv. 60 min) waarna een nieuw token aangevraagd moet worden.

Dit token mechanisme is het beste te vergelijken met armbandjes op een festival. Met je ticket (client ID en secret) haal je bij de kassa (token endpoint) je armband (token) op. Vervolgens mag je met dit armbandje overal op het festival (online database) komen.

Stap 1: OAuth2 token ophalen met Power Query

De eerste stap is een functie waarmee Power Query een token kan ophalen. Zorg dat je de volgende gegevens bij de hand hebt. Deze zijn minimaal nodig om een OAuth2 token op te halen:

• Token endpoint (bijv. https://…/Oauth/Token)
• OData base URL (bijv. https://…/odata)
• client_id
• client_secret
• scope of resource/audience (bijv. r_Odata)

Praktische tip: maak in Excel Power Query Parameters aan voor deze waarden. Dit houdt je M-code schoner.

Maak in Power Query een Lege query aan (via de ribbon Gegevens -> Gegevens ophalen -> Uit andere bronnen). De Power Query editor opent vanzelf. Open daarin de Geavanceerde Editor (via de ribbon Start in de Power Query editor), en plak het onderstaande M-script. Wijzig de naam (rechtboven in de editor) van deze functie naar GetAccessToken.

// GetAccessToken: haalt een OAuth2 access token op

(TokenUrl as text, ClientId as text, ClientSecret as text, Scope as text) as text =>
let
    Form = [
        grant_type    = "client_credentials",
        client_id     = ClientId,
        client_secret = ClientSecret,
        scope         = Scope
    ],

    TokenBody = Uri.BuildQueryString(Form),

    RawResponse =
        Web.Contents(
            TokenUrl,
            [
                Headers = [
                    #"Content-Type" = "application/x-www-form-urlencoded",
                    Accept = "application/json"
                ],
                Content = Text.ToBinary(TokenBody),
                ManualStatusHandling = {400, 401, 403, 404, 429, 500}
            ]
        ),

    Meta = Value.Metadata(RawResponse),
    Status = try Meta[Response.Status] otherwise 200,
    Json = try Json.Document(RawResponse) otherwise null,

    AccessToken =
        if Status >= 200 and Status < 300 then
            try Json[access_token]
            otherwise error "Token response bevat geen veld 'access_token'."
        else
            error Error.Record(
                "OAuth2 token error",
                "Token endpoint returned HTTP " & Text.From(Status),
                Json
            )
in
    AccessToken

Stap 2: Odata ophalen met je token

Nu kunnen we de GetAccessToken functie gebruiken om met een token data op te halen via OData. Maak opnieuw in Power Query een lege query aan. Open weer de Geavanceerde Editor en plak dit M-script. Dit script dient als een basis, en kan naar eigen wens worden aangepast. Zo is het mogelijk om een ODataQueryParameter aan te maken met daarin query opties om data mee te filteren.

let
    // Vervang deze door parameters of vaste waarden
    TokenUrl = TokenUrlParameter,
    ClientId = ClientIdParameter,
    ClientSecret = ClientSecretParameter,
    Scope = ScopeParameter,
 
    // OData service root,
    ODataBaseUrl = ODataBaseUrlParameter,

    // Entityset binnen de OData service, bijv. "Customers"
    EntityPath   = EntityPathParameter,

    // (Optioneel) OData query-opties, bijv. [$select="Id,Name"]
    ODataQuery   = try ODataQueryParameter otherwise null,

    // Token ophalen
    AccessToken = GetAccessToken(TokenUrl, ClientId, ClientSecret, Scope),

    // Headers opbouwen
    AuthHeaders = [
        Authorization = "Bearer " & AccessToken,
        Accept        = "application/json"
    ],

    RawResponse =
        Web.Contents(
            ODataBaseUrl,
            [
                RelativePath = EntityPath,
                Headers      = AuthHeaders,
                Query =
                    if ODataQuery <> null then
                        ODataQuery
                    else
                        null,
                ManualStatusHandling = {400, 401, 403, 404, 429}
            ]
        ),

    Meta   = Value.Metadata(RawResponse),
    Status = try Meta[Response.Status] otherwise 200,

    Json =
        if Status >= 200 and Status < 300 then
            try Json.Document(RawResponse)
            otherwise error "Response is geen geldige JSON."
        else
            let
                ErrJson = try Json.Document(RawResponse) otherwise null
            in
                error Error.Record(
                    "OData request error",
                    "OData endpoint returned HTTP " & Text.From(Status),
                    ErrJson
                ),

    Rows =
        if Record.HasFields(Json, "value") then
            Json[value]
        else if Record.HasFields(Json, "d")
            and Value.Is(Json[d], type record)
            and Record.HasFields(Json[d], "results") then
            Json[d][results]
        else
            error "Unexpected response structure: no 'value'.",

    Result =
        if Value.Is(Rows, type list)
            and List.Count(Rows) > 0 then
            Table.FromRecords(Rows)
        else
            #table({}, {})
in
    Result

Stap 3: Token hergebruiken

Een veelgemaakte fout is om in elke query een nieuw token op te halen. Dat is traag en kan je token endpoint belasten. Daarom is het handig om een losse query te maken die alleen een token ophaalt. Andere query’s kunnen hieraan refereren zodat hetzelfde token meerdere keren wordt gebruikt. Zie het M-script hieronder. Noem deze query GetToken.

let
    AccessToken =
        GetAccessToken(
            TokenUrlParameter,
            ClientIdParameter,
            ClientSecretParameter,
            ScopeParameter
        )
in
    AccessToken

In je andere queries kun je vervolgens zo een token inladen:

TokenValue = GetToken, // verwijst naar query "GetToken"

Deze opzet is ook heel nuttig omdat veel OData-services niet in één keer alle resultaten aanleveren, maar deze pagineren. Je krijgt dan per response bijvoorbeeld de eerste 1.000 rijen terug, plus een next page link die je naar de volgende batch wijst. In OData v4 heet die meestal: @odata.nextLink. Volg deze link om opeenvolgende pagina’s binnen te halen totdat er geen link meer is.

Hiervoor geldt dat je de @odata.nextLink altijs “as-is” moet gebruiken. Let ook op dat sommige APIs next links alleen teruggeven als je een server-side page size overschrijdt. Je moet dan bewust over de resultaten-per-pagina limiet gaan in je request.

Troubleshooting

Hieronder staan de fouten die vaak voorkomen, en wat ze betekenen. Omdat het basis M-script van stap 2 ManualStatusHandling gebruikt zijn de statuscodes en error-body duidelijk inzichtelijk.

• 401 Unauthorized: token ontbreekt / verkeerd meegestuurd, het token is verlopen, of de audience/resource is verkeerd.
• 403 Forbidden: token is geldig, maar je app/user heeft geen rechten.
• invalid_client: client_id en/of secret zijn fout.
• invalid_scope: scope verkeerd (of resource i.p.v. scope verwacht).
• 429 Too Many Requests: te veel token aanvragen of OData calls.

Conclusie

Met deze aanpak kun je in Excel Power Query tóch met OAuth2-beveiligde OData-bronnen werken: je haalt een access token op, gebruikt dat als header in OData.Feed(), en hergebruikt het token zodat je niet onnodig vaak opnieuw authenticatie doet. Zie dit wel als een praktische workaround voor prototypes en korte trajecten—voor robuuste, gedeelde of productie-oplossingen blijft een connector- of platformoplossing de veiligste en meest onderhoudbare route.

Hulp nodig bij het veilig inrichten van OAuth2/OData of het productierijp maken van deze oplossing? Neem gerust contact op—dan kijken we mee naar een robuuste aanpak die past bij jullie omgeving.