Build an inventory dashboard in Google Sheets from Shopify CSV snapshots

Short answer: you can build a workable stock dashboard in Google Sheets in about an hour from two exports — Shopify's inventory CSV (quantities per SKU per location) and the sales-by-variant report (units sold) — stacked week over week on a History tab. Three tiles earn their space: days of cover (what's about to run dry), dark days (what was out of stock last week), and aging (what's been sitting). Everything else is decoration until those three answer to you every Monday.

The honest frame before the click-path: this dashboard is built on snapshots, so it knows what you exported, not what happened between exports. It's a weekly decision screen, not a live monitor — and that's fine, because your buying decisions happen weekly anyway. (The working reorder spreadsheet underneath is a separate build; this is the layer that goes on top.)

The data spine: one History tab

Everything on the dashboard derives from a single tab where each week's export lands as new rows. Setup (about 20 minutes):

1. Create the workbook — call it Stock Dashboard. Tab 1: Dashboard. Tab 2: History. Tab 3: Data (current).
2. Export the inventory CSV — Products → Inventory → Export, All states format, all locations (full column reference here). This gives you Handle, SKU, Location, Available, On hand per variant per location.
3. Export sales by variant — Analytics → Reports → sales by product/variant, last 30 days, units sold per SKU (report availability varies by plan; if you're below the tier, use the last-30-days variant sales from the product CSV plus order data you can pull).
4. Paste both into Data (current) on separate areas, values only — never link live, never paste formulas over data.
5. In History, build one row per SKU per week with the columns the dashboard needs:
   • Week (date of the export — type it once per refresh)
   • SKU, Product
   • Available (summed across locations)
   • Sold 30d
   • DarkFlag — a helper computed per row (Available is column D): =IF(D2=0, 1, 0)
   • FirstSeen — a date column you maintain manually: the first week a SKU appeared in your exports. This is what makes the aging tile possible.

After four weeks of Monday refreshes you have a real dataset: four snapshots of every SKU's quantity plus a rolling 30-day sales number. The dashboard reads the newest snapshot for levels and the History column for trends and dark weeks. There's no connector to babysit, no integration to break — if the refresh doesn't happen, the dashboard is simply a week stale, and the History tab tells you which week it's showing (Shopify's native reports are the alternative when you need more than weekly).

The dashboard, tile by tile

One screen, four blocks. Numbered steps are the actual clicks.

Tile 1 — Days of cover (the "what's about to run dry" block)

1. In Dashboard, cell A1: Cover < 14 days. This is the header of the block.
2. In A3, enter: =QUERY(History!A:I, "select B, C, D, I where A = date '"&TEXT(TODAY()-WEEKDAY(TODAY(),2)+1,"yyyy-mm-dd")&"' and I > 0", 1) — this pulls this week's rows using the History column map (A Week, B SKU, C Product, D Available, E Sold 30d, F DarkFlag, G FirstSeen, H Velocity, I Cover); the cover math needs velocity, so first add a Velocity column in History (H): Sold 30d ÷ 30 adjusted to divide only by in-stock weeks if a SKU was dark part of the period (dividing by 30 when the SKU was dark for 11 of them understates demand — the same correction principle our spreadsheet guide uses).
3. Add Cover in History (column I): = D ÷ H — Available divided by velocity.
4. On the dashboard, use Data → Create a filter on the QUERY result, or simpler: a second QUERY selecting where Cover < 14 order by Cover asc.
5. Select the result range → Insert → Chart → Table chart. Tables beat pie charts here: you're reading a queue of SKUs to act on, worst-first.

That table is the most valuable tile in the workbook: it's your buying queue. Sort ascending, work down.

Tile 2 — Dark days (the "what went dark" block)

This is what the snapshot method uniquely enables — a live system shows you what's out now; History shows you what was out and for how long.

1. In History, keep the DarkFlag column from setup.
2. On the dashboard, a QUERY for this week's DarkFlag = 1 rows, with the Sold 30d column beside it — a dark SKU with real recent demand is a leak; a dark SKU that wasn't selling is just an empty slot.
3. Add a weekly roll-up: =SUMIF(History!Week, thisWeek, History!DarkFlag) per week, in a small table of Week | Dark SKUs.
4. Insert → Chart → Column chart on that Week | Dark SKUs range. One column per week. The shape of this chart is your trend line: rising means your buying rhythm is losing to demand; flat at a low number means the routine works. Don't chart it as a percentage of catalog — raw counts, because the denominator shifts as you add SKUs.

The dollar value of these dark weeks is worth knowing, but it's an estimate — Shopify can't tell you what demand would have been while a shelf was empty, and neither can a spreadsheet. Keep this tile in units, and treat any per-day value you assign a dark SKU as an assumption, not a measurement.

Tile 3 — Aging (the "what's been sitting" block)

1. Aging needs the FirstSeen column from the History setup. For each current row, compute WeeksHeld in a column of its own (J, after Cover): (A2 − G2) ÷ 7 — the row's week minus FirstSeen, in weeks.
2. Band it with =IFS(J2>=26, "6mo+", J2>=13, "3-6mo", J2>=6, "6-13wk", TRUE, "fresh").
3. On the dashboard, QUERY the current week where WeeksHeld >= 13, ordered by Available × cost descending if you've pulled cost per item into the export (it rides along in the product CSV if not the inventory one).
4. Insert → Chart → Stacked column — one column per age band, stacked by stock value. A healthy catalog skews fresh; a growing "6mo+" band is cash crystallizing into an aging problem worth its own deeper report.

A note on honesty: FirstSeen is your floor, not your ceiling — a SKU you imported in February shows as "held since February" even if it sold most of it and the remaining units are old. For a weekly dashboard that's acceptable; for disposal decisions, pair it with last-sold dates from your sales data.

Tile 4 — the header row (one formula, no chart)

Across the top: total SKUs, total units, SKUs at cover < 14, dark SKUs this week, and the week date. COUNTIF and SUMIF against the current snapshot. This row exists so that a glance replaces opening five reports.

The Monday refresh routine

1. Export the inventory CSV (All states) and paste into Data (current) — values only.
2. Copy the SKU rows into History with the new week date.
3. Refresh the 30-day sales numbers from the report.
4. Scan Tile 1 (cover) top-down — those are the week's orders.
5. Glance Tile 2's trend — a bad week deserves ten minutes of why.
6. Check Tile 3 monthly rather than weekly; aging moves slowly.

At 500 SKUs the ritual runs 20–30 minutes, dominated by the two exports. The failure modes are the spreadsheet classics: someone types a number over a QUERY (re-paste from a backup copy of the formula), the export goes stale because Monday was a holiday (date-stamp every snapshot so staleness is visible), and FirstSeen lapses (set a calendar recurrence for the ritual — the dashboard is only as current as its laziest Monday).

Chart choices, so you don't over-build

• Table for the cover queue — you act row by row; charts hide the actionable cells.
• Column chart for dark SKUs by week — trend is the message.
• Stacked column for aging bands — composition is the message.
• No pie charts, no sparkline walls, no conditional-formatting fireworks. Every additional visual on a dashboard you maintain by hand is a maintenance debt. Three blocks and a header row have survived in practice; eleven tiles have not.

Common questions

Can I build an inventory dashboard in Google Sheets from Shopify? Yes — export the inventory CSV (All states format) and the sales-by-variant report, stack weekly snapshots on a History tab, and point QUERY-driven tiles at the newest week. It's snapshot-based: the dashboard knows what you last exported, so it's a weekly decision screen rather than a live monitor (Shopify Help Center export docs, accessed October 2026).

What metrics belong on a stock dashboard? Days of cover per SKU (available ÷ in-stock velocity), the count and list of SKUs that were out of stock in the period, and stock aging in weeks-held bands. Those three answer "what do I buy, what did I lose, what's stuck" — the questions the dashboard exists for. Everything else is optional until those are answered weekly.

How do I show out-of-stock history in Google Sheets? Keep one row per SKU per week on a History tab with a dark-flag column (1 when available = 0), then chart the weekly totals as a column chart. A single live export can't show out-of-stock history — only a snapshot archive can, which is why the History tab is the core of the build.

How often should I refresh an inventory dashboard? Weekly, on a fixed day — Monday works because it feeds that week's buying decisions. Every refresh is a fresh Shopify export pasted as values; the date stamp on each snapshot makes staleness visible. Daily refreshes burn time for decisions you still make weekly.

Why do my dashboard's sales numbers look too low? Because velocity computed over calendar days counts out-of-stock weeks as zero demand. Divide units sold by in-stock weeks only, or the SKUs that ran dry look slower than they are and drop off your buying queue — the most dangerous failure mode a snapshot dashboard has.

Next step: build the History tab this week — just five columns and one paste — and add tiles only after two weeks of snapshots exist. The dashboard is worth exactly what its History archive is worth, and if the Monday ritual ever feels like it's outgrowing the spreadsheet, that's the signal our spreadsheet guide's graduation rules describe.