Finance Tracker Automation — Technical Documentation

Purpose: Complete context for a developer or AI assistant to understand, recreate, or extend the Finance Tracker. Reflects the actual implemented state of the system.

System Overview
Architecture
Google Drive Setup
Google Sheets Setup
Google Apps Script
iOS Shortcuts Overview
Data Flow Details
Configuration Reference
Known Limitations & Design Decisions

1. System Overview

This system captures financial transactions from three iPhone sources and records them in a Google Spreadsheet.

Source	Shortcut	Input type
Bank SMS notification	SMS Automation (automatic)	Text — raw SMS body
Bank app screenshot	Log Transaction v2 (Share Sheet)	Image — PNG screenshot
Manual entry	Input Transaction (Home Screen icon)	Text — constructed from user prompts

For each transaction the system records:

Date and time
Type (Income or Expense)
Amount
Item name (merchant, biller, sender)
Category
Split By (how many people share the bill)
My Share (computed: Amount ÷ Split By)

2. Architecture

High-level flow

mermaid

flowchart TD
    A1["SMS Automation (auto)\nSenders: Rogers Credit Card\nTransaction / Bemn Lam\nKeyword: 'Details:'"] -->|SMS body text| D
    A2["Log Transaction v2\n(Share Sheet)\nBank app screenshot"] -->|PNG image| D
    A3["Input Transaction\n(Home Screen icon)\nAsk: amount + split + details"] -->|Constructed text| D

    D["iOS Shortcut\nBase64 encode input\nAsk split count\nPOST JSON to Web App"]

    D -->|filename, mimeType, data, splitBy| E

    E["Apps Script — doPost\nDecode base64\nEmbed splitBy in filename\nSave to Drive Inbox\nReturn immediately ~1s"]

    E --> F["Google Drive\nFinance/Inbox/"]

    G["Apps Script — processInboxFiles\n5-minute time-based trigger"]
    G -->|reads new files| F
    G -->|text or image| H["Gemini Flash API\nExtract: date, type, amount\nitem, category"]
    H -->|JSON entries| G
    G -->|one row per entry| I["Google Sheets — tracker"]
    G -->|archive| J["Google Drive\nFinance/Processed/"]

Processing mode: deferred

doPost saves the file and returns in ~1 second. The 5-minute time-based trigger handles Gemini parsing and sheet writing. Rows appear within 0–5 minutes of the shortcut completing.

Component responsibilities

Component	Role
SMS Automation	Fires on matching SMS, asks split, encodes SMS text, POSTs
Log Transaction v2	Share Sheet entry for screenshots, asks split, encodes image, POSTs
Input Transaction	Manual entry, prompts amount/split/details, builds text string, POSTs
`doPost`	Decodes base64, embeds splitBy in filename, saves to Drive, returns immediately
`processInboxFiles`	Polls inbox every 5 min, routes files to Gemini, writes rows, archives files
`analyzeWithGemini`	Sends text or image to Gemini API, parses JSON response, applies date fallback
`resolveFallbackDate`	Date fallback chain: Gemini → filename pattern → `1970-01-01`
`appendRow`	Writes one row, sets `=C/F` formula for My Share, color-codes Type cell
Google Drive	Staging inbox and processed archive
Google Sheets	Permanent transaction ledger

3. Google Drive Setup

Folder structure

My Drive/
└── Finance/
    ├── Inbox/        ← uploaded files land here
    └── Processed/    ← files archived here after processing

Steps

Create folder Finance in the root of Google Drive
Create subfolders Inbox and Processed inside Finance
Note the Folder ID of each from the browser URL: https://drive.google.com/drive/folders/FOLDER_ID_HERE
Paste both IDs into the Apps Script constants

4. Google Sheets Setup

Create the spreadsheet

Inside Finance/, create a new Google Sheet named tracker
Open it and go to Extensions → Apps Script to create a bound script
The script uses SpreadsheetApp.getActiveSpreadsheet() — it must be bound, not standalone
The header row is auto-created on the first run — no manual setup needed

Column structure

Col	Header	Type	Notes
A	Date	Text	`YYYY-MM-DD HH:MM` from Gemini or fallback
B	Type	Text	`Income` or `Expense` — color-coded
C	Amount	Number	Negative = expense, positive = income
D	Item	Text	Merchant, biller, or sender name
E	Category	Text	e.g. Groceries, Utilities, Salary
F	Split By	Number	Number of people sharing the transaction
G	My Share	Formula	`=C/F` — live, updates if C or F is edited
H	Source File	Text	Original filename from iPhone
I	Processed At	Timestamp	When the script wrote this row

