Automated Raw Materials Inventory Management with Google Sheets, Supabase, and Gmail using n8n Webhooks Description What Problem Does This Solve? 🛠️ This workflow automates raw materials inventory management for businesses, eliminating manual stock updates, delayed material issue approvals, and missed low stock alerts. It ensures real-time stock tracking, streamlined approvals, and timely notifications. Target audience: Small to medium-sized businesses, inventory managers, and n8n users familiar with Google Sheets, Supabase, and Gmail integrations. What Does It Do? 🌟 - Receives raw material data and issue requests via form submissions. - Updates stock levels in Google Sheets and Supabase. - Manages approvals for material issue requests with email notifications. - Detects low stock levels and sends alerts via Gmail. - Maintains data consistency across Google Sheets and Supabase. Key Features - Real-time stock updates from form submissions. - Automated approval process for material issuance. - Low stock detection with Gmail notifications. - Dual storage in Google Sheets and Supabase for redundancy. - Error handling for robust data validation. Setup Instructions Prerequisites - n8n Instance: Self-hosted or cloud n8n instance. - API Credentials: - Google Sheets API: Credentials from Google Cloud Console with Sheets scope, stored in n8n credentials. - Supabase API: API key and URL from Supabase project, stored in n8n credentials (do not hardcode in nodes). - Gmail API: Credentials from Google Cloud Console with Gmail scope. - Forms: A form (e.g., Google Form) to submit raw material receipts and issue requests, configured to send data to n8n webhooks. Installation Steps 1. Import the Workflow: - Copy the workflow JSON from the “Template Code” section (to be provided). - Import it into n8n via “Import from File” or “Import from URL”. 2. Configure Credentials: - Add API credentials in n8n’s Credentials section for Google Sheets, Supabase, and Gmail. - Assign credentials to respective nodes. For example: - In the Append Raw Materials node, use Google Sheets credentials: {{ $credentials.GoogleSheets }}. - In the Current Stock Update node, use Supabase credentials: {{ $credentials.Supabase }}. - In the Send Low Stock Email Alert node, use Gmail credentials. 3. Set Up Nodes: - Webhook Nodes (Receive Raw Materials Webhook, Receive Material Issue Webhook): Configure webhook URLs and link them to your form submissions. - Approval Email (Send Approval Request): Customize the HTML email template if needed. - Low Stock Alerts (Send Low Stock Email Alert, Send Low Stock Email After Issue): Configure recipient email addresses. 4. Test the Workflow: - Submit a test form for raw material receipt and verify stock updates in Google Sheets/Supabase. - Submit a material issue request, approve/reject it, and confirm stock updates and notifications. How It Works High-Level Steps 1. Receive Raw Materials: Processes form submissions for raw material receipts. 2. Update Stock: Updates stock levels in Google Sheets and Supabase. 3. Handle Issue Requests: Processes material issue requests via forms. 4. Manage Approvals: Sends approval requests and processes decisions. 5. Monitor Stock Levels: Detects low stock and sends Gmail alerts. Detailed Descriptions Detailed node descriptions are available in the sticky notes within the workflow screenshot (to be provided). Below is a summary of key actions. Node Names and Actions Raw Materials Receiving and Stock Update - Receive Raw Materials Webhook: Receives raw material data from a form submission. - Standardize Raw Material Data: Maps form data into a consistent format. - Calculate Total Price: Computes Total Price (Quantity Received Unit Price). - Append Raw Materials: Records receipt in Google Sheets. - Check Quantity Received Validity: Ensures Quantity Received is valid. - Lookup Existing Stock: Retrieves current stock for the Product ID. - Check If Product Exists: Branches based on Product ID existence. - Calculate Updated Current Stock: Adds Quantity Received to stock (True branch). - Update Current Stock: Updates stock in Google Sheets (True branch). - Retrieve Updated Stock for Check: Retrieves updated stock for low stock check. - Detect Low Stock Level: Flags if stock is below minimum. - Trigger Low Stock Alert: Triggers email if stock is low. - Send Low Stock Email Alert: Sends low stock alert via Gmail. - Add New Product to Stock: Adds new product to stock (False branch). - Current Stock Update: Updates Supabase Current Stock table. - New Row Current Stock: Inserts new product into Supabase. - Search Current Stock: Retrieves Supabase stock records. - New Record Raw: Inserts raw material record into Supabase. - Format Response: Removes duplicates from Supabase response. - Combine Stock Update Branches: Merges branches for existing/new products. Material Issue Request and Approval - Receive Material Issue Webhook: Receives issue request from a form submission. - Standardize Data: Normalizes request data and adds Approval Link. - Validate Issue Request Data: Ensures Quantity Requested is valid. - Verify Requested Quantity: Validates Product ID and Submission ID. - Append Material Request: Records request in Google Sheets. - Check Available Stock for Issue: Retrieves current stock for the request. - Prepare Approval: Checks stock sufficiency for the request. - Send Approval Request: Emails approver with Approve/Reject options. - Receive Approval Response: Captures approver’s decision via webhook. - Format Approval Response: Processes approval data with Approval Date. - Verify Approval Data: Validates the approval response. - Retrieve Issue Request Details: Retrieves original request from Google Sheets. - Process Approval Decision: Branches based on approval action. - Get Stock for Issue Update: Retrieves stock before update (Approved). - Deduct Issued Stock: Reduces stock by Approved Quantity (Approved). - Update Stock After Issue: Updates stock in Google Sheets (Approved). - Retrieve Stock After Issue: Retrieves updated stock for low stock check. - Detect Low Stock After Issue: Flags low stock after issuance. - Trigger Low Stock Alert After Issue: Triggers email if stock is low. - Send Low Stock Email After Issue: Sends low stock alert via Gmail. - Update Issue Request Status: Updates request status (Approved/Rejected). - Combine Stock Lookup Results: Merges stock lookup branches. - Create Record Issue: Inserts issue request into Supabase. - Search Stock by Product ID: Retrieves Supabase stock records. - Issues Table Update: Updates Supabase Materials Issued table. - Update Current Stock: Updates Supabase stock after issuance. - Combine Issue Lookup Branches: Merges issue lookup branches. - Search Issue by Submission ID: Retrieves Supabase issue records. Customization Tips - Expand Storage Options : Add nodes to store data in other databases (e.g., Airtable) alongside Google Sheets and Supabase. - Modify Approval Email : Update the Send Approval Request node to customize the HTML email template (e.g., adjust styling or add branding). - Alternative Notifications : Add nodes to send low stock alerts via other platforms (e.g., Slack or Telegram). - Adjust Low Stock Threshold : Modify the Detect Low Stock Level node to change the Minimum Stock Level (default: 50).!
