Tracking fractional inventory accurately in Shopify can be crucial for businesses dealing with small quantities or perishable goods. Here’s a broad overview of the steps required to setting up precise inventory tracking using Matrixify and Google Sheets. Note: You can also accomplish the same thing by setting up a Private App which I will cover in a future blog post.
Installation and Setup:
- Start by installing Matrixify, a powerful bulk import/export app, from the Shopify App Store.
- Connect Matrixify to a Google Sheets document. Create three sheets: "Import to Shopify", "Export from Shopify", and "Precise Inventory".
Automate Exports and Imports:
- Set up automatic exports of your Shopify orders to the "Export from Shopify" sheet.
- Configure a second automatic import from the "Import to Shopify" sheet, which contains a Shopify-formatted import sheet for products you want to track precise inventory for.
Google Sheets Scripts:
In Google sheets, write two scripts:
- Script A (On Edit): This script triggers on edits and loops through each order in "Export from Shopify - Orders". It copies the order to "Import to Shopify - Products", loops through line items, and adjusts corresponding inventory in the "Precise Inventory" sheet.
- Script B (On Edit): This script also triggers on edits and loops through "Import to Shopify - Products". It matches SKUs from the "Precise Inventory" sheet and inserts revised values into the metafield column.
Script A (On Edit):
function onEditScriptA(e) {
var ss = e.source;
var editedSheet = ss.getActiveSheet();
var editedRange = e.range;
// Check if the edited sheet is "Export from Shopify - Orders"
if (editedSheet.getName() === "Export from Shopify - Orders") {
var editedRow = editedRange.getRow();
var lastColumn = editedSheet.getLastColumn();
var orderData = editedSheet.getRange(editedRow, 1, 1, lastColumn).getValues()[0];
// Get the "Import to Shopify - Products" sheet
var importSheet = ss.getSheetByName("Import to Shopify - Products");
var importLastRow = importSheet.getLastRow();
// Copy the order to "Import to Shopify - Products"
importSheet.appendRow(orderData);
// Adjust corresponding inventory in the "Precise Inventory" sheet
var preciseInventorySheet = ss.getSheetByName("Precise Inventory");
var skuColumnIndex = 1; // Assuming SKU is in the first column
// Loop through line items and adjust inventory
for (var i = 2; i <= lastColumn; i++) {
var sku = orderData[i]; // Assuming SKU is in the second row
var quantity = orderData[i]; // Assuming quantity is in the same row as SKU
// Find the row in "Precise Inventory" sheet corresponding to the SKU
var skuRange = preciseInventorySheet.getRange(2, skuColumnIndex, importLastRow - 1, 1);
var skuValues = skuRange.getValues();
for (var j = 0; j < skuValues.length; j++) {
if (skuValues[j][0] === sku) {
// Adjust inventory
preciseInventorySheet.getRange(j + 2, 2).setValue(preciseInventorySheet.getRange(j + 2, 2).getValue() - quantity);
break;
}
}
}
}
}
Script B (On Edit):
function onEditScriptB(e) {
var ss = e.source;
var editedSheet = ss.getActiveSheet();
// Check if the edited sheet is "Import to Shopify - Products"
if (editedSheet.getName() === "Import to Shopify - Products") {
var lastRow = editedSheet.getLastRow();
var lastColumn = editedSheet.getLastColumn();
// Get the "Precise Inventory" sheet
var preciseInventorySheet = ss.getSheetByName("Precise Inventory");
var preciseInventoryRange = preciseInventorySheet.getRange("A2:B"); // Assuming SKU is in column A and inventory in column B
var preciseInventoryValues = preciseInventoryRange.getValues();
// Loop through each row in "Import to Shopify - Products"
for (var i = 2; i <= lastRow; i++) {
var sku = editedSheet.getRange(i, 1).getValue(); // Assuming SKU is in the first column
var quantity = editedSheet.getRange(i, lastColumn).getValue(); // Assuming quantity is in the last column
// Loop through "Precise Inventory" sheet to find matching SKU
for (var j = 0; j < preciseInventoryValues.length; j++) {
if (preciseInventoryValues[j][0] === sku) {
// Insert revised value into metafield column
preciseInventorySheet.getRange(j + 2, 2).setValue(quantity);
break;
}
}
}
}
}
If you have foundational knowledge of Shopify, Google Scripts, and Metafields, or if you have the time and interest, it's something that you can figure out, but if you need help, contact me here.