Hi @Marcus_Holmes 
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.