QR Code Inventory Tracker with Google Sheets

QR Code Inventory Tracker with Google Sheets, Managing inventory doesn’t have to mean expensive software or complicated systems.

With just Google Sheets and a bit of creativity, you can set up a QR code–based inventory tracker that logs every item movement automatically.

Imagine this: instead of manually updating quantities, your team simply scans a QR label, selects an action (Check-In or Check-Out), enters a quantity, and Google Sheets does the rest — calculating live stock, flagging low inventory, and showing recent activity.

Step 1: Structure Your Google Sheet

Create a sheet named Inventory with these columns:

• ItemID
• ItemName
• Category
• Location
• Unit
• MinStock
• StartingQty
• QR Image

Then add a second sheet called Transactions with:

• Timestamp
• ItemID
• Action (Check-In / Check-Out)
• Quantity

Step 2: Generate QR Codes

Use this formula to generate QR codes directly inside Google Sheets:

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=160x160&data=" & ENCODEURL(A2))

Each QR code corresponds to an ItemID. Fill down the column to create codes for all items.

(Visual: Screenshot of a Google Sheet with QR codes in cells)

Step 3: Print & Attach Labels

Copy ItemID, ItemName, and QR Image to a separate sheet. Print them and attach to physical items or shelves.

(Visual: Example of printed QR labels on storage boxes)

Step 4: Choose Your Scanning Method

You have three options:

🔹 Option A: Google Forms (Prefilled Response QR Codes)

• Create a Google Form with fields: ItemID, Action, Quantity.
• Enable Prefill so each item’s QR code opens the form with ItemID already filled.
• Users scan → form opens → they select Action & Quantity → submit.
• Responses flow directly into your Transactions sheet.

(Visual: Mobile screenshot of a Google Form with prefilled ItemID)

🔹 Option B: Scan-to-Sheets Apps

Apps like Scan to Sheets (iOS/Android) or Barcode to Google Sheets (Android) send scans straight into your sheet.

• Map QR scan → ItemID
• Auto timestamp → Timestamp
• Action → Check-In/Out
• Quantity → entered or default

(Visual: App interface showing scan-to-sheet mapping)

🔹 Option C: AppSheet (No-Code App)

For teams needing permissions or a guided UI, AppSheet integrates barcode scanning with Google Sheets.

(Visual: AppSheet demo with camera scanning)

Step 5: Calculate Live Stock

Add a CurrentStock column in Inventory with this formula:

=G2 
+ IFERROR(SUMIFS(Transactions!D:D, Transactions!B:B, A2, Transactions!C:C, "Check-In"), 0) 
- IFERROR(SUMIFS(Transactions!D:D, Transactions!B:B, A2, Transactions!C:C, "Check-Out"), 0)

This keeps stock levels updated automatically. Apply conditional formatting to highlight items below MinStock.

(Visual: Google Sheet with red-highlighted low stock items)

Step 6: Build a Dashboard

Create a new sheet called Dashboard:

• Low Stock List:

=FILTER(Inventory!A2:I, Inventory!H2:H < Inventory!F2:F)

• Transactions by Day: Insert a Pivot Table (Rows: ItemID, Values: SUM of Quantity).
• Charts: Add a Pie Chart or Bar Chart to visualize stock movements.

(Visual: Dashboard with charts and tables)

✅ Conclusion

With this setup, you’ve built a zero-cost inventory tracker that’s:

• Easy to use (just scan and submit)
• Accessible anywhere (Google Sheets is cloud-based)
• Flexible (works for small businesses, home organization, or equipment tracking)

No more manual updates — just scan, log, and let Google Sheets do the math.