Automate Subscription Renewal using Google Apps Script

Welcome!

Managing subscription renewals can be a headache for any business, and a gym I go to, was no exception. They struggled with keeping track of memberships and reminding clients about renewal, often resulting in lost customers.

To address this, I built an automation workflow using Google Sheets and Google Apps Script, which simplified the process of reminding the clients about their subscription renewal.

In this blog we are going to be automating subscription renewal using Google Apps Script.

If you want a video version of the blog, you can check out the video tutorial given below.

What can this Automation do?

This function is designed to automate the process of sending renewal reminder emails to users whose subscriptions are due to expire within the current month. This function operates on a Google Sheet, fetching data like email addresses, subscription end dates, and user names to determine who needs a reminder.

Now that we have understood the problem and the possible solution, lets get coding.

Sample Google Sheet

The Google Sheet that I will be using for this blog contains the following details:

1. User ID
2. Email Address
3. Name of Client
4. Date of Birth
5. Date Joined
6. Subscription Start Date
7. Subscription End Date
8. Payment Method
9. Amount Paid
10. Status
11. Logs

If you want to work with the sheet, click here.

Step1: Write the Automation Script

While you are in the Google Sheet, let’s open up the Script Editor to write some Google Apps Script. To open the Script Editor, follow these steps:

1. Click on Extensions and open the Script Editor.

2. This brings up the Script Editor as shown below.

We have reached the script editor lets code.

Now that we have our Google Sheet ready and good to go, let’s go ahead and write the code to send automated subscription renewal emails.

function checkAndSendRenewalEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const data = sheet.getDataRange().getValues();

We start off by creating a new function checkAndSendRenewalEmails(), inside which we get the active spreadsheet using the getActiveSpreadsheet(), function followed by getting the sheet by name.

Once we get the sheet, we use the getDataRange().getValues)() function, that gets all the data from the sheet into a 2D array.

const EmailRange = sheet.getRange(3, 2, sheet.getLastRow() - 2, 1);
const EndDateRange = sheet.getRange(3, 7, sheet.getLastRow() - 2, 1);
const NameRange = sheet.getRange(3, 3, sheet.getLastRow() - 2, 1);
const StatusRange = sheet.getRange(3, 11, sheet.getLastRow() - 2, 1); 
const LogsRange = sheet.getRange(3, 12, sheet.getLastRow() - 2, 1); 

On accessing the contents of the sheet, we define ranges for all the columns in the sheet using the getRange() function.

  const EmailValues = EmailRange.getValues();
  const EndDateValues = EndDateRange.getValues();
  const NameValues = NameRange.getValues();
  const StatusValues = StatusRange.getValues();
  const LogsValues = LogsRange.getValues();

We then use each of the ranges defined earlier and fetch the actual values using the range and getValues() function.

const today = new Date();
const currentmonth = today.getMonth();
const currentyear = today.getFullYear();
const dayOfMonth = today.getDate();

if (dayOfMonth !== 30) { // Adjust this as per your requirement
  Logger.log("Today is not the 30th of the month. No emails will be sent.");
  return;
}

As discussed, we want to ensure that the emails are sent on a specific day of the month. We fetch the current year, month and date using the today.getFullYear() ,getMonth() and getDate() functions.

The loop then checks the current date and only sends the email if it is the designated day, otherwise it logs a message and exits.

 for (let i = 0; i < EmailValues.length; i++) {
    const email = EmailValues[i][0];
    const endDateStr = EndDateValues[i][0];
    const name = NameValues[i][0];

We use a for loop to iterate through each row in the sheet, processing on user at a time. Through the loop, we extract the user’s email, subscription end date, and name for processing.

if (email !== "" && endDateStr !== "") {
      const endDate = new Date(endDateStr);
      if (isNaN(endDate)) {
        Logger.log(`Invalid date in row ${i + 3}: ${endDateStr}`);
        LogsValues[i][0] = `Invalid date format: ${endDateStr}`;
        continue;
      }

This function converts the subscription end date from a string to a Date object. Incase the conversion fails, it will log the issue and move on to the next user/client, ensuring that the an invalid entry does not disrupt the process.

const monthend = endDate.getMonth();
const dayend = endDate.getDate();

We extract the month and day from endDate object, which represents the subscription end date of the user.

if (monthend === currentmonth && endDate.getFullYear() === currentyear) {
        try {
          const emailBody = `
Dear ${name},

We hope you're enjoying your fitness journey with us! We wanted to remind you that your gym membership is set to expire on ${endDateStr}.

To keep your momentum going and continue achieving your fitness goals, we encourage you to renew your membership before it expires. By renewing now, you'll ensure that you have uninterrupted access to all our facilities, classes, and expert trainers.

If you have any questions about your renewal options or need assistance, our team is here to help. Just give us a call or drop by the front desk on your next visit.

Thank you for being a valued member of our fitness community. We look forward to supporting you in your ongoing pursuit of health and wellness.

Stay strong,

24/7 Fitness
Member Support Team
Rachel Grae

P.S. Renew your membership this month and enjoy exclusive offers, including discounted personal training sessions!
`;
          MailApp.sendEmail(email, "Please upgrade your subscription", emailBody);
          Logger.log(`Email sent to ${email} for renewal date ${endDateStr}`);
          StatusValues[i][0] = 'Pending';
          LogsValues[i][0] = 'EMAIL SENT';
        } catch (e) {
          Logger.log(`Failed to send email to ${email}: ${e.message}`);
          LogsValues[i][0] = `Failed to send email: ${e.message}`;
        }
      }
    } else {
      Logger.log(`Skipping row ${i + 3} due to missing email or end date.`);
      LogsValues[i][0] = 'Missing email or end date';
    }
  }

This is the main part of the code that checks if the subscription end date falls within the current month and year. If it does, an email is sent to the user reminding them to renew their subscription.

To do this we use the an if loop to check if the current month and year match. On matching, a new email body is designed for that particular client and an email is sent.

If the email is sent, the status is updated Pending , and a log entry of "EMAIL SENT" is made. If an error occurs during email sending, the scripts catches it, logs the error, and updates the logs in the sheet accordingly.

  StatusRange.setValues(StatusValues);
  LogsRange.setValues(LogsValues);
}

After processing all the rows, the script writes the updated status and logs back to the sheet. To ensure that all operations are recorded, providing transparency and accountability for the renewal reminder process.

Our code is complete and good to go.

Step2: Check the Output

Its time to see if the code is able to analyse the subscription end dates and sent subscription renewal emails to the clients accordingly.

On running the code you should get an output like this in the Execution Log.

Now that we have no error in the execution log, if you switch back to the sheet you will see that the status and the logs have been updated for those clients whose subscription end dates are in the current month.

Now that we know that the emails have been sent out, let’s take a look at the email received by the client.

Conclusion

In conclusion, the use of Google Apps Script can efficiently automate the process of sending subscription renewal emails. This functionality is especially valuable for managing and organizing member data, ensuring timely reminders for those whose memberships are due to expire. This approach not only saves time but also enhances member retention and provides a seamless experience for both the gym and its members.

You can check out the code by clicking on the link given below.

Google-Apps-Script/Renewal.js at master · aryanirani123/Google-Apps-Script

Feel free to reach out if you have any issues/feedback at aryanirani123@gmail.com.