Diabetes, Project - Automating prescription renewal

Disclaimer: this is a really rough guide. I intended to deprecate it, but every now and then I receive emails asking me for info or to update the code as the google API changes. Until I have time to rebuild a new version of this application, I leave the snippets below as a guide.

As a caregiver of a diabetic patient, and through discussions at work with doctors, we encountered a common frustration: the prescription renewal process.

The traditional prescription renewal process is cumbersome (while it varies between countries and regions, the majority of these steps generally apply): Patients must remember each medication renewal, which adds another layer of complexity on top of their daily diabetes management activities. When it's time for renewal, patients must call their doctor's office during business hours, often waiting on hold for extended periods. If unable to speak with someone directly, patients leave messages and then wait for responses, frequently needing to follow up if they don't hear back promptly.

Meanwhile, doctors must issue prescriptions, constantly checking their emails for these requests among many others. Once the doctor responds, the patient needs to forward this response to their pharmacy. Later, patients require confirmation from the pharmacy by phone that their request has been received. After waiting a day or two, patients must call again to check if their order is ready for pickup. Finally, when patients go to the pharmacy, they sometimes discover issues with their prescription that require restarting the entire process from the beginning.

This burden multiplies when managing medications for yourself and family members. For elderly patients or busy caregivers, this isn't just annoying; it can lead to dangerous medication gaps if the deadlines are not followed.

The solution

