Medical Store POS & Inventory with AI – Google Apps Script

A pharmacy and medical store management system on Google Sheets, built around the thing that breaks most retail software when you point it at a chemist: packaging. Medicines here live at four levels — carton, box, packet and unit — and the conversions run automatically, so selling six packets out of a carton leaves the stock correct at every level.

What it does

At the counter you pick a medicine and choose which level you are selling in. Sell one carton and it takes forty-eight units; add a box on top and the total adjusts live. Discounts apply across the bill, payments can be split between cash and card, and loyalty points earned by a registered customer can be redeemed as part of the tender. If a medicine is flagged as prescription-required, the sale will not complete until a doctor and prescription are attached — which is the control a pharmacy actually needs rather than a note in a comment field.

Stock that survives a real chemist

Opening stock is entered with batch, manufacturing date, expiry, cost and MRP. From there you can adjust, transfer, write off damage or run an audit count across several medicines in one operation rather than one at a time. The expiry board shows what lapses in 2, 30, 60 and 90 days alongside low and out-of-stock lines. Purchases run properly too: a purchase order moves through quotation, ordered, partially received and received, with goods received notes carrying the supplier invoice and other charges.

Highlights

• AI chat assistant over your own data — "today's sales total", "which medicines are below reorder level" — using your own ChatGPT or Gemini key
• Doctors, prescriptions with refills and validity, and an uploaded prescription image
• Customers with allergies, medical notes, credit limit, membership card and loyalty points
• Accounting: expenses, other income, banks, cash book, profit and loss and balances
• Salary and payroll with advances deducted, plus a day-close routine
• Four roles — admin, manager, pharmacist, cashier — with a live permission matrix

Who it is for

Independent pharmacies and medical stores that buy in cartons, sell in strips and need expiry and batch control — not a generic retail POS with a medicine list bolted on.