Shopify inventory by location in a live spreadsheet
Add the Inventory by location table to a sheet as a data source named inventory_by_location and its 15 columns become names your formulas, pivot tables and charts use. Sheets reads it from your store each time the sheet opens or refreshes.
Formulas on this table
=data.inventory_by_location.sum(available) Total available. Adds up the Available column over every row of Inventory by location.
=data.inventory_by_location.sum(available, product, A2) Available per product. With a product in A2, the total for that one. Fill it down next to a list of them.
=list.inventory_by_location(product) Every product, once. Spills the distinct product values down the column: the row labels for the formula above.
=data.inventory_by_location.count(inventory_item_id, product, "<>") Rows with a product. Counts the rows whose Product is not empty.
=data.inventory_by_location.avg(on_hand) Average on hand. avg, min, max, count, countd (distinct) and first work the same way as sum.
Reports that use it
=data.inventory_by_location.count(variant_id, available, "<5") Low stock. Variant locations with fewer than five units available.
=data.inventory_by_location.sum(on_hand, location, A2) Units on hand per location. Physical stock per location, including units already committed to orders.
Columns
| Column | In formulas | Type |
|---|---|---|
| Inventory item ID | inventory_item_id | Text |
| SKU | sku | Text |
| Variant ID | variant_id | Text |
| Variant | variant | Text |
| Product ID | product_id | Text |
| Product | product | Text |
| Location ID | location_id | Text |
| Location | location | Text |
| Available | available | Whole number |
| On hand | on_hand | Whole number |
| Committed | committed | Whole number |
| Incoming | incoming | Whole number |
| Unit cost | unit_cost | Number |
| Tracked | tracked | Yes / no |
| Updated at | updated_at | Date and time |
Other inventory tables
Your inventory by location, live
Orders, products, customers and inventory become live tables. Sales reports, stock lists and dashboards stay current without exporting a CSV, and AI assistants such as Claude can build them for you through MCP.