CSV to Google Sheets Pipeline

A lightweight Python tool that parses CSV transaction data and pushes it into Google Sheets. The script cleans, deduplicates, and sorts transactions so you always have an up-to-date ledger in the cloud.

Features

• CSV Parsing: Reads all .csv files from a local folder.

• Cleaning & Normalization: Standardizes columns (date, amount, description, source_file).

• Deduplication: Ensures transactions aren’t uploaded twice.

• Google Sheets Integration: Appends and maintains sorted data in a live spreadsheet.

Install dependencies:

    pip install -r requirements.txt

Dependencies Include:

• numpy
• pandas
• gspread
• google-auth
• python-dotenv

Setup

1. Create a project in Google Cloud Console.

2. Enable Google Sheets API and Google Drive API

3. Download your service account credentials and save them as:

   JSON/credentials.json

   ⚠️ Don’t commit this file to GitHub. Make sure .gitignore excludes it.

4. Share your target Google Sheet with the service account’s email (found in the JSON).

5. Update csvTest.py with your sheet ID:

   sheet = client.open_by_key("<YOUR_SHEET_ID>").sheet1

6. Create a .env file in the project root and add your Google service account path:

   GOOGLE_SHEETS_KEY=/path/to/your/service_account.json

Folder Structure

project-root/
│
│── README.md
│── csvTest.py              # Main pipeline script
│── requirements.txt
│
│── .env
│── .gitignore
│
├─ Data/                   # Your CSV transaction files
│   ├─ transactions1.csv
│   └─ transactions2.csv
└─ JSON/                   # Holds service account key (ignored in git)
    └─ service_account.json

Usage

Run manually:

    python csvTest.py

Example Workflow

1. Drop new .csv files into CSVs/.

2. Run the script.

3. Transactions are appended into your Google Sheet in chronological order.

4. Duplicates are automatically ignored.

Future Improvements

• Automate running with cron (daily/weekly)
• add email or slack notifications when new transactions are added
• possibly implement plaid