Getting ShopifyQLquery to work through VBA

[Paul112]

I'm trying to get the example cURL from the documentation to work from within VBA

I have extrapolated it to the payload:

varGraphQL = "{""query"": ""query { shopifyqlQuery(query: ""FROM orders SHOW sum(net_sales) AS monthly_net_sales GROUP BY month SINCE -3m ORDER BY month"") { __typename ... on TableResponse { tableData { unformattedData rowData columns { name dataType displayName } } } parseErrors { code message range { start { line character } end { line character } } } } }""}"

and I get the result:

{"errors":"Not Found"}

My end intention is to product a script that will extract the stock movement from the day before. Any help with this would be greatly appreciated

Thank you

[SBD_]

Hey @Paul112 

I suspect this is a HTTP error - can you confirm the endpoint you're hitting? Also double checking you're POSTing?

[Paul112]

Hi @SBD_ 

Thank you for that - it wasn't set to POST. Now the response is "Bad Request"

[SBD_]

Hey @Paul112 

Other things to check:

- Content-Type should be `application/json`.

- Be sure to set a `X-Shopify-Access-Token` header with your access token.

- Body should look like this: {"query": "{ shop { name }}"}

Maybe try a simple query (like shop name) to get it working. Also compare your code against a query with something like Insomnia.

[Paul112]

Thank you for looking into this @SBD_ 

the test body returned:

{"data":{"shop":{"name":"Mary's Lamb Chops"}},"extensions":{"cost":{"requestedQueryCost":1,"actualQueryCost":1,"throttleStatus":{"maximumAvailable":20000.0,"currentlyAvailable":19999,"restoreRate":1000.0},"fields":[{"path":["shop","name"],"definedCost":0,"requestedTotalCost":0,"requestedChildrenCost":null},{"path":["shop"],"definedCost":1,"requestedTotalCost":1,"requestedChildrenCost":0}]}}}

(shop name changed)

Below is the script with all the headers:

dim putURL as string ' - retrieved from else-where
dim APItoken as string ' - retrieved from else-where
dim APImethod as string
dim putAPItext as string

APImethod = "POST"
putAPItext = "{""query"": ""query { shopifyqlQuery(query: ""FROM orders SHOW sum(net_sales) AS monthly_net_sales GROUP BY month SINCE -3m ORDER BY month"") { __typename ... on TableResponse { tableData { unformattedData rowData columns { name dataType displayName } } } parseErrors { code message range { start { line character } end { line character } } } } }""}"

    Dim PutReQuest As MSXML2.XMLHTTP60 ' Not allowed to use serverXML
    Set PutReQuest = New XMLHTTP60

    With PutReQuest
        .Open APImethod, putURL, False
        .setRequestHeader "Accept", "text/json"
        .setRequestHeader "Content-Type", "application/json;charset=UTF-8"
        .setRequestHeader "X-Shopify-Access-Token", APItoken
        .setRequestHeader "X-GraphQL-Cost-Include-Fields", "true" 
        .send (putAPItext)
        ShopifyAPIresult = .responseText
        .abort
    End With