n8n Workflow: Pharmacy Inventory Alerts for Low Stock & Expiring Medicine

This n8n workflow helps pharmacies proactively manage their inventory by regularly checking for low stock and expiring medicine and sending email alerts. It automates the process of monitoring critical inventory levels, ensuring that pharmacists and staff are informed in a timely manner to prevent stockouts and the use of expired products.

What it does

1. Schedules Daily Check: The workflow is triggered daily by a Cron node, initiating a regular check of the inventory.
2. Retrieves Inventory Data: It connects to a Google Sheet to fetch the latest inventory data, including product names, stock levels, and expiration dates.
3. Processes Data (Placeholder): A "Code" node is included, serving as a placeholder for custom logic. This node is intended for processing the retrieved inventory data to identify items that are low in stock or nearing their expiration date.
4. Sends Email Alerts: Based on the processing logic, the workflow sends email alerts to designated recipients. These emails would typically contain details about the low-stock items or expiring medicines.
5. Includes a Wait Step (Placeholder): A "Wait" node is present, which could be used to introduce a delay in the workflow, for example, before sending a follow-up alert or performing another action.
6. Provides Documentation (Placeholder): A "Sticky Note" node is included, likely for adding inline documentation or notes within the workflow itself.

Prerequisites/Requirements

• n8n Instance: A running instance of n8n.
• Google Sheets Account: A Google Sheets account with a spreadsheet containing your pharmacy inventory data. The sheet should ideally have columns for product name, current stock, and expiration date.
• Email Service: Access to an SMTP server or an email service configured in n8n (e.g., Gmail, Outlook, SendGrid) to send alert emails.

Setup/Usage

1. Import the Workflow: Import the provided JSON into your n8n instance.
2. Configure Cron Trigger:
   • Open the "Cron" node.
   • Set the desired schedule for the inventory checks (e.g., daily at a specific time).
3. Configure Google Sheets Node:
   • Open the "Google Sheets" node.
   • Select or create a Google Sheets credential.
   • Specify the Spreadsheet ID and the Sheet Name where your inventory data is located.
   • Ensure the operation is set to "Read" and "Get All Rows" to fetch the entire inventory.
4. Implement Logic in Code Node:
   • Open the "Code" node.
   • Write JavaScript code to:
     • Access the data from the "Google Sheets" node.
     • Define your "low stock" threshold.
     • Define your "expiring soon" threshold (e.g., items expiring within 30 days).
     • Filter the inventory data to identify items that meet these criteria.
     • Format the output for the email alert.
     • Example (conceptual, requires full implementation):

       const inventory = $input.item.json.data; // Assuming data is in 'data' property
       const lowStockThreshold = 10;
       const expirationThresholdDays = 30;
       const today = new Date();
       
       const lowStockAlerts = [];
       const expiringSoonAlerts = [];
       
       for (const item of inventory) {
           // Assuming 'Stock' and 'Expiration Date' are column headers
           if (item.Stock <= lowStockThreshold) {
               lowStockAlerts.push(`${item.Product} is low in stock (${item.Stock} left).`);
           }
       
           const expirationDate = new Date(item['Expiration Date']);
           const diffTime = expirationDate.getTime() - today.getTime();
           const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
       
           if (diffDays > 0 && diffDays <= expirationThresholdDays) {
               expiringSoonAlerts.push(`${item.Product} expires in ${diffDays} days (${item['Expiration Date']}).`);
           }
       }
       
       // Combine alerts or send separately
       let emailBody = "";
       if (lowStockAlerts.length > 0) {
           emailBody += "--- Low Stock Alerts ---\n" + lowStockAlerts.join("\n") + "\n\n";
       }
       if (expiringSoonAlerts.length > 0) {
           emailBody += "--- Expiring Medicine Alerts ---\n" + expiringSoonAlerts.join("\n") + "\n\n";
       }
       
       if (emailBody === "") {
           return [{ json: { message: "No alerts needed." } }];
       } else {
           return [{ json: { subject: "Pharmacy Inventory Alerts", body: emailBody } }];
       }
       

5. Configure Send Email Node:
   • Open the "Send Email" node.
   • Select or create an email credential.
   • Set the To recipient(s) (e.g., pharmacy_manager@example.com).
   • Set the Subject using an expression to pull from the Code node's output (e.g., {{ $json.subject }}).
   • Set the Body using an expression to pull from the Code node's output (e.g., {{ $json.body }}).
   • Add conditional logic (e.g., an IF node before Send Email) if you only want to send an email when alerts are actually generated.
6. Activate the Workflow: Save and activate the workflow.