Daily inventory monitor for low stock and expiry

Every morning at 09:00, the inventory tab of your spreadsheet is read and each item is checked two ways: its quantity against its reorder level, and its expiry date against today. Anything out of stock or at or below its reorder level goes into a reorder list, grouped by supplier when the tab names one and with how many to order when the tab gives a reorder quantity or target stock level; anything past its expiry date or expiring within your warning window follows in its own section. The result is one email, sent only on a morning when something needs attention.

Each alert is then written into the row's Alert Status cell with the date it was first raised, so the next email separates what is new from what is still outstanding, and the cell is cleared once the item is restocked or replaced. Nothing else in the row is touched, and a status typed in by hand is left alone. A row whose quantity or date cannot be read is listed under 'check these rows' instead of being skipped, and an empty tab or a status that cannot be written fails the run instead of passing for a quiet day.

You need Google Sheets and Gmail connected, a link to your spreadsheet, the inventory tab's name, and the address the alerts go to. The tab needs Item and Quantity columns and an empty Alert Status column; Reorder Level, Expiry Date, Reorder Qty, Target Stock, SKU and Supplier columns are optional and used when present, and the default threshold and warning window are set in two steps of the workflow.

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 alertEmail to blank.Set alertEmail 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 items under its header row. Check the tab name in the inventoryTab step and that the stock list starts on row 2, then run it again.Stop and report a problem: The tab '@inventoryTab' has no items under its header row. Check the tab name in the inventoryTab step and that the stock list starts on row 2, then run it again.

No

No steps

Get today's date and save it as today.Get today's date and save it as today.

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 alertEmail.Send an email to alertEmail. 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 SheetsSet Alert Status: update.status on row update.row_number of inventoryTab in inventorySheetUrl.Set Alert Status: update.status on row update.row_number of inventoryTab in inventorySheetUrl.

On error

Add row update.row_number (update.item): writeFailed.message to the end of failedUpdates and save it as failedUpdates.Add row update.row_number (update.item): 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 items 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 items 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 alertEmail and updated the Alert Status of the length of analysis.updates rows.Finish the run: Sent '@analysis.email_subject' to alertEmail and updated the Alert Status of the length of analysis.updates rows.

No

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