如何通过PowerQuery获取所有Stripe Checkout Session的Line Items？

Stripe API限制只能通过单个Checkout Session ID查询对应Line Items，要批量获取所有Session的Line Items，需要先拉取所有Checkout Session列表，再逐个请求每个Session的Line Items接口，最后合并数据。以下是修改后的PowerQuery代码：

注意事项 #

• 替换代码中的"sk_your_secret_key"为你的Stripe API密钥
• 代码会自动处理Stripe API的分页逻辑，确保获取完整数据
• Line Items中的产品和价格字段会被展开到最终表格中

let
    // 定义Stripe API密钥
    ApiKey = "sk_your_secret_key",
    Headers = [#"Authorization" = "Bearer " & ApiKey],

    // 获取所有Checkout Sessions（处理分页）
    GetAllSessions = (OptionalUrl as nullable text) =>
        let
            Url = if OptionalUrl = null then "https://api.stripe.com/v1/checkout/sessions" else OptionalUrl,
            Response = Json.Document(Web.Contents(Url, [Headers=Headers])),
            Data = Response[data],
            HasMore = Response[has_more],
            NextUrl = if HasMore then Response[url] else null,
            RecursiveData = if HasMore then Data & GetAllSessions(NextUrl) else Data
        in
            RecursiveData,

    // 获取所有Session数据
    AllSessions = GetAllSessions(null),
    SessionsTable = Table.FromList(AllSessions, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedSessions = Table.ExpandRecordColumn(SessionsTable, "Column1", {"id", "amount_subtotal", "amount_total", "currency", "customer", "status", "created"}, {"SessionID", "AmountSubtotal", "AmountTotal", "Currency", "CustomerID", "Status", "Created"}),

    // 定义获取单个Session的Line Items函数（处理分页）
    GetSessionLineItems = (SessionID as text) =>
        let
            GetAllLineItems = (OptionalUrl as nullable text) =>
                let
                    Url = if OptionalUrl = null then "https://api.stripe.com/v1/checkout/sessions/" & SessionID & "/line_items" else OptionalUrl,
                    Response = Json.Document(Web.Contents(Url, [Headers=Headers])),
                    Data = Response[data],
                    HasMore = Response[has_more],
                    NextUrl = if HasMore then Response[url] else null,
                    RecursiveData = if HasMore then Data & GetAllLineItems(NextUrl) else Data
                in
                    RecursiveData,
            LineItems = GetAllLineItems(null),
            LineItemsTable = if List.IsEmpty(LineItems) then Table.FromRecords({}) else Table.FromList(LineItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            ExpandedLineItems = Table.ExpandRecordColumn(LineItemsTable, "Column1", {"quantity", "price"}, {"Quantity", "Price"}),
            ExpandedPrice = Table.ExpandRecordColumn(ExpandedLineItems, "Price", {"unit_amount", "currency", "product"}, {"UnitAmount", "PriceCurrency", "ProductID"}),
            GetProductDetails = (ProductID as text) =>
                let
                    Url = "https://api.stripe.com/v1/products/" & ProductID,
                    Response = Json.Document(Web.Contents(Url, [Headers=Headers])),
                    ProductName = Response[name],
                    ProductDescription = Response[description]
                in
                    [ProductName=ProductName, ProductDescription=ProductDescription],
            AddedProductDetails = Table.AddColumn(ExpandedPrice, "ProductDetails", each GetProductDetails([ProductID])),
            ExpandedProductDetails = Table.ExpandRecordColumn(AddedProductDetails, "ProductDetails", {"ProductName", "ProductDescription"}, {"ProductName", "ProductDescription"})
        in
            ExpandedProductDetails,

    // 为每个Session添加Line Items列
    AddedLineItems = Table.AddColumn(ExpandedSessions, "LineItems", each GetSessionLineItems([SessionID])),
    // 展开Line Items并合并数据
    ExpandedLineItems = Table.ExpandTableColumn(AddedLineItems, "LineItems", {"Quantity", "UnitAmount", "PriceCurrency", "ProductID", "ProductName", "ProductDescription"}, {"Quantity", "UnitAmount", "PriceCurrency", "ProductID", "ProductName", "ProductDescription"}),
    // 转换字段类型
    ChangedType = Table.TransformColumnTypes(ExpandedLineItems, {
        {"SessionID", type text},
        {"AmountSubtotal", Int64.Type},
        {"AmountTotal", Int64.Type},
        {"Currency", type text},
        {"CustomerID", type text},
        {"Status", type text},
        {"Created", Int64.Type},
        {"Quantity", Int64.Type},
        {"UnitAmount", Int64.Type},
        {"PriceCurrency", type text},
        {"ProductID", type text},
        {"ProductName", type text},
        {"ProductDescription", type text}
    })
in
    ChangedType

代码说明 #

1. 认证处理：通过请求头传递Stripe API密钥，确保请求合法
2. 分页处理：递归调用API获取所有Session和Line Items，避免遗漏数据
3. 关联数据：将每个Session的Line Items展开，并进一步拉取产品详情（名称、描述）
4. 字段整理：只保留常用字段，可根据需求自行添加需要展开的字段

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