Automate Expense Reporting: OCR Receipts to Google Sheets

Alright, let’s talk about a real time-sink: expense reports. I used to spend the last Friday of every month sorting through a pile of crumpled receipts, squinting at faded ink, and manually typing everything into a spreadsheet. It was easily a two-hour job I dreaded. Then I realized, as a DevOps engineer, I’m supposed to automate myself out of tedious work. This little project was born out of that frustration, and it’s saved me countless hours since.

This guide will walk you through building a Python script that watches a folder for new receipt images, uses Google’s Cloud Vision API to read them, and then neatly adds the vendor, date, and total amount into a Google Sheet. Let’s get that time back.

Prerequisites

Before we start, make sure you have the following ready:

• A Google Cloud Platform (GCP) account with billing enabled.
• Python 3 installed on your machine.
• A dedicated folder where you’ll drop your receipt images.
• A bit of familiarity with Python and how APIs work.

The Guide: Step-by-Step

Step 1: Configure Google Cloud and Google Sheets

First, we need to set up the Google side of things. This part is all about permissions—giving our script the keys to the car, so to speak.

1. Create a GCP Project: Go to the Google Cloud Console, create a new project (e.g., “Receipt-Processor”), and make sure it’s selected.
2. Enable APIs: We need two APIs. In the console, search for and enable the “Cloud Vision API” and the “Google Sheets API”.
3. Create a Service Account: This is the identity our script will use. Navigate to “IAM & Admin” > “Service Accounts”. Click “Create Service Account”, give it a name (like “receipt-automation-bot”), and grant it the “Editor” role for simplicity in this tutorial. In a production environment, you’d want to create a custom role with more granular permissions.
4. Generate JSON Key: After creating the service account, click on it, go to the “Keys” tab, and select “Add Key” > “Create new key”. Choose JSON and a file will download. Treat this file like a password! Rename it to credentials.json and store it securely in your project directory.
5. Set up Google Sheets: Create a new Google Sheet. Give it a title and create headers like “Date”, “Vendor”, “Amount”, and “Source Image”. Now, look inside your credentials.json file for the client_email address. It will look something like your-bot-name@your-project-id.iam.gserviceaccount.com. Share your Google Sheet with this email address, giving it “Editor” permissions.

Step 2: Set Up Your Python Environment

I’ll skip the standard virtual environment setup since you likely have your own workflow for that. Let’s jump straight to the Python logic. You’ll need to install a few libraries. You can do this by running a pip install command for each of the following packages: google-cloud-vision, google-api-python-client, google-auth-httplib2, google-auth-oauthlib, and python-dotenv.

Next, create two files in your project directory: process_receipts.py for our script and config.env to store our configuration variables safely.

Your config.env file should look like this. The spreadsheet ID is the long string in the URL of your Google Sheet.

# config.env
SPREADSHEET_ID="YOUR_LONG_SPREADSHEET_ID_FROM_URL"
GOOGLE_APPLICATION_CREDENTIALS="credentials.json"

Step 3: The Python Script

Now for the fun part. We’ll build the script piece by piece. The goal is to make it read an image, pull out the text, find the important bits, and send them over to our sheet.

Part A: Imports and Setup

First, we’ll import the necessary libraries and load our environment variables.

import os
import re
import io
from datetime import datetime
from dotenv import load_dotenv
from google.cloud import vision
from google.oauth2 import service_account
from googleapiclient.discovery import build

load_dotenv('config.env')

SPREADSHEET_ID = os.getenv('SPREADSHEET_ID')
SERVICE_ACCOUNT_FILE = os.getenv('GOOGLE_APPLICATION_CREDENTIALS')
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

# Directories for organization
INPUT_DIR = 'receipts_to_process'
PROCESSED_DIR = 'processed_receipts'

# Ensure directories exist
os.makedirs(INPUT_DIR, exist_ok=True)
os.makedirs(PROCESSED_DIR, exist_ok=True)

Part B: The OCR Function

This function takes the path to an image file, sends it to the Vision API, and gets back all the text it can find.

def ocr_receipt(image_path):
    """Uses Google Cloud Vision to extract text from an image."""
    try:
        client = vision.ImageAnnotatorClient()
        with io.open(image_path, 'rb') as image_file:
            content = image_file.read()
        
        image = vision.Image(content=content)
        response = client.text_detection(image=image)
        texts = response.text_annotations

        if response.error.message:
            raise Exception(f'{response.error.message}')
        
        if texts:
            return texts[0].description
        return ""
    except Exception as e:
        print(f"Error during OCR for {image_path}: {e}")
        return None

