Building a Multi-Tenant Utility Billing Portal with Embedded Google Sheets and S3-Backed Receipt Management

We recently completed a comprehensive rebuild of the tenant billing portal for 3028 51st Street (property code 92105) hosted at dangerouscentaur.com. This post covers the architecture decisions, infrastructure setup, and implementation patterns we used to create a scalable system for utility cost tracking, receipt management, and tenant billing reconciliation.

Problem Statement

The previous portal had several limitations:

• No centralized place to track utility receipts (electric, gas, water) or reconcile variable costs
• Rent credits and utility offsets were manual, unmaintained calculations
• Receipts lacked version control and archive organization
• No dynamic dashboard for tenant-facing billing transparency
• Monthly data lived in disparate JSON files without a collaborative editing interface

We needed a system that would let the property manager input receipts, calculate tenant offsets, and embed live billing data directly into the static site—without requiring a backend service.

Architecture Overview

The solution uses a static site with embedded Google Sheets as the source of truth for monthly billing, combined with S3 + CloudFront for receipt archival and image serving.

• Frontend: Static HTML/CSS/JS at /Users/cb/Documents/repos/sites/dangerouscentaur/demos/3028fiftyfirststreet.92105.dangerouscentaur.com/
• Data layer: JSON files (utilities.json, receipts.json, bills.json) for tenant-facing summaries; Google Sheets for collaborative property manager input
• Storage: S3 bucket for receipt images and historical archives; CloudFront for CDN distribution
• Automation: Python scripts (create-tenant-sheet.py, transfer-sheet-ownership.py) to provision and manage sheets

Data Structure and File Organization

The portal now maintains three core JSON files:

data/
  utilities.json      # Current month utility costs, rent, credits
  receipts.json       # Receipt submission queue and payment records
  bills.json          # Historical utility data (through March 2026)

The utilities.json structure includes:

{
  "rent": 3200,
  "rent_credit": 0,
  "utilities": {
    "solar": 146.35,
    "electric": 40.80,
    "gas": 87.72,
    "water": 174.87,
    "cox": 111.07
  },
  "pending_receipts": {
    "electric": false,
    "gas": false,
    "water": false
  },
  "month": "2026-05"
}

The pending_receipts flags allow the property manager to indicate which utility bills are still outstanding—this way, the tenant portal can show estimated vs. final charges.

receipts.json implements a three-queue submission workflow:

{
  "pending": [...],    # Uploaded receipts awaiting property manager review
  "approved": [...],   # Verified and archived
  "rejected": [...],   # Did not pass validation
  "payments": [...]    # Payment records linked to approved receipts
}

Receipt Management and S3 Archival

Rather than storing receipt images in version control, we implemented a tiered archival strategy:

• Upload staging: Receipts are submitted via the portal form and stored temporarily in receipts/ on the local filesystem
• S3 archival: Approved receipts are synced to an S3 bucket, organized by month: s3://property-receipts/3028-51st/202605/
• CloudFront distribution: A CloudFront distribution fronts the S3 bucket, caching receipt images with a 30-day TTL to reduce origin requests
• Metadata tracking: receipts.json maintains URLs and approval status; old receipts are never deleted, only moved to an archive prefix

This approach provides:

• Version control-friendly data (JSON only, no images in Git)
• Fast CDN delivery for tenant access
• Audit trail via the approval queue workflow
• Easy restoration if a receipt needs to be re-reviewed

Google Sheets Integration and Collaborative Editing

Rather than asking the property manager to edit JSON files manually, we created a Google Sheet as the source of truth. The sheet has one tab per month, with columns for:

• Utility type (electric, gas, water, solar, Cox)
• Amount paid
• Tenant offset percentage or fixed amount
• Receipt date and approval status
• Notes (e.g., "waiting on electric bill")

The sheet is embedded into the portal via an iframe:

<iframe src="https://docs.google.com/spreadsheets/d/SHEET_ID/edit?usp=sharing"></iframe>

Provisioning is automated via scripts/create-tenant-sheet.py, which:

1. Authenticates to the Google Sheets API using a service account credential file (credentials/financial_sheets.json)
2. Creates a new sheet with the property name and month in the title
3. Pre-populates headers and formatting
4. Returns the sheet ID and embed URL

Ownership is then transferred to the property's Gmail account via scripts/transfer-sheet-ownership.py, ensuring the property manager retains control and the service account only has collaborative access.

HTML Pages and Receipt Viewer

The portal includes several key pages:

• index.html: Main tenant dashboard showing current rent, utilities, balance, and payment status
• receipts/index.html: Receipt viewer with filters by month, type, and approval status. Displays images from the S3/CloudFront CDN
• docs/electric-statement.html, gas-statement.html, water-statement.html: Individual utility bill statements pulled from bills.json historical data

The receipt viewer dynamically renders the receipts.json queue data:

fetch('/data/receipts.json')
  .then(r => r.json())
  .then(data => {
    const approved = data.approved.map(receipt => `
      <div class="receipt-card">
        <img src="${receipt.cdn_url}" alt="${receipt.type}">
        <p>${receipt.type} - ${receipt.date}</p>
      </div>
    `);
    document.getElementById('receipts').innerHTML = approved.join