Contexto

Siguiendo con mantener una sola fuente de la verdad (Single Source of Truth)

Tenemos contratado Matrixify para acceder a la información de Shopify.

Problema

Implementar el acceso al API de Shopify desde Excel Power Query es complejo (por lo menos para mi).

Después de varias iteraciones llegué a conectarme a través de Rest API y GraphQL API

Pero adicionalmente lograr obtener todos los registros fue todo un reto.

GraphQL Admin API reference

Solución

A continuación el código que me sirivió para hacer la conexión.

Acceder a RestAPI:

let
    shopName = "escribir_nombre",
    accessToken = "escribir_token",  // Use the Admin API Access Token
    requestUrl = "https://" & shopName & ".myshopify.com/admin/api/2025-01/shop.json",

    headers = [
        #"X-Shopify-Access-Token" = accessToken,
        #"Content-Type" = "application/json"
    ],

    response = Web.Contents(requestUrl, [Headers = headers]),
    result = Json.Document(response),
    shop = result[shop],
    toTable = Record.ToTable(shop)
in
    toTable

Acceder con una consulta de GraphQL

let
    shopName = "escribir_nombre",
    accessToken = "escribir_token",
    requestUrl = "https://" & shopName & ".myshopify.com/admin/api/2025-01/graphql.json",

    // Updated query with proper syntax
    query = "{ orders(first: 5, reverse: true) { edges { node { currentTotalPriceSet { shopMoney { amount currencyCode } } } } } }",

     // Updated headers for proper authorization
    headers = [
        #"X-Shopify-Access-Token" = accessToken,
        #"Content-Type" = "application/json"
    ],

    body = Json.FromValue([query = query]),

    response = Web.Contents(requestUrl, [
        Headers = headers,
        Content = body
    ]),

    jsonResponse = Json.Document(response)
in
    jsonResponse

Función (fnShopifyRunQuery) para hacer más eficiente las demás consultas:

(query as text) =>
let
    shopName = "escribir_nombre",
    accessToken = "escribir_token",
    requestUrl = "https://" & shopName & ".myshopify.com/admin/api/2025-01/graphql.json",

    // Updated query with proper syntax
    queryReturn = query,

     // Updated headers for proper authorization
    headers = [
        #"X-Shopify-Access-Token" = accessToken,
        #"Content-Type" = "application/json"
    ],

    body = Json.FromValue([query = queryReturn]),

    response = Web.Contents(requestUrl, [
        Headers = headers,
        Content = body
    ]),

    jsonResponse = Json.Document(response)
in
    jsonResponse

Consulta de ejemplo para obtener muchos registros (bulk operation)

bulkOperationRunQuery – GraphQL Admin

let
    queryText = "mutation { bulkOperationRunQuery( query:"""""" { orders(query: ""created_at:>=2025-01-01 AND created_at:<2025-05-01"") { edges { node { name currentTotalPriceSet { shopMoney { amount currencyCode } } } } } } """""" ) { bulkOperation { id status } userErrors { field message } } }",
    Source = fnShopifyRunQuery(queryText),

    // Fetch the bulk operation status and result URL
    returnBulOp = fnShopifyRunQuery("{ currentBulkOperation { id status errorCode createdAt completedAt objectCount fileSize url partialDataUrl } }"),
    data = returnBulOp[data],
    currentBulkOperation = data[currentBulkOperation],

    // Extract the result URL
    resultUrl = currentBulkOperation[url],

    // Download and read the JSONL file
    resultContent = Web.Contents(resultUrl),
    jsonLines = Text.Split(Text.FromBinary(resultContent), "#(lf)"),
    parsedData = List.Transform(jsonLines, each try Json.Document(_) otherwise null),

    // Filter out null or failed parses
    validData = List.Select(parsedData, each _ <> null),
    #"Converted to Table" = Table.FromList(validData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "currentTotalPriceSet"}, {"name", "currentTotalPriceSet"}),
    #"Expanded currentTotalPriceSet" = Table.ExpandRecordColumn(#"Expanded Column1", "currentTotalPriceSet", {"shopMoney"}, {"shopMoney"}),
    #"Expanded shopMoney" = Table.ExpandRecordColumn(#"Expanded currentTotalPriceSet", "shopMoney", {"amount", "currencyCode"}, {"amount", "currencyCode"}),
    #"Sorted Rows" = Table.Sort(#"Expanded shopMoney",{{"name", Order.Ascending}})
in
    #"Sorted Rows"

Para conseguir el token de acceso seguir estas indicaciones:

Authentication

All GraphQL Admin API queries require a valid Shopify access token.

Public and custom apps created in the Partner Dashboard generate tokens using OAuth, and custom apps made in the Shopify admin are authenticated in the Shopify admin.

Include your token as a X-Shopify-Access-Token header on all API queries. Using Shopify’s supported client libraries can simplify this process.

To keep the platform secure, apps need to request specific access scopes during the install process. Only request as much data access as your app needs to work.

Learn more about getting started with authentication and building apps.

GraphQL Admin API reference

Resultado