Pro Tip: The Vision API response is incredibly rich, containing bounding box data for every single word. For this simple script, we’re just grabbing the full text description (texts[0].description). In a more advanced setup, you could use those coordinates to find text in specific regions of a receipt, like the top for the vendor or the bottom for the total.

Part C: Parsing the Text

This is where the magic and the madness happen. Raw text from a receipt is messy. We’ll use regular expressions (regex) to find what we need. This part will almost certainly need tweaking based on your common receipts.

def parse_receipt_text(text, filename):
    """Parses the raw OCR text to find vendor, date, and total."""
    if not text:
        return None
    
    # Simple vendor extraction (first line)
    vendor = text.split('\n')[0].strip()

    # Find the total amount using regex
    total = None
    # This regex looks for lines containing 'total', 'amount', etc. and a number
    total_match = re.search(r'(?i)(?:total|amount|due)\s+\$?(\d+\.\d{2})', text)
    if total_match:
        total = float(total_match.group(1))

    # If the first regex fails, try a simpler one just for a standalone amount
    if not total:
        amounts = re.findall(r'\$\d+\.\d{2}', text)
        if amounts:
            # Assume the largest amount is the total
            total = max([float(a.replace('$', '')) for a in amounts])

    return {
        'date': datetime.now().strftime('%Y-%m-%d'),
        'vendor': vendor,
        'amount': total,
        'filename': filename
    }

Part D: Writing to Google Sheets

This function handles authenticating with the Sheets API and appending a new row of data.

def append_to_google_sheet(data):
    """Appends a new row of data to the specified Google Sheet."""
    creds = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)
    
    service = build('sheets', 'v4', credentials=creds)
    sheet = service.spreadsheets()
    
    # The data should be a list of lists
    row_data = [
        [data['date'], data['vendor'], data['amount'], data['filename']]
    ]
    
    request = sheet.values().append(
        spreadsheetId=SPREADSHEET_ID,
        range='Sheet1!A1',
        valueInputOption='USER_ENTERED',
        insertDataOption='INSERT_ROWS',
        body={'values': row_data}
    ).execute()
    
    print(f"Successfully added row to sheet: {request}")

Part E: The Main Loop

Finally, we tie it all together. This main function will scan our input directory, process each image, and move it to a “processed” folder so we don’t run it twice.

def main():
    """Main function to process all receipts in the input directory."""
    print("Starting receipt processing...")
    
    image_files = [f for f in os.listdir(INPUT_DIR) if f.lower().endswith(('.png', '.jpg', '.jpeg'))]
    
    if not image_files:
        print("No new receipts to process.")
        return
        
    for filename in image_files:
        image_path = os.path.join(INPUT_DIR, filename)
        print(f"Processing {image_path}...")
        
        raw_text = ocr_receipt(image_path)
        
        if raw_text:
            parsed_data = parse_receipt_text(raw_text, filename)
            if parsed_data and parsed_data['amount']:
                append_to_google_sheet(parsed_data)
                # Move the file to the processed directory on success
                os.rename(image_path, os.path.join(PROCESSED_DIR, filename))
                print(f"Successfully processed and moved {filename}.")
            else:
                print(f"Could not parse a valid total from {filename}. Leaving for manual review.")
        else:
            print(f"OCR failed for {filename}.")
            
    print("Processing complete.")

if __name__ == '__main__':
    main()

Common Pitfalls

I’ve built this a few times, and here’s where I usually trip up. Hopefully, you can avoid my mistakes.

• IAM Permissions: Forgetting to enable the Vision API or Sheets API in GCP. You’ll get a nasty 403 Permission Denied error that can be a pain to debug. Double-check that both are enabled for your project.
• Sharing is Caring: Not sharing the Google Sheet with the service account’s client_email address. The script will authenticate fine but will fail when it tries to write, complaining it can’t find the sheet.
• The Regex Trap: My simple regex for finding the total is brittle. It works for 80% of my receipts but fails on ones that say “Amount Due” or have a weird layout. Don’t expect it to be perfect out of the box; you’ll need to tune it for the types of receipts you get.

Conclusion

And there you have it. A solid foundation for a system that takes a folder of images and turns it into a structured expense report. Just drop your receipt photos into the receipts_to_process folder and run the script. From here, you could trigger this script on a schedule using a simple cron job, like 0 2 * * 1 python3 process_receipts.py to run every Monday at 2 AM. Or, for a more advanced setup, you could hook it up to a cloud function that triggers whenever a new file is uploaded to a storage bucket. The goal is to get that manual work off your plate so you can focus on more important problems. Happy automating!

---

🤖 Frequently Asked Questions