Excel Rest API payouts data

  • Resolved vladvalentine

    (@vladvalentine)


    3 weeks, 5 days ago

    Hello
    Trying to get a consolidated transaction list for the accountant/auditor.
    I’ve pulled all the data from /wp-json/wc/v3/orders but I’m getting error 500 from /wp-json/wc/v3/payments/deposits/

    Working power query code

    let
    // Base setup for WooCommerce REST API Orders Endpoint
    BaseUrl = "https://website.com/wp-json/wc/v3/orders",
    Key = "ck_",
    Secret = "cs_",
    // Construct authorized API request 
    // Uses standard Basic Authentication via HTTP headers
    AuthHeader = "Basic " & Binary.ToText(Text.ToBinary(Key & ":" & Secret), BinaryEncoding.Base64),

    // Send API Request (pulling a batch of up to 100 recent orders)
    Source = Json.Document(Web.Contents(BaseUrl, [
        Headers=[
            Authorization=AuthHeader,
            // FIXED: Explicitly force the server to respond with data rather than an HTML page
            #"Accept" = "application/json",
            // FIXED: Mask Excel's user-agent to prevent security firewalls from blocking it as a bot
            #"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"
        ],
        Query=[per_page="100", status="completed,processing"]
    ])),
    // Convert raw JSON list of records into a Power Query Table
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // 1. Dynamically extract every single column name present inside the JSON records
    AllColumnNames = List.Distinct(List.Combine(List.Transform(TableFromList[Column1], Record.FieldNames))),
    // 2. Expand all columns dynamically using that generated list
    ExpandedOrders = Table.ExpandRecordColumn(TableFromList, "Column1", AllColumnNames, AllColumnNames)
    in
    ExpandedOrders

    Thanks

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Support shahzeen(woo-hc)

    (@shahzeenfarooq)

    3 weeks, 5 days ago

    Hi @vladvalentine!

    I understand you’re trying to generate a consolidated transaction list for your accountant or auditor and have been able to successfully retrieve order data from the WooCommerce REST API, but you’re receiving a 500 error when accessing the /wp-json/wc/v3/payments/deposits/ endpoint.

    To assist you further, could you please share the complete error message you’re receiving? It would also be helpful if you could check WooCommerce > Status > Logs and look for any recent WooPayments-related log entries around the time the request fails.

    Additionally, can you confirm whether the 500 error occurs when accessing the endpoint directly in a browser or API client, or only when making the request through Power Query?

    With those details, we’ll be better able to understand what’s causing the error and advise on the next steps.

    Thread Starter vladvalentine

    (@vladvalentine)

    3 weeks, 5 days ago

    Hi @shahzeenfarooq

    We are using WooPayments – I got the endpoint from a Google search, so if that is wrong it’s not from a different plugin.
    Looks like the required parameter of date was required, I’ve also filtered on payment status:
    /wp-json/wc/v3/payments/deposits/?sort=date&status_is=paid

    Code for those that want it

    let
        // Base setup for WooCommerce REST API payout Endpoint
        BaseUrl = "https://website.com/wp-json/wc/v3/payments/deposits/?sort=date&status_is=paid",
        Key = "ck_***",
        Secret = "cs_***",
    // Construct authorized API request 
    // Uses standard Basic Authentication via HTTP headers
    AuthHeader = "Basic " & Binary.ToText(Text.ToBinary(Key & ":" & Secret), BinaryEncoding.Base64),

    // Send API Request (pulling a batch of up to 100 recent orders)
    Source = Json.Document(Web.Contents(BaseUrl, [
        Headers=[
            Authorization=AuthHeader,
            // FIXED: Explicitly force the server to respond with data rather than an HTML page
            #"Accept" = "application/json",
            // FIXED: Mask Excel's user-agent to prevent security firewalls from blocking it as a bot
            #"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"
        ],
        Query=[per_page="100", status="completed,processing"]
    ])),
    // Send API Request (pulling a batch of up to 100 recent orders)
    Data = Source[data],
    // Convert raw JSON list of records into a Power Query Table
    TableFromList = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // 1. Dynamically extract every single column name present inside the JSON records
    AllColumnNames = List.Distinct(List.Combine(List.Transform(TableFromList[Column1], Record.FieldNames))),
    // 2. Expand all columns dynamically using that generated list
    PaymentsList = Table.ExpandRecordColumn(TableFromList, "Column1", AllColumnNames, AllColumnNames)
    in
    PaymentsList

    Thanks

    Frank Remmy

    (@frankremmy)

    3 weeks, 5 days ago

    Hi @vladvalentine,

    Glad you got it sorted, and thanks for sharing the working code. That’ll be useful for anyone else trying to pull payout data from WooPayments into Excel.

    For reference, the full WooPayments Deposits API is documented here if you need other endpoints or parameters down the line: https://automattic.github.io/woocommerce-payments/#deposits

    If you have a moment, we’d really appreciate it if you could leave us a review here: https://wordpress.org/support/plugin/woocommerce-payments/reviews/. It means a lot to the team!

Viewing 3 replies - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.