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).!