TL;DR:
If you're selling perishable goods on Shopify subscriptions, you need a way to aggregate scheduled fulfilments by date. By pulling Fulfilment Orders from the Shopify Admin API and importing them into Google Sheets, you can build a simple production planning dashboard.
Choosing a e-commerce platform for a subscription microbakery
I recently started a micro bakery selling bread by subscription. Obviously, I needed some software to help me do this. As an ex-software engineer, I knew the temptation to write it myself was a dangerous one. Trying to do so would likely send me down a rabbit hole, getting distracted by tech instead of bread, and in the long run I'd likely end up with something worse than using something off-the-shelf (OTS). Even if I could build something better, it would likely take weeks—if not months - valuable time when you are trying to move fast as a startup.
So I spent a couple of weeks diligently researching OTS ecommerce solutions, trying to find one that was as close-to-perfect as I could. Suffice to say this proved to be a frustrating experience. Nothing even came close to feature set or user experience I was envisioning. Many times, I threw my hands up in despair, thinking "Bugger this, I'll just write it myself". But I pushed through the frustration and kept poking and playing, until I finally settled on Shopify. It was far from perfect, but it seemed the best option with powerful customisation, extensibility and a relatively straightforward interface for both customers and merchants.
The good thing about Shopify is that even though it doesn't necessarily provide you all the features you need out of the box, it does provide an extensibility model and a thriving marketplace of 'Apps' that allow you to customize it to behave how you want. The key extension I needed was something to support my subscription model. While Shopify "understands" subscriptions, it doesn't actually provide a native way to sell them - for that you need an App.
Another painful search began, this time for a subscription app that met my needs. There are quite a few good ones out there, but one of my key constraints was price. As a startup I'm initially going to have a microscopic turnover - probably under £100 per month. Which means I absolutely cannot justify spending £30 on a subscription app (or worse - them taking a cut from every subscription). Eventually I settled on Appstle Subscriptions. It was cheap and cheerful, but it looked like it would do the job.
At this point my business was up and running! Within a week I already had 10 subscribers, with more trickling in each day. There was a niggling issue in the back of my mind though. During my research I realised that none of the subscription apps I tried (or could afford) supported a crucial feature: production planning.
The production planning problem with subscription bakeries
What I mean by "production planning" is having an easy way to work out what customers want and when, such that I could plan my baking in advance. Most subscription businesses are selling pre-packed products with a long shelf life to customers, so as soon as a subscription needs to be fulfilled (i.e. posted) they can just grab an item from the warehouse and post it. Beyond inventory control, they don't really need much advance warning. But if you are selling baked goods (or anything made on demand - sourdough in particular needs 24-36 hours of preparation time!!), it's not very useful only to be told on-the-day that a customer needs bread!
What I needed was a view that aggregated all the scheduled fulfilments for each day, so that for each bake day I would know how many loaves I needed to prepare.
Yet no such view exists in Shopify or Appstle or anything else that I tried (including various Shopify reporting Apps). And while Shopify does provide a basic display of scheduled fulfilments it ONLY displays the fulfilments for each customer. There is NO aggregated view. Not only that but there isn't even a way to export the data. Yes, you can export orders, but the objects specific to prepaid subscriptions that I need to access are things called Fulfilment Orders. These are what you see in the admin UI but frustratingly cannot be exported.
I figured there had to be a way to get this data out. And once I had it, I would be free build my own solution (yay - finally writing some code). And building my own solution would finally mean I get exactly what I need and without paying someone else.
Initially it took me a while to understand the Shopify data model, specifically the subscriptions. As a newbie I didn't even understand the various APIs that Shopify offered. Eventually I realised the Admin API was what I needed as that dealt with "back office" stuff. The Subscriptions API initially seemed quite confusing, partly because of what I mentioned before: Shopify only provide an interface - only the App vendors provide the implementations. It was the App vendors themselves who would store the data about subscriptions NOT Shopify.
Digging deeper into Shopify docs I come across something that implied I could access the subscription API, but I needed to sign up to be a Shopify Partner in order to access it.
"If you're building a solution for a single store, then build your custom app in the Partner Dashboard."
Ugh. Becoming a "Partner" would mean lots of paperwork.
On top of that there were bad signs from the API access scopes that made it seem I may only be able to read/write my "own" subscriptions. What did "own" mean in this context? Ones a custom App had created, or simply ones from my store? Would I be able to read OUT what Appstle had written IN? 😱
Using Shopify Fulfilment Orders to build a production plan
Eventually I figured out that the only data I needed (at least to solve my initial production problems) was something called the FulfillmentOrder. The API documentation around this, including the diagrams, are what finally made things "click". It seemed I could read these via the API and they are where I would finally be able to see all the SCHEDULED fulfilments which up-to-now I hadn't been able to find a way to extract.
As a side note, the Shopify AI assistant "Sidekick" proved to be quite valuable during this journey. It actually had specific Shopify knowledge, over-and-above what a general-purpose AI chat bot had. So, for example, it could answer natural language queries about my customers and orders (impressive!) as well as tell me how to use the APIs and give me sample code. One thing it did get confused about though was the first obstacle I ran into: how to authenticate with the API. Sidekick didn't seem to understand that the access token authentication method had been deprecated, and everything was moving to OAuth 2.0. This confusion cost me a few hours as I went digging around in the Dev portal trying to find things it was telling me about that simply didn't exist! Alas you can only access the GraphQL API with OAuth 2.0 anyway, so this was all a rabbit hole that cost a bit of time.
To test out the API I jumped into GitHub Codespaces, which provided me with a ready-made development environment. As a retired software developer, I no longer keep a full development environment installed — I simply can't be arsed. 😂. So Codespaces is a lifesaver. Also, it includes another AI agent called Copilot which helped me write some prototype code (in Python - a language I had never used!) to call the Shopify API. Amazingly all the code it generated works first time, and I was soon getting data back from the API.
The final stumbling block was access scopes. I eventually figured out that I needed read_merchant_managed_fulfillment_orders but every time I did a new "release" which included additional access scopes they never seemed to activate (tip: you can see the active scopes returned as part of the /admin/oauth/access_token). None of the AI agents I queried seemed to help here, until I returned to Sidekick which told me right away what the problem was: Every time you change access scopes on an App you need to uninstall and reinstall it (yes makes sense if you think about an App in real marketplace - you don't want a nefarious actor unilaterally upgrading their access rights without you granting permission).
And with that I finally could see what I needed: All my scheduled FulfilmentOrders! Yay 🎉
In the end I ditched the idea of using Python and bulding with Github workflows, because the data ended up being written to a file on GitHub and I still needed to manually copy it into a spreadsheet to visualize it. Yes, there are various options for push/pull artefacts from GitHub to Google Sheets but in the end, I decided it wasn't worth the trouble (especially for a cheap and dirty prototype). So, I ended up asking the AI to rewrite it for me as a spreadsheet script. Guess what? The code pretty much worked first time again.
Below is a sneak peak of the end game: A view that shows each day of the week I need bread, including who wants it and how much of it they ordered.
Below is a code snippet you are free to use as-is, or to inspire your own solution. The code is written as "App Script" (basically JavaScript), designed to run as part of Google Sheets. To use it you simply create a new spreadsheet and then go to the Extensions menu and select Apps Script. Paste in the code below, making a few small changes to insert your credentials, shop name and sheet IDs. Upon execution of updateSheetWithGraphQL data will appear in your chosen sheet. It's been formatted for my own needs, but it should be fairly easy to adapt to the table format you want. From there I recommend creating a pivot table as an easy way to aggregate and visualise the data.
// Insert your shop name in here
const shopName = "your-shop-name";
// Call this function from Apps Script (found in Extensions menu in Google Sheets)
function updateSheetWithGraphQL() {
// 1. Get your data (using the previous methods)
const response = getFulfilments();
// 2. Navigate to the specific array in your JSON
// Example path: response.data.repository.artifacts.nodes
const orders = response.data.orders.edges;
if (!orders || orders.length === 0) {
console.log("No data found.");
return;
}
const tableData = parseGraphQLToTable(orders);
// Add your Spreadsheet and Sheet ID here
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("<sheet ID>");
sheet.clear();
sheet.getRange(1, 1, tableData.length, tableData[0].length).setValues(tableData);
}
function parseGraphQLToTable(orders) {
const table = [];
// Add Header Row - reflecting the most granular level
table.push([
"Order Name",
"Customer Name",
"Email",
"Fulfillment Status",
"Fulfill At",
"Product Title",
"Variant",
"Remaining Qty",
"Weighted Qty",
"Weight"
]);
orders.forEach(orderEdge => {
const order = orderEdge.node;
const customer = order.customer || {};
const email = customer.defaultEmailAddress ? customer.defaultEmailAddress.emailAddress : "N/A";
// Level 2: Fulfillment Orders
order.fulfillmentOrders.edges.forEach(fOrderEdge => {
const fOrder = fOrderEdge.node;
// CONVERSION LOGIC:
// 1. Create a JS Date object from the string
// 2. If the date is valid, it will be written to the sheet as a date type
let fulfillDate = fOrder.fulfillAt ? new Date(fOrder.fulfillAt) : "N/A";
// Optional: Force the time to midnight so the pivot table groups perfectly
if (fulfillDate instanceof Date && !isNaN(fulfillDate)) {
fulfillDate.setHours(0, 0, 0, 0);
}
// Level 3: Line Items (The most granular level)
fOrder.lineItems.edges.forEach(lineItemEdge => {
const item = lineItemEdge.node;
// Create the fully denormalized row
const row = [
order.name,
customer.displayName || "No Customer",
email,
fOrder.status,
fulfillDate,
item.productTitle,
item.variantTitle,
item.remainingQuantity,
item.remainingQuantity * item.variantTitle.toLowerCase().includes("half")? 0.5 : 1.0,
item.weight.value
];
table.push(row);
});
});
});
return table;
}
/**
* Fully denormalizes JSON while preserving the original field order.
*/
function denormalizeJsonToTable(data) {
const input = Array.isArray(data) ? data : [data];
let flatRows = [];
let headers = []; // Using an array to track order instead of a Set + Sort
function process(item, currentFlatRow) {
let containsArray = false;
for (let key in item) {
let value = item[key];
let newKey = currentFlatRow.prefix ? currentFlatRow.prefix + "." + key : key;
if (value !== null && typeof value === 'object') {
if (Array.isArray(value)) {
containsArray = true;
value.forEach(child => {
let nextRowState = { ...currentFlatRow.data };
process(child, { data: nextRowState, prefix: newKey });
});
} else {
process(value, { data: currentFlatRow.data, prefix: newKey });
}
} else {
// Track header order the first time we see a key
if (!headers.includes(newKey)) {
headers.push(newKey);
}
currentFlatRow.data[newKey] = value;
}
}
if (!containsArray) {
flatRows.push({ ...currentFlatRow.data });
}
}
// Start processing
input.forEach(obj => process(obj, { data: {}, prefix: "" }));
if (flatRows.length === 0) return [["No Data"]];
// Map rows to the sequential header array
const results = [headers];
flatRows.forEach(row => {
results.push(headers.map(h => (row[h] === undefined || row[h] === null) ? "" : row[h]));
});
return results;
}
function getFulfilments() {
// Insert your credentials from https://dev.shopify.com/dashboard
const clientId = "<client ID>";
const clientSecret = "<client secret>";
const tokenUrl = `https://${shopName}.myshopify.com/admin/oauth/access_token`;
// 1. Get the Access Token
const accessToken = getAccessToken(clientId, clientSecret, tokenUrl);
// 2. Use the token in your GraphQL call
const graphqlUrl = `https://${shopName}.myshopify.com/admin/api/2026-01/graphql.json`;
const query = `
{
orders(first: 50) {
edges {
node {
name
customer {
displayName
defaultEmailAddress {
emailAddress
}
}
fulfillmentOrders(first: 12) {
edges {
node {
fulfillAt
status
lineItems(first: 50) {
edges {
node {
productTitle
variantTitle
remainingQuantity
weight { value }
}
}
}
}
}
}
}
}
pageInfo {
hasNextPage
endCursor
}
}
}
`;
return callGraphQL(graphqlUrl, accessToken, query);
}
/**
* Fetches the Access Token using Client Credentials flow
*/
function getAccessToken(id, secret, url) {
const options = {
method: "post",
headers: {
"Accept": "application/json",
},
payload: {
client_id: id,
client_secret: secret,
grant_type: "client_credentials" // Standard for machine-to-machine
}
};
const response = UrlFetchApp.fetch(url, options);
const data = JSON.parse(response.getContentText());
if (data.access_token) {
return data.access_token;
} else {
throw new Error("Failed to get token: " + response.getContentText());
}
}
/**
* Executes the GraphQL Query
*/
function callGraphQL(url, token, query) {
const options = {
method: "post",
contentType: "application/json",
headers: {
"X-Shopify-Access-Token" : token
},
payload: JSON.stringify({ query: query })
};
const response = UrlFetchApp.fetch(url, options);
return JSON.parse(response.getContentText());
}
0 comments