5. Google Apps Script

How to open the bound script editor

Open the tracker Google Sheet → Extensions → Apps Script → paste the full script below → save.

Constants

javascript

const GEMINI_API_KEY    = "YOUR_GEMINI_API_KEY";
const GEMINI_URL        = `https://generativelanguage.googleapis.com/v1beta/models/gemini-flash-latest:generateContent?key=${GEMINI_API_KEY}`;
const INBOX_FOLDER_ID   = "YOUR_INBOX_FOLDER_ID";
const ARCHIVE_FOLDER_ID = "YOUR_ARCHIVE_FOLDER_ID";
const SHEET_NAME        = "Sheet1";

Complete script

See ExpenseTracker.js.

Deployment

mermaid

flowchart LR
    A[Edit code] --> B["Deploy → Manage deployments"]
    B --> C[Click pencil icon]
    C --> D[Version → New version]
    D --> E[Deploy]
    E --> F[Same URL preserved ✓]

Setting	Value
Type	Web app
Execute as	Me
Who has access	Anyone
Version	Always "New version" on update — never create a new deployment

Critical: Creating a new deployment generates a new URL and breaks all three iOS shortcuts. Always edit the existing deployment.

Initial setup sequence

Open Google Sheet → Extensions → Apps Script
Paste script, fill in constants, save
Deploy as Web App (first time only)
Copy the Web App URL into all three iOS shortcuts
Run createTrigger once from the editor
Authorize all permissions when prompted

6. iOS Shortcuts Overview

There are three independent shortcuts. Each has its own full action sequence — there is no shared core shortcut. See ios-shortcuts-setup.md for the complete step-by-step setup of each.

Shortcut	Type	How triggered	Sends
SMS Automation	Automation	SMS from defined contacts containing `Details:`	`.txt` (`text/plain`)
Log Transaction v2	Manual — Share Sheet	Tap Share in bank app, select shortcut	`.png` (`image/png`)
Input Transaction	Manual — Home Screen	Tap icon on home screen	`.txt` (`text/plain`)

Shared POST body structure

All three shortcuts POST to the same Web App URL with this JSON body:

json

{
  "filename": "sms_March 27 2026 at 9 10 AM.txt",
  "mimeType": "text/plain",
  "data": "<base64 encoded content>",
  "splitBy": 2
}

doPost then renames the file to embed splitBy: sms_March 27 2026 at 9 10 AM.txt → sms_March 27 2026 at 9 10 AM_split2.txt

7. Data Flow Details

File naming convention

Shortcut	Filename prefix	Extension
SMS Automation	`sms_`	`.txt`
Log Transaction v2	`file_`	`.png`
Input Transaction	`input_`	`.txt`

All filenames use iOS's raw Current Date format. See Known Limitations.

Input Transaction text format

The shortcut constructs this string before encoding:

New spending on [Current Date]
Amount $[Amount]
Details: [User typed details]

Gemini extracts structured fields from this natural language string.

Date resolution chain

mermaid

flowchart TD
    A[Gemini extracts date field] --> B{Is date null?}
    B -->|No| C[Use Gemini date ✓]
    B -->|Yes| D{"Filename matches\nyyyy-MM-dd pattern?"}
    D -->|Yes + time| E[Use yyyy-MM-dd HH:MM:SS]
    D -->|Yes, date only| F[Use yyyy-MM-dd 00:00]
    D -->|No match| G["Fallback: 1970-01-01 00:00\n⚠ current behaviour\ndue to filename format"]

Deferred processing sequence

mermaid

sequenceDiagram
    participant iPhone
    participant doPost
    participant Drive
    participant Trigger
    participant Gemini
    participant Sheet

    iPhone->>doPost: POST (base64 + splitBy)
    doPost->>Drive: createFile in Inbox (splitBy in filename)
    doPost->>iPhone: {"status":"ok"} in ~1 second
    Note over Trigger: 0–5 minutes later...
    Trigger->>Drive: getFiles from Inbox
    Trigger->>Gemini: send text or image
    Gemini->>Trigger: JSON array of entries
    Trigger->>Sheet: appendRow per entry
    Trigger->>Drive: moveTo Processed

