Low stock and expiry alerts for your pharmacy

Every morning at 09:00, the inventory tab of your spreadsheet is read and each medicine is checked two ways: its quantity against its reorder level, and its expiry date against today. Everything at or below the reorder level, already expired, or expiring within your warning window goes into one email to the pharmacist, split into what is new today and what is still outstanding from earlier runs.

Each alert is then written into the sheet's Alert Status column with the date it was first raised, and cleared again once the medicine has been restocked or replaced, so the sheet always shows the current state. Only that one cell of a row is written. A row whose quantity or expiry date cannot be read is listed in the same email rather than skipped, and a blank tab or a missing column stops the run with a message saying what to fix. When every medicine is stocked and in date, nothing is sent.

Before the first run you need a spreadsheet tab with a header row naming the medicine, quantity and expiry date columns, a column headed Alert Status, an optional Reorder Level column, and the pharmacist's email address.

Run every day at 09:00.Run every day at 09:00.

Set inventorySheetUrl to blank.Set inventorySheetUrl to blank.

Set inventoryTab to Inventory.Set inventoryTab to Inventory.

Set pharmacistEmail to blank.Set pharmacistEmail to blank.

Set lowStockThreshold to 10.Set lowStockThreshold to 10.

Set expiryWarningDays to 30.Set expiryWarningDays to 30.

Set datesAreDayFirst to false.Set datesAreDayFirst to false.

Google SheetsRead the rows of inventorySheetUrl. Save it as inventory.Read the rows of inventorySheetUrl. Save it as inventory.

Set rows to inventory.rows.Set rows to inventory.rows.

Set headers to inventory.headers.Set headers to inventory.headers.

If rows is emptyIf rows is empty

Yes

Stop and report a problem: The tab '@inventoryTab' has no medicines under its header row. Check the tab name in the inventoryTab step and that the inventory starts on row 2, then run it again.Stop and report a problem: The tab '@inventoryTab' has no medicines under its header row. Check the tab name in the inventoryTab step and that the inventory starts on row 2, then run it again.

No

No steps

Run Python code on rows, headers, today, low_stock_threshold, expiry_warning_days, and dates_are_day_first and save it as analysis.Run Python code on rows, headers, today, low_stock_threshold, expiry_warning_days, and dates_are_day_first and save it as analysis.

If analysis.error isn't emptyIf analysis.error isn't empty

Yes

Stop and report a problem: analysis.error.Stop and report a problem: analysis.error.

No

No steps

If analysis.has_alerts is yesIf analysis.has_alerts is yes

Yes

GmailSend an email to pharmacistEmail.Send an email to pharmacistEmail. Subject: analysis.email_subjectanalysis.email_body

No

No steps

Set failedUpdates to an empty list.Set failedUpdates to an empty list.

For each update in analysis.updates:For each update in analysis.updates:

If a step below fails, save the error as writeFailedIf a step below fails, save the error as writeFailed

Try

Google SheetsOverwrite row update.row_number of inventoryTab in inventorySheetUrl.Overwrite row update.row_number of inventoryTab in inventorySheetUrl.

On error

Add row update.row_number (update.medicine): writeFailed.message to the end of failedUpdates and save it as failedUpdates.Add row update.row_number (update.medicine): writeFailed.message to the end of failedUpdates and save it as failedUpdates.

If failedUpdates isn't emptyIf failedUpdates isn't empty

Yes

Stop and report a problem: The Alert Status column could not be updated for failedUpdates joined by ; . Any alert email due today was already sent; these medicines will be reported as new again on the next run.Stop and report a problem: The Alert Status column could not be updated for failedUpdates joined by ; . Any alert email due today was already sent; these medicines will be reported as new again on the next run.

No

No steps

If analysis.has_alerts is yesIf analysis.has_alerts is yes

Yes

Finish the run: Sent '@analysis.email_subject' to pharmacistEmail and updated the Alert Status of the length of analysis.updates rows.Finish the run: Sent '@analysis.email_subject' to pharmacistEmail and updated the Alert Status of the length of analysis.updates rows.

No

Finish the run: All medicines checked (analysis.checked) are stocked and in date. Nothing was sent.Finish the run: All medicines checked (analysis.checked) are stocked and in date. Nothing was sent.