The challenge
Renewal reminders arrived from the certifying authority as individual emails, and someone had to read each one and key it into a tracking sheet - re-sent reminders were entered twice, and payment confirmations arrived separately with no reliable way to tie them back to the application already logged.
What we built
We built a Google Apps Script that reads both message types straight out of the mailbox: renewal notices are matched on email, name and expiry date so a repeat reminder can never create a second row, and payment confirmations find the application already in the sheet and mark it paid with the certificate class, creating the row only when no application was logged. The team is notified in their Google Chat space as records land. What was a daily read-and-retype job now maintains itself, and the renewal pipeline is complete rather than as complete as anyone remembered to make it.
The result
Before: daily read-and-retype. Now: maintains itself.