Payment Schedule Automation
Commissioned Work | Forward Deployed Engineer
Context and Problem
Built a Google Apps Script (GAS) system that automates the manual work of reading invoices (PDF and JPEG) arriving on Google Drive and copying the payee, amount, and payment method into a payment schedule. GAS limits how long one run can take, so the system had to keep making progress even in months with many invoices.
Structure and Flow
Five modules (config, main flow, Drive, Gemini, and spreadsheet), run from a spreadsheet menu. Each invoice is read, renamed, written to the payment schedule, and moved to a processed folder, one at a time.
- Reading: Gemini API JSON mode extracts four fields (company name, amount, kana reading, payment method) in one call. The output shape is given in the prompt, and the prompt tells the model not to extract the receiving company's name, so the issuer becomes the company name.
- Name normalization: Unicode NFKC, a mapping table that unifies legal-entity notations (such as (株) and 株式会社), and removal of trailing honorifics, whitespace, and control characters. The model output is not trusted to be normalized; the same rules run in code.
- Company master: new companies are registered automatically, missing kana readings are filled in, and the master is re-sorted by kana. The payment schedule looks up kana from the master and is sorted by it.
- Monthly sheets: rows are written per payee on each month's sheet.
Handling GAS Limits, and Design Decisions
- Time limit: elapsed time is checked before each file; once 5 minutes have passed the run stops there (the GAS limit is 6 minutes) and reports how many files were processed and how many remain. Processed files leave the unprocessed folder, so running again resumes from the rest.
- No double runs: a script lock keeps two runs from executing at once.
- Failures: Gemini API errors and malformed responses are recorded on a separate "needs review" sheet and the loop moves on. Failed files stay in the unprocessed folder, so the next run retries them.
- Secrets and model: the API key lives in script properties, not code. The model comes from a config file generated from a shared model registry and can be overridden per deployment with a script property; because GAS cannot read the local registry, the value is baked into the generated file.
Development and Verification
Developed with clasp to sync local files and GAS. There are no automated tests; after clasp push the system is run from the spreadsheet and checked against a manual checklist.
Technologies
Google Apps ScriptGemini APIGoogle Drive (DriveApp)Google Sheets (SpreadsheetApp)UrlFetchAppLockServiceclaspJavaScript