Google Sheets Purchase Data Update

I keep all purchase records in a Google Sheet that acts as our single source of truth for customer history. Every week I export the latest sales report as an Excel file. I now need that file merged into the Sheet so that:

• brand-new buyers are added as fresh rows, and
• existing customers have their lifetime totals and last-purchase fields updated accurately.

Because these numbers drive commission payouts, strict validation is non-negotiable. The import routine must reject rows with missing customer IDs, mismatched currencies, negative quantities, or duplicate order numbers and return a clear summary of any skipped items so I can correct them.

I’m happy for you to work directly in the Google Sheet interface or to create an Apps Script / Python bridge—whichever is faster and easier to maintain. What matters is that, after I drop the weekly Excel file into Drive, I can trigger the update with a single click and see the Sheet refreshed without manual edits.

Deliverables
– A working update mechanism (formula workflow, Apps Script, or small script tied to a button).
– Embedded validation rules that enforce the checks outlined above.
– A brief, plain-language guide showing me how to run the update and interpret the validation log.

If anything is unclear about the data structure, let me know and I’ll share a redacted sample file right away.