This simple gmail automation enables patients to specify when each prescription needs to be renewed. Then, an email with the correct attachments is sent to the doctor first. Next, a script will monitor for a response to that email, verify that the prescription is present, and then forward the email to the pharmacy. The pharmacy will then respond with the date of arrival of the medicine, reducing the time spent managing all of this by at least an hour every week (estimate based on the implementations I've set up myself).

More advanced optimizations can be implemented, for example by grouping multiple medicine orders in one common time window, so even the number of trips to the pharmacy can be reduced, but I don't want to overcomplicate this presentation. The only slightly more advanced feature that I want to include is a general tracking dashboard made in Google Sheets, because tracking the state of all requests can be really confusing when using only the Gmail UI.

A big downside of this solution is that it is not intended for an elderly person who has never used a computer, but rather for a caregiver or a patient with basic knowledge of operating Gmail and Google Sheets.

Solution Architecture

This Gmail add-on automates prescription renewals using Google Apps Script with the following workflow:

Initial Setup (one-time)

  • The patient uploads medication barcode images to Google Drive
  • Through the add-on UI, the patient links each medication name to its barcode image
  • The patient configures doctor email, pharmacy email, and renewal frequency in settings

Automated Renewal Requests

  • The system checks daily for prescriptions due for renewal based on configured frequency
  • When due, the system automatically sends email to doctor with:
    • Customizable subject line (e.g., "Prescription Renewal: {medication}")
    • Customizable email body text
    • Attached barcode image from Google Drive
  • Status updated to "Waiting for doctor" in tracking spreadsheet

Response Processing

  • The system monitors inbox hourly for replies from doctor
  • When the doctor replies with a prescription attachment, the system:
    • Detects the medication based on email subject
    • Extracts the prescription attachment
    • Forwards attachment to pharmacy with patient information
    • Updates status to "Sent to pharmacy"

Status Tracking

  • In the Google Sheet, each email shows its current status:
    • Last requested date
    • Doctor response status
    • Pharmacy status
    • Next renewal due date

Key Components

  1. Gmail Add-on UI: The user interface that patients interact with
  2. Apps Script Backend: Contains the logic and automation rules
  3. Google Sheet Configuration Storage: Securely stores settings and medicine information

I think this automation solution is elegant not because of some cutting-edge technology stack or clever coding (something that I'm hardly able to do once anyway), but because it maintains good privacy and maintainability profiles without being overengineered.

Implementation Details

The implementation can vary from deployment to deployment quite a bit, but these snippets should get you 80% of the way there if you want to implement something similar.

Part 1: Medication Barcode Storage System

First, let's create a UI for patients to link medication barcode images from Google Drive:

Figure 1.
Figure 2.
Figure 3.
Figure 4.

/**
* Creates the medication management UI
*/
function buildAddOn(e) {
var card = CardService.newCardBuilder();
card.setHeader(CardService.newCardHeader().setTitle("Prescription Renewal Assistant"));

// Add medication section
var addSection = CardService.newCardSection()
.setHeader("Add Medication")
.addWidget(CardService.newTextInput()
.setFieldName("medicationName")
.setTitle("Medication Name"))
.addWidget(CardService.newButtonSet()
.addButton(CardService.newTextButton()
.setText("Select Barcode Image")
.setOnClickAction(CardService.newAction().setFunctionName("selectBarcodeImage"))))
.addWidget(CardService.newButtonSet()
.addButton(CardService.newTextButton()
.setText("Save Medication")
.setOnClickAction(CardService.newAction().setFunctionName("saveMedication"))));

// View medications section
var viewSection = CardService.newCardSection()
.setHeader("My Medications")
.addWidget(CardService.newButtonSet()
.addButton(CardService.newTextButton()
.setText("View My Medications")
.setOnClickAction(CardService.newAction().setFunctionName("viewMedications"))));

// Process status section
var statusSection = CardService.newCardSection()
.setHeader("Renewal Status")
.addWidget(CardService.newButtonSet()
.addButton(CardService.newTextButton()
.setText("Check Renewal Status")
.setOnClickAction(CardService.newAction().setFunctionName("checkStatus"))));

card.addSection(addSection).addSection(viewSection).addSection(statusSection);
return [card.build()];
}

/**
* Opens a picker dialog for selecting a barcode image from Drive
*/
function selectBarcodeImage(e) {
var html = HtmlService.createHtmlOutputFromFile('Picker')
.setWidth(600)
.setHeight(425)
.setSandboxMode(HtmlService.SandboxMode.IFRAME);

return CardService.newActionResponseBuilder()
.setNotification(CardService.newNotification()
.setText("Select a barcode image from your Drive"))
.setOpenDynamicLinkAction(CardService.newAction()
.setFunctionName("openPicker"))
.build();
}

/**
* Handles the file selected from Drive Picker
*/
function handlePickerSelection(fileId) {
var userProperties = PropertiesService.getUserProperties();
userProperties.setProperty('tempBarcodeFileId', fileId);

// Get file info for confirmation
var file = DriveApp.getFileById(fileId);

return CardService.newActionResponseBuilder()
.setNotification(CardService.newNotification()
.setText("Image selected: " + file.getName()))
.build();
}

/**
* Saves medication information to the configuration spreadsheet
*/
function saveMedication(e) {
var medicationName = e.formInput.medicationName;
var barcodeFileId = PropertiesService.getUserProperties().getProperty('tempBarcodeFileId');

if (!medicationName || !barcodeFileId) {
return CardService.newActionResponseBuilder()
.setNotification(CardService.newNotification()
.setText("Please provide a medication name and select a barcode image"))
.build();
}

// Get or create the config spreadsheet
var configSheet = getConfigSpreadsheet();
var medicationsSheet = configSheet.getSheetByName('Medications') ||
configSheet.insertSheet('Medications');

// Find the next empty row
var lastRow = medicationsSheet.getLastRow();

// Add the medication info
medicationsSheet.appendRow([
medicationName,
barcodeFileId,
new Date().toISOString(),
"Active"
]);

// Clear temporary storage
PropertiesService.getUserProperties().deleteProperty('tempBarcodeFileId');

return CardService.newActionResponseBuilder()
.setNotification(CardService.newNotification()
.setText("Medication saved: " + medicationName))
.build();
}

/**
* Helper function to get or create the configuration spreadsheet
*/
function getConfigSpreadsheet() {
var userProperties = PropertiesService.getUserProperties();
var configSheetId = userProperties.getProperty('configSheetId');

if (configSheetId) {
try {
return SpreadsheetApp.openById(configSheetId);
} catch (e) {
// Sheet might have been deleted, continue to create a new one
}
}

// Create a new configuration spreadsheet
var newSheet = SpreadsheetApp.create('Prescription Renewal Config');

// Set up initial sheets
var medicationsSheet = newSheet.getSheetByName('Sheet1');
medicationsSheet.setName('Medications');
medicationsSheet.appendRow(['Medication Name', 'Barcode File ID', 'Date Added', 'Status']);

// Create the status tracking sheet
var statusSheet = newSheet.insertSheet('RenewalStatus');
statusSheet.appendRow(['Medication', 'Last Requested', 'Doctor Response', 'Pharmacy Status', 'Next Due']);

// Create the configuration sheet
var configSheet = newSheet.insertSheet('Config');
configSheet.appendRow(['Setting', 'Value']);
configSheet.appendRow(['Doctor Email', '']);
configSheet.appendRow(['Pharmacy Email', '']);
configSheet.appendRow(['Renewal Frequency (days)', '30']);
configSheet.appendRow(['Email Subject Template', 'Prescription Renewal Request: {medication}']);
configSheet.appendRow(['Email Body Template', 'Dear Doctor,\n\nI would like to request a renewal for my
{medication} prescription. The barcode is attached.\n\nThank you,\n{patient_name}']);

// Save the spreadsheet ID
userProperties.setProperty('configSheetId', newSheet.getId());

return newSheet;
}

Part 2: Automated Email Scheduling System

Next, let's implement the periodic email scheduling system:


/**
* Sets up time-based triggers for prescription renewal
*/
function setupRenewalTriggers() {
// Delete any existing triggers
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
if (triggers[i].getHandlerFunction() === 'checkAndSendRenewalRequests') {
ScriptApp.deleteTrigger(triggers[i]);
}
}

// Create a daily trigger to check for renewals
ScriptApp.newTrigger('checkAndSendRenewalRequests')
.timeBased()
.everyDays(1)
.atHour(9)
.create();

return CardService.newActionResponseBuilder()
.setNotification(CardService.newNotification()
.setText("Renewal scheduler activated"))
.build();
}

/**
* Checks if any prescriptions need renewal and sends requests
*/
function checkAndSendRenewalRequests() {
var configSheet = getConfigSpreadsheet();
var medicationsSheet = configSheet.getSheetByName('Medications');
var statusSheet = configSheet.getSheetByName('RenewalStatus');
var configValues = configSheet.getSheetByName('Config').getDataRange().getValues();

// Get configuration values
var config = {};
for (var i = 1; i < configValues.length; i++) {
config[configValues[i][0]] = configValues[i][1];
}

var doctorEmail = config['Doctor Email'];
var renewalFrequency = parseInt(config['Renewal Frequency (days)']) || 30;
var emailSubjectTemplate = config['Email Subject Template'];
var emailBodyTemplate = config['Email Body Template'];

// Get user's name from Gmail
var userEmail = Session.getActiveUser().getEmail();
var userName = userEmail.split('@')[0].replace(/\./g, ' ').replace(/\b\w/g, function(l) { return
l.toUpperCase(); });

// Get all medications
var medications = medicationsSheet.getDataRange().getValues();
var headers = medications.shift(); // Remove headers

// Get all status records
var statuses = statusSheet.getDataRange().getValues();
var statusHeaders = statuses.shift(); // Remove headers

// Check each medication
for (var i = 0; i < medications.length; i++) {
var medicationName = medications[i][0];
var barcodeFileId = medications[i][1];
var status = medications[i][3];

if (status !== 'Active') continue;

// Find status record for this medication
var statusRecord = null;
var statusRowIndex = -1;

for (var j = 0; j < statuses.length; j++) {
if (statuses[j][0] === medicationName) {
statusRecord = statuses[j];
statusRowIndex = j + 1; // +1 because we removed the header row
break;
}
}

var lastRequested = statusRecord ? new Date(statusRecord[1]) : new Date(0);
var nextDue = statusRecord ? (statusRecord[4] ? new Date(statusRecord[4]) : null) : null;

var today = new Date();

// If no next due date or if today is past the next due date
if (!nextDue || today >= nextDue) {
// Send renewal email
var emailSubject = emailSubjectTemplate.replace('{medication}', medicationName);
var emailBody = emailBodyTemplate
.replace('{medication}', medicationName)
.replace('{patient_name}', userName);

// Get the barcode file
var barcodeFile = DriveApp.getFileById(barcodeFileId);

// Send email with attachment
GmailApp.sendEmail(
doctorEmail,
emailSubject,
emailBody,
{
attachments: [barcodeFile.getBlob()],
name: userName
}
);

// Update status in the spreadsheet
var newNextDue = new Date();
newNextDue.setDate(today.getDate() + renewalFrequency);

if (statusRowIndex > 0) {
// Update existing record
statusSheet.getRange(statusRowIndex + 1, 2).setValue(today); // Last Requested
statusSheet.getRange(statusRowIndex + 1, 3).setValue("Waiting for doctor"); // Doctor Response
statusSheet.getRange(statusRowIndex + 1, 4).setValue("Not sent"); // Pharmacy Status
statusSheet.getRange(statusRowIndex + 1, 5).setValue(newNextDue); // Next Due
} else {
// Create new record
statusSheet.appendRow([
medicationName,
today,
"Waiting for doctor",
"Not sent",
newNextDue
]);
}

// Log the renewal request
console.log("Sent renewal request for " + medicationName);
}
}
}

Part 3: Email Response Processing System

Now, let's implement the system to monitor for the doctor's response and forward prescriptions to the pharmacy:

Figure 5.
Figure 6.
Figure 7.

/**
* Sets up a Gmail trigger to process incoming emails
*/
function setupEmailMonitoring() {
// Create a trigger that runs every hour
ScriptApp.newTrigger('processIncomingEmails')
.timeBased()
.everyHours(1)
.create();

return CardService.newActionResponseBuilder()
.setNotification(CardService.newNotification()
.setText("Email monitoring activated"))
.build();
}

/**
* Processes incoming emails to check for prescription responses
*/
function processIncomingEmails() {
var configSheet = getConfigSpreadsheet();
var statusSheet = configSheet.getSheetByName('RenewalStatus');
var configValues = configSheet.getSheetByName('Config').getDataRange().getValues();

// Get configuration values
var config = {};
for (var i = 1; i < configValues.length; i++) {
config[configValues[i][0]] = configValues[i][1];
}

var doctorEmail = config['Doctor Email'];
var pharmacyEmail = config['Pharmacy Email'];

// Get all status records
var statuses = statusSheet.getDataRange().getValues();
var statusHeaders = statuses.shift(); // Remove headers

// Filter for medications waiting for doctor response
var waitingMedications = [];
for (var i = 0; i < statuses.length; i++) {
if (statuses[i][2] === "Waiting for doctor") {
waitingMedications.push({
name: statuses[i][0],
index: i + 1 // +1 because we removed the header row
});
}
}

if (waitingMedications.length === 0) {
return; // Nothing to process
}

// Search for emails from the doctor
var threads = GmailApp.search('from:' + doctorEmail + ' has:attachment newer_than:7d');

// Process each thread
for (var i = 0; i < threads.length; i++) {
var messages = threads[i].getMessages();

// Check each message in the thread
for (var j = 0; j < messages.length; j++) {
var message = messages[j];
var subject = message.getSubject();
var attachments = message.getAttachments();

// Only process if there are attachments (prescriptions)
if (attachments.length > 0) {
// Try to match this email to one of our waiting medications
for (var k = 0; k < waitingMedications.length; k++) {
var medication = waitingMedications[k];

// Simple matching - check if medication name is in the subject
if (subject.indexOf(medication.name) !== -1) {
// Found a match! Forward to pharmacy
forwardPrescriptionToPharmacy(message, medication.name, pharmacyEmail, attachments);

// Update status
var statusRowIndex = medication.index + 1; // +1 for header row
statusSheet.getRange(statusRowIndex, 3).setValue("Received"); // Doctor Response
statusSheet.getRange(statusRowIndex, 4).setValue("Sent to pharmacy"); // Pharmacy Status

// Remove from waiting list
waitingMedications.splice(k, 1);
break;
}
}
}
}
}
}

/**
* Forwards prescription to the pharmacy
*/
function forwardPrescriptionToPharmacy(message, medicationName, pharmacyEmail, attachments) {
// Get user's name from Gmail
var userEmail = Session.getActiveUser().getEmail();
var userName = userEmail.split('@')[0].replace(/\./g, ' ').replace(/\b\w/g, function(l) { return
l.toUpperCase(); });

// Create forwarding email
var subject = "Prescription Order: " + medicationName + " for " + userName;
var body = "Hello,\n\nPlease find attached my prescription for " + medicationName + ".\n\n" +
"Patient Name: " + userName + "\n" +
"Email: " + userEmail + "\n\n" +
"Please let me know when the medication is ready for pickup.\n\n" +
"Thank you,\n" + userName;

// Send email with attachments
GmailApp.sendEmail(
pharmacyEmail,
subject,
body,
{
attachments: attachments,
name: userName
}
);

// Log the forwarding
console.log("Forwarded prescription for " + medicationName + " to pharmacy");
}

Part 4: Process Status Dashboard

Let's configure the a Google Sheet file to visualize the current status of all prescriptions.


/**
* Displays the status dashboard
*/
function checkStatus(e) {
var configSheet = getConfigSpreadsheet();
var statusSheet = configSheet.getSheetByName('RenewalStatus');

if (!statusSheet || statusSheet.getLastRow() <= 1) {
return CardService.newActionResponseBuilder()
.setNotification(CardService.newNotification()
.setText("No prescription renewals in progress"))
.build();
}

var statuses = statusSheet.getDataRange().getValues();
var headers = statuses.shift(); // Remove headers

var card = CardService.newCardBuilder();
card.setHeader(CardService.newCardHeader().setTitle("Prescription Renewal Status"));

var section = CardService.newCardSection()
.setHeader("Current Status");

// Add each medication status
for (var i = 0; i < statuses.length; i++) {
var medication = statuses[i][0];
var lastRequested = new Date(statuses[i][1]).toLocaleDateString();
var doctorStatus = statuses[i][2];
var pharmacyStatus = statuses[i][3];
var nextDue = statuses[i][4] ? new Date(statuses[i][4]).toLocaleDateString() : "N/A";

// Create status widget
var statusWidget = CardService.newKeyValue()
.setTopLabel(medication)
.setContent(doctorStatus + " | " + pharmacyStatus)
.setBottomLabel("Last requested: " + lastRequested + " | Next due: " + nextDue);

// Set icon based on status
if (doctorStatus === "Waiting for doctor") {
statusWidget.setIcon(CardService.Icon.DESCRIPTION);
} else if (pharmacyStatus === "Sent to pharmacy") {
statusWidget.setIcon(CardService.Icon.FLIGHT_DEPARTURE);
} else if (pharmacyStatus === "Ready for pickup") {
statusWidget.setIcon(CardService.Icon.CONFIRMATION_NUMBER);
} else {
statusWidget.setIcon(CardService.Icon.CLOCK);
}

section.addWidget(statusWidget);
}

card.addSection(section);
return [card.build()];
}

/**
* Manually triggers a check for renewals
*/
function manualCheckRenewals(e) {
checkAndSendRenewalRequests();

return CardService.newActionResponseBuilder()
.setNotification(CardService.newNotification()
.setText("Checked for renewals"))
.build();
}

/**
* Manually triggers email processing
*/
function manualProcessEmails(e) {
processIncomingEmails();

return CardService.newActionResponseBuilder()
.setNotification(CardService.newNotification()
.setText("Processed incoming emails"))
.build();
}

Part 5: Configuration Management

Finally, let's add a settings screen to configure the system:

Figure 8.
Figure 9.
Figure 10.
Figure 11.

/**
* Displays the settings page
*/
function showSettings(e) {
var configSheet = getConfigSpreadsheet();
var configValues = configSheet.getSheetByName('Config').getDataRange().getValues();

// Get configuration values
var config = {};
for (var i = 1; i < configValues.length; i++) {
config[configValues[i][0]] = configValues[i][1];
}

var card = CardService.newCardBuilder();
card.setHeader(CardService.newCardHeader().setTitle("Settings"));

var section = CardService.newCardSection()
.setHeader("Email Settings");

// Doctor Email
section.addWidget(CardService.newTextInput()
.setFieldName("doctorEmail")
.setTitle("Doctor's Email")
.setValue(config['Doctor Email'] || ''));

// Pharmacy Email
section.addWidget(CardService.newTextInput()
.setFieldName("pharmacyEmail")
.setTitle("Pharmacy Email")
.setValue(config['Pharmacy Email'] || ''));

// Renewal Frequency
section.addWidget(CardService.newTextInput()
.setFieldName("renewalFrequency")
.setTitle("Renewal Frequency (days)")
.setValue(config['Renewal Frequency (days)'] || '30'));

// Email Templates
var templateSection = CardService.newCardSection()
.setHeader("Email Templates");

// Subject Template
templateSection.addWidget(CardService.newTextInput()
.setFieldName("subjectTemplate")
.setTitle("Email Subject Template")
.setValue(config['Email Subject Template'] || 'Prescription Renewal Request: {medication}'));

// Body Template
templateSection.addWidget(CardService.newTextArea()
.setFieldName("bodyTemplate")
.setTitle("Email Body Template")
.setValue(config['Email Body Template'] || 'Dear Doctor,\n\nI would like to request a renewal for my
{medication} prescription. The barcode is attached.\n\nThank you,\n{patient_name}'));

// Scheduler
var schedulerSection = CardService.newCardSection()
.setHeader("Schedule Settings");

schedulerSection.addWidget(CardService.newButtonSet()
.addButton(CardService.newTextButton()
.setText("Activate Daily Check")
.setOnClickAction(CardService.newAction().setFunctionName("setupRenewalTriggers")))
.addButton(CardService.newTextButton()
.setText("Activate Email Monitoring")
.setOnClickAction(CardService.newAction().setFunctionName("setupEmailMonitoring"))));

// Manual actions
schedulerSection.addWidget(CardService.newButtonSet()
.addButton(CardService.newTextButton()
.setText("Check Now")
.setOnClickAction(CardService.newAction().setFunctionName("manualCheckRenewals")))
.addButton(CardService.newTextButton()
.setText("Process Emails Now")
.setOnClickAction(CardService.newAction().setFunctionName("manualProcessEmails"))));

// Save button
var buttonSet = CardService.newButtonSet()
.addButton(CardService.newTextButton()
.setText("Save Settings")
.setOnClickAction(CardService.newAction().setFunctionName("saveSettings")));

card.addSection(section).addSection(templateSection).addSection(schedulerSection);
card.addSection(CardService.newCardSection().addWidget(buttonSet));

return [card.build()];
}

/**
* Saves the settings
*/
function saveSettings(e) {
var configSheet = getConfigSpreadsheet();
var configSheet = configSheet.getSheetByName('Config');

// Update configuration values
configSheet.getRange(2, 2).setValue(e.formInput.doctorEmail);
configSheet.getRange(3, 2).setValue(e.formInput.pharmacyEmail);
configSheet.getRange(4, 2).setValue(e.formInput.renewalFrequency);
configSheet.getRange(5, 2).setValue(e.formInput.subjectTemplate);
configSheet.getRange(6, 2).setValue(e.formInput.bodyTemplate);

return CardService.newActionResponseBuilder()
.setNotification(CardService.newNotification()
.setText("Settings saved"))
.build();
}

Deployment and Testing

To deploy this Gmail add-on:

  1. Save all the code in your Google Apps Script project
  2. Click on "Deploy" > "New deployment"
  3. Choose "Deploy as Add-on" for Gmail
  4. Set appropriate access controls
  5. Submit to the Google Workspace Marketplace or deploy it internally for your organization

To test your add-on without full deployment:


/**
* Test function to simulate the entire workflow
*/
function testWorkflow() {
// 1. Set up configuration
var configSheet = getConfigSpreadsheet();

// 2. Add a test medication
var testFile = DriveApp.getFilesByName("TestBarcode.jpg").next();
var barcodeFileId = testFile.getId();

var medicationsSheet = configSheet.getSheetByName('Medications');
medicationsSheet.appendRow([
"Test Medication",
barcodeFileId,
new Date().toISOString(),
"Active"
]);

// 3. Simulate checking for renewals
checkAndSendRenewalRequests();

console.log("Test workflow completed. Check the RenewalStatus sheet.");
}

Tips and Resources

If you want to try something like this, the best hack for avoiding setting up the whole Google extension store process is to try it in a lab environment, where some resources are already configured and all permission issues are handled: google lab

Then, if you want to try it on your own Gmail inbox, try publishing the add-on as a test. A good summary on how to do that is available at: stackoverflow

Here are some additional official resources:

Thank you

Thank you for reading and, as always, let me know what you think at XY at gmail.com where X = tommaso and Y = bassignana, no dots between X and Y.