Automated Google Sheet Tracker

I need a single Google Sheet that automatically captures and organises three data streams—Mail enquiries, Purchase orders and Inventory levels—in one combined view with clearly labelled columns. No manual entry should be required; the information must flow in through an automated process.

Here’s the flow I have in mind: new enquiries arrive in Gmail, purchase orders come from our ordering system and inventory counts live in a separate file or app. Your task is to design the column layout, connect each source using Google Apps Script, API calls, Zapier or a similar tool and ensure every new record lands in the correct row with timestamps and unique IDs.

Key points to cover
• Unified sheet structure: column headers for source, date, reference number, item, quantity, status, remarks and any other essentials you recommend.
• Automation: scripts / integrations that pull fresh data on a schedule or webhook trigger, flag duplicates and update existing rows when quantities change.
• Quality controls: conditional formatting or data-validation rules to highlight low inventory, overdue orders or unanswered enquiries.
• Light reporting: a summary section or simple dashboard charting open enquiries, pending POs and current stock.
• Documentation: brief guide explaining how the scripts work and which cells or ranges are safe to edit.

The sheet should be ready for multiple users to view simultaneously, but data entry will remain entirely automated. Let me know what access or sample data you need and your preferred method for linking the external sources.