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

This post documents the implementation of a dynamic tenant portal for a residential property at 3028 51st Street, San Diego (92105). The system needed to handle variable utility costs, rent credits based on shared solar generation, receipt management with approval workflows, and provide tenants with transparent visibility into their charges while maintaining an administrative backend for property management.

Project Overview and Requirements

The core challenge was creating a system where:

• Monthly utility costs (electric, gas, water, internet) vary and need to be tracked against fixed solar credits
• Tenants can view detailed bills but shouldn't see raw receipts until approved by the property manager
• Receipt images need both a live processing queue and an archive system for historical access
• A shared Google Sheet tracks monthly data with one tab per month
• Rent can be offset by utility credits when solar generation exceeds consumption
• The system handles three utility providers with different billing cycles and payment methods

The property has two tenants, fixed costs from solar (SDG&E net metering) and Cox internet, plus variable costs from electric, gas, and water usage that must be split and reconciled monthly.

Architecture: Static Site with JSON Data Layer

The portal lives at /Users/cb/Documents/repos/sites/dangerouscentaur/demos/3028fiftyfirststreet.92105.dangerouscentaur.com/ and is deployed to CloudFront-backed S3. This hybrid approach—static HTML with embedded JSON data files—was chosen because:

• No backend required: Property manager edits JSON files locally, commits via git, deploys via S3 sync. No database, no server logic, no authentication layer.
• Git history: Every utility adjustment, receipt approval, and payment entry is version-controlled.
• Tenant-friendly: Loads instantly, works offline, no backend latency.
• Scalable data structure: JSON queues handle pending/approved/rejected receipts without needing a document database.

Data Structure and Key Files

The system uses three primary JSON files:

data/utilities.json
{
  "rent": 3200,
  "rent_credit": 0,
  "utilities": {
    "solar": 146.35,
    "electric": 40.80,
    "gas": 87.72,
    "water": 174.87,
    "cox": 111.07
  },
  "pending_bills": {
    "electric": true,
    "gas": true,
    "water": true
  }
}

The pending_bills flags indicate which utilities are awaiting receipt uploads for the current month. When a receipt is approved, the property manager updates the corresponding utility amount and sets the flag to false.

data/receipts.json
{
  "pending": [
    {
      "id": "recv_202605_electric_001",
      "utility": "electric",
      "month": "202605",
      "filename": "SDG&E_May_2026.pdf",
      "amount": 127.45,
      "submitted_date": "2026-05-28",
      "notes": "Includes solar credit adjustment"
    }
  ],
  "approved": [...],
  "rejected": [...]
}

The receipt ID structure encodes metadata: recv_[YYYYMM]_[utility]_[sequence]. This allows the frontend to organize receipts by month and utility without additional database lookups.

data/bills.json
{
  "202603": {
    "solar": 146.35,
    "electric": 42.10,
    "gas": 91.50,
    "water": 168.23,
    "cox": 111.07,
    "rent": 3200
  },
  "202604": {...},
  "202605": {...}
}

Historical bills are immutable once a month closes. This serves as the source of truth for past charges.

Receipt Management Workflow

Receipts flow through three states:

• Pending: Property manager has photographed or scanned a utility bill. The image sits in receipts/archive/202605/ with a corresponding JSON entry in receipts.json.
• Approved: Manager has verified the bill is legitimate and the amount is recorded in utilities.json. The entry moves to the approved queue and the image is accessible to the tenant.
• Rejected: Bill was a duplicate, incorrect, or invalid. Moved to rejected queue; image is archived but not shown to tenant.

This three-queue model prevents tenant confusion (they only see approved bills) while maintaining an audit trail for the property manager.

Receipt Storage Strategy

Receipt images are organized by month in the S3 structure:

receipts/
  index.html (viewer page)
  archive/
    202605/
      SDG&E_May_2026.jpg
      SDGE_May_2026_detail.jpg
      Cox_May_2026.jpg
      SDWD_May_2026.jpg
    202604/
      ...

The receipts/index.html page is a single-page viewer that queries data/receipts.json and renders approved receipts with lazy-loaded images from CloudFront. This decouples receipt discovery from static HTML—adding new approved receipts requires only a JSON update, no HTML changes.

Stale receipts older than 12 months are moved to receipts/archive-old/YYYY/ to reduce clutter while keeping them accessible if needed for dispute resolution.

Google Sheets Integration

A shared Google Sheet was created at the property Gmail account (3028fiftyfirststreet92105@gmail.com) with the following structure:

• One tab per month: Tabs named 202605, 202606, etc.
• Columns: Utility, Amount, Pending Status, Receipt Link, Notes
• Embedded in portal: An <iframe> displays the current month's tab on the index page.
• No data duplication: The Sheet is a human-readable view; source of truth remains in data/utilities.json and data/bills.json.

Ownership was transferred from the service account to the property account using scripts/transfer-sheet-ownership.py, ensuring the tenant or property manager can edit the Sheet directly without service account credential exposure.

Rent Credit Logic

When solar generation exceeds the tenant's share of total utility costs, a rent credit is applied:

total_utilities = solar + electric + gas + water + cox
if solar > (total_utilities - solar):
  rent_credit = solar - (total_utilities - solar)
  effective_rent = rent - rent_credit
else:
  rent_credit = 0
  effective_rent = rent

The rent_credit field in utilities.json is updated monthly. This allows months with high solar generation