Invoice Payment Schedule Automation
Commissioned Work
Overview
Built a Google Apps Script system that extracts invoice PDFs from Google Drive through the Gemini API and auto-populates a payment schedule in Google Sheets. A continuation-execution pattern that works around the GAS runtime-time limit, together with automatic maintenance of a company master, makes the monthly 50+ invoice workload feasible and cuts accounting-side manual work significantly.
Architecture
To avoid a single-file blob, the code is split across five modules — `Config.js`, `Main.js`, `GeminiService.js`, `DriveService.js`, `SheetService.js`. Even inside the GAS constraint that triggers can only bind to top-level functions, clearly separating the service layer keeps test targets and mock surfaces decoupled.
Secrets such as API keys are never written into `Config.js`; they live in GAS script properties. The repo therefore never carries secrets, and environment-specific values can be swapped without code changes. Local development uses clasp (`clasp push / pull / open`) to keep the GAS side in sync while the code is versioned in Git.
Key Features
Structured invoice extraction: `GeminiService.js` calls Gemini with `response_mime_type: "application/json"` and an explicit JSON schema, pulling `companyName`, `amount`, `kanaReading`, and `paymentMethodStr` in a single call. The prompt leans on the model's vision capability so amounts circled in red pen are preferred when multiple numbers appear.
Self-maintaining company master: `addOrUpdateCompany()` in `SheetService.js` registers new companies automatically and fills in missing katakana readings. `sortMasterByKana()` keeps the master in Japanese-syllabary order, so the payment schedule also renders in that order without extra sorting.
Company-name normalization: `normalizeCompanyName()` applies Unicode NFKC plus a 16-entry mapping that unifies legal-entity notations ('(株)', '㈱', '㈲', etc.) to the canonical form. Honorifics ('御中', '様') are stripped, ASCII is uppercased and halfwidth-normalized, and whitespace is collapsed — all in sequence — which prevents key collisions in the master.
Error path: invoices that fail to parse are recorded in a 'needs review' sheet with a file link and the failure reason, so manual follow-up is always discoverable.
Working Around GAS Limits
`isApproachingTimeLimit()` in `Main.js` trips at the five-minute mark so the script aborts safely inside the GAS six-minute ceiling. A continuation pattern persists the list of files already processed and skips them on the next run, so 50+ invoice batches always finish end-to-end.
Progress notifications spell out 'N files remaining'. GAS jobs are famously invisible; explicit progress messages combined with incremental writes to Drive and Sheets make it clear the job is alive.
Development
Local development runs through clasp for two-way sync with GAS, and commits land in Git. Script-property setup and the `clasp push` flow are documented in `CLAUDE.md`, making handover and rebuilds straightforward.