Best way of automating ShopifyQL query on partner stores?

GraphQL Admin API Troubleshooting
1

We have a set of partner stores that we need to collect data on each week.

At the moment this is a manual process; we have someone log in to each store, copy/paste a GraphQL query, copy/paste the results into a spreadsheet.

Obviously, this is a complete waste of human time and we should automate this.

However, I’m finding that the changes to the app model for custom apps and how they work very confusing, and there are reports that even creating a custom app for each store won’t work because of the new rules around them.

What’s the best way of automating this extremely simple and tedious task?

2

Create a single app in your Dashboard that you install into all of your partner stores. The app can then just use the shopifyqlQuery endpoint to run the queries you need. Write the result of these queries into a spreadsheet and you have everything you want in the format you want.

Simply create a small script that you manually execute (or execute periodically via a cron), which goes over all stores and generates the reports. You would have to maintain a list of applicable stores in that script, but that should not really be an issue unless you deal with tons of stores.

If you want that to be automated aswell, you can build a proper app that handles the installed/unisntalled webhooks, so you would just have to install or uninstall the app. But depending on the amount of stores and your workflow, this extra work may not be worth it.

3

You can’t install one (custom) app across multiple partner stores (unless they’re in the same organisation).

4

Hi @Marcus_Holmes :waving_hand: The good news is that automating ShopifyQL queries via the API is well-supported and the custom app route still works fine. The legacy custom app flow was retired in January, but custom apps created through the Dev Dashboard serve the same purpose.

The API side is straightforward. Your app needs the read_reports access scope, and then you query the shopifyqlQuery endpoint.

{
  shopifyqlQuery(
    query: "FROM sales SHOW total_sales, orders SINCE last_week GROUP BY day ORDER BY day DESC"
  ) {
    tableData {
      columns { name dataType }
      rows
    }
    parseErrors
  }
}

The distribution model is where it matters. Luke is right that a single custom app can only be installed across multiple stores if those stores belong to the same Shopify Plus organization (or are transfer-disabled development stores).

If your stores are in the same Plus org and your organization owns them, create one custom app in the Dev Dashboard with read_reports scope, enable multi-store installs under the Distribution settings, and install it on each store via the generated link. Then use the client credentials grant to get an access token per store. Your script loops through each store, exchanges the client ID and secret for a token, runs the ShopifyQL query, and writes the results to your spreadsheet.

If the stores are separate (not in the same org), you’d create one custom app per store in the Dev Dashboard. Each store’s owner installs the app via the generated link, which triggers the authorization code grant and gives your app an offline access token for that store. Your script stores the token per shop and iterates through them the same way. It’s a bit more setup up front, but for a handful of stores it’s manageable and avoids the overhead of building a public app.

The client credentials grant is only available for apps installed on stores your organization owns. If these are other merchants’ stores, the authorization code flow handles the install and token exchange instead. It’s a one-time interactive step per store, after which your script runs unattended with the offline token.

Could you share whether these stores are under the same Shopify organization, and whether your org owns them? That’ll confirm which path and auth flow is the right fit.

5

Thanks for the reply. Yeah, these are separate organisations that we are granted Partner access to. We can ask the owners to install an app, probably, but that can’t be a complicated process - none of our customers are technical and anything more complicated than clicking on a link and pushing a couple of buttons will cause mayhem (and even then I’d guarantee that at least one of them will need us to walk them through it).

So, just to confirm: I can create a custom app for each customer in our own Dev Dashboard, and then that creates an install link that our customers can use on their store? Or do I need access to the customer’s own Dev Dashboard to create the app on their store?

edit: I just checked and I don’t think we have access via Partner to their dev dashboard; the link just takes me to our own Dashboard. If we need to create the app in the customer’s own store, how do we do this?

6

Sorry to bug you, can I get some clarification on whether this needs to happen in the customer’s own Dev Dashboard, please?

7

Donal, please let me know what’s going on here?

8

Donal, chasing this clarification, please

9

Apologies for the delay getting back to you on this, @Marcus_Holmes

You don’t need access to the client’s Dev Dashboard at all. The mechanism you’re looking for is Custom Distribution, which you set up from your own Partner Dashboard. You create the app in your Partner Dev Dashboard, then go to the Partner Dashboard and select “Custom distribution” as the distribution method. Enter the client store’s myshopify.com domain, generate the install link, and send it to your client. They click it, authorize the app, and they’re done. That’s a two-click process on their end, so it should be manageable even for non-technical store owners.

This was covered in a similar thread here where a partner hit the same wall with the new Dev Dashboard. The accepted answer walks through it.

You’d create one app per client store, each with its own install link. Once installed, your app uses the authorization code grant to get an offline access token for that store. Your automation script stores each token and loops through the stores running the shopifyqlQuery endpoint. For scopes, the app needs read_reports and the protected customer data scopes (read_customers, read_customer_name, read_customer_address, read_customer_email, read_customer_phone). shopifyqlQuery requires all of these even for non-customer queries like FROM sales, but for custom apps they’re available without any approval process. You just declare them in your app configuration.

If the number of stores grows significantly down the road, you might want to consider building a public app with limited visibility instead, which would let you use a single app across all stores (though that route involves app review, unlike custom distribution).

Let me know if you run into any issues getting it set up.

10

Thanks Donal that’s useful :slight_smile:

I’ll let you know if we get into any problems with it