Healthcare

Medical Training Diploma Automation System

Automated diploma processing for 8,000+ students, reducing weekly admin hours by 95%.

95%Reduction in weekly admin hours

The Challenge

A medical training institute offering certification courses to 8,000+ students annually was buckling under the administrative weight of diploma processing. Each student's completion required: verifying attendance records across multiple course modules, assigning a unique sequential certificate number (with zero tolerance for duplicates or gaps), generating the diploma document with correct personal details and course information, scheduling the diploma collection appointment, and sending reminders. The admin team spent 10+ hours weekly on these tasks, and the sequential numbering system — maintained in a shared spreadsheet — had already produced 3 duplicate certificate numbers that year due to concurrent edits. Students frequently called in asking about their diploma status, consuming additional staff time. The institute needed to scale to 12,000+ students the following year without adding headcount.

Our Solution

We built an end-to-end automation system using Google Apps Script as the backbone, deeply integrated with their existing Google Workspace. The system auto-populates diploma data from course completion records, implements thread-safe certificate numbering using Google Apps Script's LockService to prevent any possibility of duplicates even under concurrent access, generates pre-filled diploma documents using Google Docs templates, and automatically schedules collection appointments. Daily reminder workflows send WhatsApp messages to students 48 hours and 2 hours before their appointment. We also built a 24/7 self-service web app (deployed as a Google Apps Script web app) where students can look up their diploma status by entering their ID number — showing whether their diploma is processing, ready for pickup, or already collected. The admin dashboard provides a real-time view of the processing pipeline with filters by course, date, and status.

Detailed Approach

The entire system runs within Google Workspace, which was critical for the client — no external servers, no additional subscriptions, and their IT team can maintain it. The backbone is a Google Apps Script project with 12 functions orchestrated by time-driven triggers.

The certificate numbering challenge was the most technically interesting. Google Sheets doesn't support database-style auto-increment, and with 3 admin staff potentially processing diplomas simultaneously, race conditions were inevitable. We solved this using Google Apps Script's LockService, which provides a mutex mechanism. When the system needs to assign a number, it acquires an exclusive lock, reads the current maximum from a dedicated "counter" sheet, increments it, writes it back, and releases the lock — all within a try/finally block to prevent deadlocks. The lock has a 30-second timeout with automatic retry.

The self-service web app is deployed as a Google Apps Script web application with a clean, mobile-responsive interface. Students enter their national ID number, the app queries the master spreadsheet using an indexed lookup (we pre-sort data by ID for performance), and displays their current status with an estimated completion date. The app serves 200+ lookups weekly and has completely eliminated the "what's my diploma status?" phone calls that were consuming 5+ hours of staff time per week. Make.com handles the WhatsApp reminder orchestration, pulling upcoming appointments daily and dispatching messages via the WhatsApp Business API.

Key Results

🕐 Discovery and Google Workspace audit took 3 days

✓95% reduction in weekly admin hours — from 10+ hours to under 30 minutes
✓Zero duplicate certificate numbers since launch — previously 3 duplicates per year
✓Student self-service portal handles 200+ status lookups per week with zero staff involvement
✓Appointment no-show rate dropped from 25% to 8% with automated WhatsApp reminders
✓System scaled to handle 12,500 students in year two without any additional staff
✓Diploma processing time per student reduced from 15 minutes to under 60 seconds

Discovery and Google Workspace audit took 3 days. Core automation development took 2.5 weeks. The self-service web app was built in an additional week. We ran a parallel period processing 200 test records before going live.