Separate imported records from operational decisions
Use a raw import area for source rows and a separate working table for reviewer decisions. Choose a stable key that identifies the same record across refreshes. Preserve source timestamps and import details. A row’s spreadsheet position is a poor identity when sorting, filtering, or new records can change its position between runs.
- ✓ Define the stable key and required fields before importing.
- ✓ Keep manual reviewer notes outside the replaceable raw-data range.
Validate before merging
Check column names, data types, duplicates, and unexpected missing values. Put invalid rows into a review queue with a specific reason. Merge valid updates using the stable key and record whether a row was added, changed, or left untouched. A failed source request should produce a visible failure state instead of replacing the working table with empty data.
- ✓ Compare counts before and after each import.
- ✓ Make retries safe and prevent concurrent runs from competing.
Close the loop with an operating record
Provide a last-successful-refresh indicator and a simple reconciliation summary. Protect formula and configuration ranges, scope permissions, and retain only the source data needed. Test a removed source column, repeated import, and interrupted execution. If several people or systems need simultaneous writes, reassess whether a database should become the record of truth.
- ✓ Assign someone to review exceptions and failed imports.
- ✓ Keep an export and recovery procedure with the project.