Spreadsheet Google Drive Consolidator
Drive-to-Database: Automated Spreadsheet Consolidation Pipeline
This pipeline standardizes and validates a folder of inconsistent spreadsheets, then loads the accepted rows into SQLite. Rejected rows go to a separate quarantine table with their original values, source file, row number, and a plain-language reason. A Streamlit dashboard reports what loaded, what failed, and why.
Local mode runs without credentials against any folder of supported files. Google Drive mode accepts a folder ID and OAuth credentials, downloads the files, and passes them through the same processing stages.
Source Code
The source code is on GitHub.
Dashboard
Motivation
Small teams often collaborate through a shared Drive folder where each person maintains a separate spreadsheet. Without a shared schema, column names drift, dates appear in several formats, and notes end up in numeric cells. A manual consolidation can quietly lose rows, leaving no record of what failed.
Power BI and dedicated ETL products can solve parts of this problem, but they may add licensing or operating overhead that a small team does not need. This project keeps the spreadsheet workflow and adds a repeatable validation layer behind it.
This is a data-cleaning pipeline, not a data warehouse. Its job is to turn inconsistent source files into usable tables and account for every row it rejects.
Quarantining Rejected Rows
Every row that fails validation goes into a quarantine table with the original data unchanged, the source file and row number, and a plain-language reason. The sample files produce errors such as these:
"revenue is negative (-450.00) in row 23 of Q3_2024_sales.xlsx"
"date '2099-01-01' is out of range in row 14 of Q4_2024_sales.xlsx"
"date '2024-13-01' is not a valid date in row 3 of Q3_2024_sales.xlsx"
"quantity 'TBD' is not numeric in row 9 of Q3_2024_sales.xlsx"
"required field 'customer' is empty in row 12 of Q1_2024_sales.xlsx"
"date 'Mike please update this by Friday' is not a valid date in row 31 of Q2_2024_sales.xlsx"
The last example contains a note in a date cell. The consolidator cannot parse it, so it preserves the row and identifies the exact problem. The file owner can correct the source without guessing what the pipeline discarded.
At the end of a run the dashboard shows something like: "347 rows loaded successfully, 12 rows quarantined for review." The quarantine table is filterable by source file, so you can hand back a targeted list of fixes to whichever team member owns each file.
Design Tradeoffs
Power BI and Tableau Prep may be the right choice when a team already uses them. This project is aimed at cases that need a lightweight, queryable SQLite output and an explicit rejection trail without adding another managed platform.
SQLite fits the intended scale of a few hundred to a few thousand rows. It needs no server or credentials, produces one inspectable file, and runs immediately after cloning the project.
Validation rules live in YAML rather than Python because the whole point is reusability without code changes. Someone adapting this for their own spreadsheets should be able to open one config file, change the column names and range limits, and run the pipeline. Anyone comfortable editing a text file can adapt the rules without changing Python.
The pipeline does not guess how to fix invalid data. If a revenue cell says "see notes below," the intended value could be 50,000. The row stays in quarantine until the source owner resolves it.
The tool accepts CSV, spreadsheet, and Google Sheets exports interchangeably. Keeping it format-agnostic means anyone on the team can contribute files however they prefer, and the pipeline handles everything without a conversion step.
Before/After: What the Pipeline Does to Messy Files
The project ships with eight sample files in data/sample_files/, six spreadsheet and two CSV, designed to reflect the
kind of mess you'd actually encounter on a shared drive. Here's what the consolidator handles before the data even
reaches the validator:
| Issue in source files | How it's handled |
|---|---|
| Column called "Revenue", "Rev.", "Total Revenue" across different files | Mapped to canonical name revenue via alias dictionary |
Dates formatted as 2024-01-15, 01/15/2024, Jan 15 2024, 15-Jan-24 | Normalized to ISO 8601 (YYYY-MM-DD) |
Numbers stored as text, currency symbols like $1,200.00 | Stripped and cast to numeric, failures preserved for quarantine |
west_region_2024.xlsx has two title rows above the actual header | Header row auto-detected, title rows skipped |
| Same row appearing in two different quarterly files | Exact cross-file duplicates removed, first occurrence kept |
File has no region column but filename contains "west" | Region column injected: "West" |
Every one of those transformations gets written to the cleaning_log table: what changed, in which file, and when.
Configurable Validation Rules
Rules live in config/validation_rules.yaml. Here's the actual config for this project:
columns: revenue: type: numeric min: 0 required: true date: type: date min: "2015-01-01" max: "2026-12-31" required: true quantity: type: numeric min: 0 max: 100000 required: true region: type: text required: false sales_rep: type: text required: false customer: type: text required: false product: type: text required: false min_non_null_fields: 3 flag_non_conforming_types: true negative_revenue_allowed_files: - returns_flagged.csv
The returns_flagged.csv file contains refunds, so negative revenue is intentional. A file-level exception keeps those rows out of quarantine. This makes the business exception explicit in configuration instead of burying it in Python.
To adapt this tool to a completely different dataset, you change the YAML. No Python edits required.
Architecture
data/sample_files/ ──► consolidator.py ──► validator.py
(or Google Drive) │
▲ ┌───────┴────────┐
│ ▼ ▼
drive_connector.py clean_df quarantine_df
│ │
└──────┬─────────┘
▼
db_loader.py
┌──────┴──────┐
▼ ▼
consolidated quarantine
table table
│
cleaning_log
table
│
┌─────────────┼─────────────┐
▼ ▼ ▼
export.py report.py dashboard/app.py
(Spreadsheet out) (HTML report) (Streamlit UI)
The pipeline has clean separation between stages. consolidator.py handles all the messy standardization work
(column names, dates, numeric cleaning, duplicates) and outputs a single merged DataFrame. validator.py doesn't
touch the data. It only decides which rows are clean and which get quarantined. db_loader.py just writes to
SQLite. Each module has one job.
Each module uses functional programming patterns so transformations are easy to swap out. If you need a different
connector, say Dropbox instead of Google Drive, you replace drive_connector.py without touching anything
downstream.
Database tables:
| Table | Contents |
|---|---|
consolidated | Clean rows that passed all validation rules |
quarantine | Failed rows with original data preserved, source file, row number, and plain-English reason |
cleaning_log | Every transformation applied: column renames, date normalization, numeric cleaning, duplicate removals |
Google Drive Integration
The Drive connector is the optional layer. It authenticates via OAuth 2.0, lists all .xlsx/.xls/.csv files in
a specified folder, downloads them, and hands the local folder path to consolidator.py. The same pipeline runs as
if the files were always local. The integration code is fully readable in the repo. The credentials live in .env
and are never committed.
# Run the pipeline against a Drive folder .venv/bin/python src/consolidator.py --source gdrive --folder-id YOUR_FOLDER_ID .venv/bin/python src/db_loader.py --full .venv/bin/streamlit run dashboard/app.py
This pattern, separating the integration code (public, readable) from the credentials (private, gitignored), is a deliberate design choice. It means anyone can review exactly how the Drive connection works without needing access to your Google Cloud project.
Engineering Notes
The source_file and source_row metadata has to be attached before any cleaning happens, not after. If you tag
rows after duplicate removal and empty-row filtering, the row numbers in your quarantine table point to positions in
the cleaned DataFrame, not the original file. That makes the quarantine reasons useless for tracing back to the
source. Tagging first, cleaning second keeps every reference accurate.
The sample files are the test spec. Writing validation logic against an abstract rule list and backfilling test data afterward means you miss edge cases. Define the inputs first, then write the code that handles them.
Quick Start
git clone <repo> cd excel_consolidator python3 -m venv .venv .venv/bin/pip install -r requirements.txt .venv/bin/python src/consolidator.py --input data/sample_files/ .venv/bin/python src/db_loader.py .venv/bin/streamlit run dashboard/app.py
No credentials needed. The repo ships with eight sample files that demonstrate every edge case the pipeline handles.
Tools: Python, pandas, openpyxl, SQLite, PyYAML, Streamlit, google-api-python-client