Automate Google Sheets Data Workflow

Several teams feed raw inputs into a set of shared Google Sheets, and the manual copy-paste routine that follows is slowing everyone down. The goal is to replace that entire routine with code-driven automation.

Here is what needs to happen under the hood:
• New rows entering specified sheets should be captured automatically, validated, and routed to the correct tabs or companion sheets (covering the data entry and processing pieces).
• Existing records must be updated when source data changes, and brand-new rows created whenever the incoming data represents a net-new item.
• Once the data is in its final state, summary reports should be generated on demand—ideally as refreshed dashboard tabs inside the workbook and as scheduled PDF or email digests for stakeholders.

Apps Script is the most convenient environment, but I’m open to another language or integration layer if it keeps everything inside the Google Workspace ecosystem without extra friction.

Deliverables
1. Well-commented script(s) or workflow configuration that run end-to-end with a single trigger.
2. An install/usage README explaining how to deploy, adjust sheet IDs, and modify triggers.
3. Confirmation that reports generate correctly, existing rows update in place, and new rows append exactly once.

A short loom or walkthrough video demonstrating the finished workflow working on live data will serve as final acceptance.