8. Configuration Reference

Location	Key / Setting	How to find or set it
Apps Script	`GEMINI_API_KEY`	aistudio.google.com → Get API key
Apps Script	`INBOX_FOLDER_ID`	Open Inbox folder in Drive → copy ID from URL
Apps Script	`ARCHIVE_FOLDER_ID`	Open Processed folder in Drive → copy ID from URL
Apps Script	`SHEET_NAME`	Tab name at bottom of Google Sheet (default: `Sheet1`)
Apps Script	Web App URL	Deploy → Manage deployments → copy URL
iOS Shortcuts	Web App URL	Paste into "Get Contents of URL" in all 3 shortcuts
SMS Automation	Sender filter	Contact names: `Rogers Credit Card Transaction`
SMS Automation	Message contains	`Details:` (with colon)

9. Known Limitations & Design Decisions

Filename date format (active issue)

iOS Shortcuts' raw Current Date variable produces March 27, 2026 at 9:10 AM, not 2026-03-27_091000. The resolveFallbackDate regex expects yyyy-MM-dd and will never match — any row where Gemini returns null for date will show 1970-01-01 00:00.

Recommended fix: Add a Format Date action (format: yyyy-MM-dd_HHmmss) in each shortcut and use that variable in the filename field instead of raw Current Date.

Base64 line breaks

All three shortcuts have the Encode action set to Line Breaks: Every 76 Characters. This is RFC 2045 MIME format — it adds \n every 76 chars. Utilities.base64Decode in Apps Script handles this gracefully, but it is non-standard for raw data transfer. If you ever see corrupted files in Drive, disable line breaks in the Encode actions.

`getActiveSpreadsheet()` binding requirement

The script uses SpreadsheetApp.getActiveSpreadsheet() — this only works in a script bound to the spreadsheet via Extensions → Apps Script. A standalone Apps Script project will throw TypeError: Cannot call method "getSheetByName" of null.

Three independent shortcuts (no shared core)

Each shortcut is self-contained. Changes to the Web App URL or the split-by logic must be made in all three shortcuts independently. This was a pragmatic choice — the single-core-shortcut pattern was designed but the final implementation diverged into separate shortcuts per use case.

Deferred processing delay

Rows appear within 0–5 minutes of the shortcut completing. If immediate updates are needed, add processInboxFiles() at the end of doPost — but this makes the shortcut wait 3–5 seconds for a response.

SMS keyword

The automation triggers on messages containing Details: (with a colon), not just details. The keyword is case-sensitive in iOS automations — ensure the SMS content matches exactly.

Finance Tracker Automation — Technical Documentation ​

Table of Contents ​

1. System Overview ​

2. Architecture ​

High-level flow ​

Processing mode: deferred ​

Component responsibilities ​

3. Google Drive Setup ​

Folder structure ​

Steps ​

4. Google Sheets Setup ​

Create the spreadsheet ​

Column structure ​

5. Google Apps Script ​

How to open the bound script editor ​

Constants ​

Complete script ​

Deployment ​

Initial setup sequence ​

6. iOS Shortcuts Overview ​

Shared POST body structure ​

7. Data Flow Details ​

File naming convention ​

Input Transaction text format ​

Date resolution chain ​

Deferred processing sequence ​

8. Configuration Reference ​

9. Known Limitations & Design Decisions ​

Filename date format (active issue) ​

Base64 line breaks ​

getActiveSpreadsheet() binding requirement ​

Three independent shortcuts (no shared core) ​

Deferred processing delay ​

SMS keyword ​

Finance Tracker Automation — Technical Documentation

Table of Contents

1. System Overview

2. Architecture

High-level flow

Processing mode: deferred

Component responsibilities

3. Google Drive Setup

Folder structure

Steps

4. Google Sheets Setup

Create the spreadsheet

Column structure

5. Google Apps Script

How to open the bound script editor

Constants

Complete script

Deployment

Initial setup sequence

6. iOS Shortcuts Overview

Shared POST body structure

7. Data Flow Details

File naming convention

Input Transaction text format

Date resolution chain

Deferred processing sequence

8. Configuration Reference

9. Known Limitations & Design Decisions

Filename date format (active issue)

Base64 line breaks

`getActiveSpreadsheet()` binding requirement

Three independent shortcuts (no shared core)

Deferred processing delay

SMS keyword