Building a Multi-Tenant Property Management Portal: Utility Reconciliation, Receipt Management, and Google Sheets Integration

Over the past development session, we implemented a comprehensive tenant portal for 3028 51st Street that consolidates utility billing, receipt management, and payment reconciliation into a single hosted application. This post details the architecture decisions, implementation patterns, and infrastructure setup required to support monthly bill tracking with dynamic rent adjustments.

Problem Statement

The property manager needed visibility into monthly utility expenses (electric, gas, water, solar, and internet) alongside tenant rent payments. The core challenge: utility bills arrive on different schedules, some are fixed (solar and Cox), others are variable and pending, and the tenant's rent must be offset by their portion of utility credits. Manual spreadsheets were error-prone and lacked a clear audit trail. We needed a system that:

Tracks monthly utility consumption and costs across 5 different vendors
Applies utility credits to tenant rent dynamically
Stores receipts and statements with version history
Provides tenants with a read-only portal while preserving landlord audit trails
Integrates a shared Google Sheet for collaborative tracking

Architecture Overview

The solution is deployed at 3028fiftyfirststreet.92105.dangerouscentaur.com as a static site with JSON-backed data layers, hosted on S3 with CloudFront distribution. The tech stack consists of:

Frontend: Vanilla HTML/CSS/JavaScript (no build step required)
Data Storage: JSON files (data/utilities.json, data/receipts.json, data/bills.json)
Receipts: Static image assets organized by month in S3
Collaboration: Google Sheets API + embedded iframe for real-time updates
Hosting: S3 + CloudFront + Route53

Data Structure and Reconciliation Logic

The core of the system is data/utilities.json, which tracks both fixed and variable utility costs:

{
  "rent": 3200,
  "rent_credit": 0,
  "utilities": {
    "solar": { "cost": 146.35, "status": "fixed", "vendor": "Sunrun" },
    "electric": { "cost": 40.80, "status": "pending", "vendor": "SDG&E" },
    "gas": { "cost": 87.72, "status": "pending", "vendor": "SDG&E" },
    "water": { "cost": 174.87, "status": "pending", "vendor": "City of SD" },
    "cox": { "cost": 111.07, "status": "fixed", "vendor": "Cox Communications" }
  },
  "period": "202605"
}

The status field indicates whether a utility bill has been received (fixed) or is still pending. This allows the portal to display conditional UI—tenants see only finalized charges, and the property manager sees what's outstanding. The rent_credit field stores the calculated offset, which is updated once all utilities for the month are confirmed.

Receipt and Document Management

Utility statements and receipts are stored in a tiered directory structure:

/docs/electric-statement.html, /docs/gas-statement.html, /docs/water-statement.html — HTML-rendered statements for web viewing
/receipts/202605/ — Monthly folders containing receipt images (JPG/PNG)
/receipts/index.html — Receipt viewer with month/vendor filtering

Receipt images are deployed to S3 under the same CloudFront distribution, making them accessible via HTTPS at predictable paths like:

https://3028fiftyfirststreet.92105.dangerouscentaur.com/receipts/202605/IMG_4517.jpg

The receipt viewer page (/receipts/index.html) reads from data/receipts.json which maintains three queues:

{
  "pending": [],
  "approved": [],
  "rejected": [],
  "payments": []
}

This structure supports a future upload workflow where tenants can submit receipts for verification before they're added to the official record. Currently, the property manager manually places images in the monthly folder and updates the data file.

Google Sheets Integration and Ownership Transfer

To enable collaborative tracking while keeping the portal as source-of-truth, we created a Google Sheet and embedded it via iframe. The sheet contains one tab per month, with columns for each utility vendor, the corresponding receipt image URL, and reconciliation notes.

The creation and ownership transfer involved two Python scripts:

scripts/create-tenant-sheet.py — Uses the Google Sheets API to instantiate a new sheet with the property account as the initial owner
scripts/transfer-sheet-ownership.py — Transfers ownership from the service account to a dedicated property Google account, ensuring the property manager has direct control without service account credentials in their workflow

The embed code is injected into index.html as a responsive iframe, allowing the sheet to update in real-time without requiring a redeploy of the static site:

<iframe src="https://docs.google.com/spreadsheets/d/SHEET_ID/edit" 
        width="100%" height="600"></iframe>

Deployment and S3 Sync Strategy

All static assets are deployed to S3 via an automated sync process. The deployment manifest includes:

HTML files: index.html, receipts/index.html, all docs/* statements
JSON data files: data/utilities.json, data/receipts.json, data/bills.json
Receipt images: All files in receipts/202605/ and prior month folders

The S3 bucket is configured with:

Block public access disabled (CloudFront serves traffic, not direct S3)
CloudFront Origin Access Control (OAC) restricting direct bucket access
Cache invalidation on index.html and data/* to ensure fresh data within 60 seconds
Long cache TTL (1 year) on receipt images, since filenames are unique per month

Key Decisions and Tradeoffs

JSON over Database: We chose JSON files instead of DynamoDB or a traditional database because the data volume is tiny (one property, 5-6 utilities per month) and updates are infrequent. This eliminates backend infrastructure, cold starts, and query complexity. The tradeoff: no concurrent write safety, but that's acceptable since the property manager is the sole writer.

Static HTML over React/Vue: No frontend framework is needed. The portal is read-heavy for tenants and update-heavy only during month-end reconciliation. Vanilla JavaScript + fetch() is simpler, faster, and removes a build dependency.