Automize LogoFloLogixAI
Back to Blog

Automating Procurement with Google Sheets and Webhooks: A Step-by-Step Case Study

Automating Procurement with Google Sheets and Webhooks: A Step-by-Step Case Study

Automating Procurement with Google Sheets and Webhooks: A Step-by-Step Case Study

In today’s fast-paced business environment, efficiency is key. Procurement, often considered a time-consuming process, can be streamlined through automation. This case study highlights how a Bill of Materials (BOM) in Google Sheets was transformed into an automated system for generating Requests for Quotation (RFQs), complete with Google Docs, templates, and options for future expansions like PDF generation and automated emailing.

The Problem

The procurement process was manual and repetitive:

  1. A purchaser had to manually track items in a BOM (Bill of Materials) in Google Sheets.
  2. When items were marked as "Ready for RFQ," the process of generating the necessary documents for suppliers was entirely manual.
  3. This led to delays and increased the chance of errors, such as forgetting to send RFQs or including incorrect information.

The challenge was to create a seamless system that:

  • Automatically reacts when a checkbox is ticked in the BOM.
  • Generates an RFQ in a structured format.
  • Allows the purchaser to review and finalize the RFQ.
  • Offers scalability for future improvements, such as PDF generation and automatic emailing.

The Solution

To solve the problem, we designed an automation workflow that uses Google Sheets, Make.com (formerly Integromat), and Google Docs. Here's how it works:

1. BOM Monitoring with Webhooks

The BOM is maintained in Google Sheets. A checkbox column, labeled "Ready for RFQ", is added to the sheet.

  • Trigger: When a checkbox is ticked, a webhook is activated via Make.com. This ensures that any updates to the BOM are instantly detected.

2. Automated RFQ Generation

Once the webhook detects a change:

  1. The automation retrieves the relevant row data (e.g., item name, quantity, specifications) from the BOM.

  1. It uses this data to populate a Google Sheets template, creating a new Google Sheet with the necessary information for the RFQ.
  • Template Structure: The RFQ template includes placeholders for:
    • Item Name
    • Quantity
    • Specifications
    • Deadline for supplier response
    • Additional notes or custom fields

This ensures consistency and reduces the manual effort of copying and formatting data.

3. Purchaser Review

The newly created Google Sheet is shared with the purchaser:

  • The purchaser reviews the content for accuracy.
  • Any adjustments can be made directly within the Google Sheet before finalizing the RFQ.

4. Expansion Possibilities

Although the current process relies on the purchaser to send the RFQ manually, the automation can easily be expanded to include:

  1. PDF Generation: Using tools like Google Docs or PDF Generator, the finalized RFQ can be automatically converted into a professional PDF document.
  2. Email Automation: Once the PDF is generated, the automation can email it directly to suppliers. The email would include:
    • The supplier's address (pre-populated from the BOM or a supplier database).
    • The RFQ document as an attachment.
    • A standardized email body with instructions and deadlines.
  3. Status Update in BOM: After the RFQ is sent, the automation can update the BOM in Google Sheets to reflect the current status (e.g., "RFQ Sent," "Pending Response").

The Workflow in Action

Here’s what the end-to-end process looks like:

  1. Checkbox Ticked: The purchaser marks an item as “Ready for RFQ” in the BOM.
  2. Webhook Triggered: The automation detects the change and fetches the relevant row data.
  3. Template Creation: A new Google Sheet is created from a pre-designed RFQ template, populated with the BOM data.
  4. Purchaser Review: The purchaser receives the link to the newly generated Google Sheet for review.
  5. Optional Enhancements: The RFQ can be finalized as a PDF and sent directly to suppliers via email, saving even more time.

Key Benefits

  1. Time Savings: The automation eliminates repetitive tasks, allowing the procurement team to focus on strategic decisions.
  2. Error Reduction: Data is automatically pulled from the BOM, reducing the chances of mistakes in the RFQ.
  3. Scalability: The process can be expanded to include additional features like supplier notifications, tracking, and automated follow-ups.
  4. Transparency: With everything centralized in Google Sheets, it’s easy to track the status of RFQs and ensure nothing slips through the cracks.

Future Possibilities

As the procurement team grows or the number of RFQs increases, additional automation layers can be added:

  • Supplier Portals: Suppliers could submit quotations directly into a shared system.
  • Quotation Comparison: Automate the comparison of quotations to identify the most competitive option.
  • Integrated Ordering: Once a supplier is selected, automatically generate and send purchase orders.

Conclusion

This automation showcases the power of combining simple tools like Google Sheets with advanced automation platforms like Make.com. By automating the RFQ process, the procurement team reduced inefficiencies and set the foundation for further digital transformation.

Are you ready to automate your procurement process? Start today and experience the difference automation can make for your business!