Every year, libraries undergo a crucial process called stock verification—ensuring that the books physically available on the shelves match the records in the library management system. This blog post outlines a simple, yet effective method for performing stock verification using Koha ILS and Google Sheets, making the process smooth, digital, and continuation friendly.
Why Not Use Koha’s Built-in Inventory Tool?
Koha does offer an Inventory tool under the Tools module, but it comes with a few practical limitations:
- You can only upload up to 1000 barcodes at a time.
- It doesn’t provide a straightforward option to download a list of missing books as CSV.
- Comparing scanned barcodes with existing records is not as flexible as spreadsheet-based methods.
- For large libraries, doing stock verification in batches of 1000 can be tedious and error prone.
That’s why we recommend a Google Sheets-based workflow—more flexible, scalable, and collaborative.
Step 1: Extract List of All Available Items from Koha
To begin, we need a list of all item barcodes except those currently checked out.
Use the following SQL query from Koha's report module:
SELECT i.barcode, i.itemcallnumber
FROM items AS i
LEFT JOIN issues AS iss ON i.itemnumber = iss.itemnumber
WHERE iss.itemnumber IS NULL;
For a Specific Branch Library
If your library system has multiple branches and you want data specific to your branch, use:
SELECT i.barcode, i.itemcallnumber
FROM items i
LEFT JOIN issues iss ON i.itemnumber = iss.itemnumber
WHERE iss.itemnumber IS NULL
AND i.homebranch = 'EC';
This query fetches all barcodes and call numbers of items not currently issued and belonging to the 'EC' branch.
Step 2: Set Up Google Sheet for Scanning
Go to Google Sheets and create a new sheet with the file name: stock_verification_2025
In Column A of Sheet1, start scanning barcodes/accession numbers of books as you pick them up from the shelf, No header needed.
Google Sheets automatically saves—no worries about data loss if you pause and resume later.
Rename Sheet1 to: From Shelf
Step 3: Prepare Data from Koha
Create a new sheet in the same file with the name: From Koha
Move this sheet before from_shelf, so the order looks like:
- From Koha
- From Shelf
Paste the Koha-exported data (barcode and itemcallnumber columns) into From Koha. No headers, just clean data:
- Column A: barcode
- Column B: itemcallnumber
Step 4: Compare Shelf vs Koha Data
In Column C of From Koha, paste the following formula in cell C1:
=VLOOKUP(A1,'From Shelf'!A:A,1,FALSE)
- Drag this formula down to apply it for all rows.
- What it does: Checks if a Koha barcode is present in the From Shelf scanned list.
Now sort the entire From Koha sheet by Column C. The rows with #N/A in Column C indicate items that are missing from the shelf.
Step 5: Final Touch – Go to Shelf with Call Numbers
- You now have a clean, printable list of missing barcodes and their corresponding item call numbers.
- Print this list and use it to physically verify whether those items are truly missing or misplaced.
Watch Video
