Automating Email Reminders Using WSO2 Choreo and Google APIs: A Step-by-Step Guide

In this blog, you’ll learn how to create a scheduled task that reads data from a Google Sheet stored in Google Drive and automatically sends an email to a specific user based on that data.

Use Case:
At Organization “ABC,” there’s a requirement to notify cleaning staff a day before their scheduled duty. Currently, the organization maintains a timetable in a Google Sheet and manually checks it each day to send reminder emails. To streamline this process, they want to automate these reminders, reducing the daily manual effort.

To automate this process, we’ll leverage the following tools:

• WSO2 Choreo : A powerful developer platform that will serve two primary purposes:
  1. Deploying the Python script
  2. Creating a scheduled task for automation
• Python : The programming language used to develop the script that will handle the logic
• Google APIs: To access and interact with Google Drive and Google Sheets, where the timetable is stored..

Let’s now visualize this with a high-level diagram:

High Level Diagram

Before diving into the implementation, let’s take a look at the structure of the Google Sheet that will serve as our data source. The sheet is organized in the following format:

• First column: Lists the days of the week (e.g., Monday, Tuesday, etc.)
• Second column: Contains the corresponding dates in numerical format (e.g., 1, 2, 3, etc.)
• Subsequent columns: Contain the email addresses of the cleaning staff, with each column representing a different staff member.

Step 1 : Creating Python Script

We’ll begin by writing a Python script that reads the Google Sheet and sends an email to the appropriate user. The full code can be accessed on GitHub [here]. In this section, I’ll highlight some key aspects of the code.

First, we need to define the scope and credentials required to access the Google Sheets API. This is done using the credentials1.json file, which securely stores the necessary Google API credentials. The script will authenticate using this file, ensuring it has the proper permissions to read the data from Google Sheets and trigger the email process..

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('/credentials1.json', scope)  # Replace 'credentials.json' with your credentials file

From below code snippet we will be iterating through out the Google sheet to find the person who will be cleaning tomorrow

# Get tomorrow's date (the day before the ticket scan day)
tomorrow = datetime.date.today() + datetime.timedelta(days=1)

# Find the row index corresponding to tomorrow's date in column B
row_index = None
dates = worksheet.col_values(2)
for index, date in enumerate(dates):
    if date == tomorrow.strftime('%-m/%-d/%Y'):  # Adjusted date format for comparison
        row_index = index + 1  # Adding 1 because index starts from 0, but row numbering starts from 1
        break

if row_index is None:
    print("Tomorrow's date not found in the sheet.")
else:
    # Get the email address of the person assigned for tomorrow from columns C to K
    def get_column_letter(column_index):
        letters = ''
        while column_index > 0:
            column_index, remainder = divmod(column_index - 1, 26)
            letters = chr(65 + remainder) + letters
        return letters

word = "Clean"
print(row_index)
row_values = worksheet.row_values(row_index)

column_alphabet = None
for col_index, cell_value in enumerate(row_values, start=1):
    if isinstance(cell_value, str) and word in cell_value:
        column_alphabet = get_column_letter(col_index)
        print(column_alphabet)
        break

if column_alphabet is None:
    print("Column with 'Clean' not found.")
else:
    Email_index = 1
    Email_reference = f"{column_alphabet}{Email_index}"
    print(Email_reference)
    default_email_prefix = "@gmail.com"
    assigned_person_email_name = worksheet.acell(Email_reference).value
    assigned_person_email = assigned_person_email_name + default_email_prefix
    print(assigned_person_email)

Once we identify the person from the Google Sheet, the script will send an email to them via Gmail. Additionally, I’ve embedded a button in the email content, allowing the recipient to easily create a reminder in their Google Calendar with a single click.

 if assigned_person_email is None:
        print("Assigned person's email not found.")
    else:
        # Create a function to generate the Google Calendar event link
        def generate_google_calendar_link():
            event_title = "Cleaning Task Reminder"
            event_description = "Kind reminder,You have been allocated to do the Cleaning Task for tomorrow"
            event_start_date = tomorrow.strftime('%Y%m%d')  # Format as YYYYMMDD
            event_start_time = "030000"  # Start time is 03:00 AM in UTC
            event_end_time = "040000"    # End time is 04:00 AM in UTC

            google_calendar_link = f"https://www.google.com/calendar/render?action=TEMPLATE&text={event_title}&details={event_description}&dates={event_start_date}T{event_start_time}Z/{event_start_date}T{event_end_time}Z"
            return google_calendar_link

        # Send an email to the assigned person
        sender_email = os.environ.get("emailaddress")
        sender_password = os.environ.get("emailpass") 

        message = MIMEMultipart()
        message['From'] = sender_email
        message['To'] = assigned_person_email
        message['Subject'] = 'Cleaning Task Reminder Tomorrow'

        google_calendar_link = generate_google_calendar_link()
        button_html = f'<a href="{google_calendar_link}" target="_blank"><button style="background-color: #2196F3; color: white; border: none; border-radius: 4px; padding: 10px 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 16px; margin: 4px 2px; cursor: pointer;">Add to Google Calendar</button></a>'
        body = f'Hello,<br> Kind reminder,You have been allocated to do Cleaning Task for tomorrow.<br> {button_html} <br><br> <i>This is an auto-generated email.</i>'
        message.attach(MIMEText(body, 'html'))

        server = smtplib.SMTP('smtp.gmail.com', 587)
        server.starttls()
        server.login(sender_email, sender_password)
        server.send_message(message)
        server.quit()

Steps 2 : Deploy the Python Script in WSO2 Choreo

Next, we’ll deploy the Python script using WSO2 Choreo. Start by navigating to the Choreo home page and selecting your project. Within the project, you’ll need to create a Scheduled Task component, which will handle the automation of running the script at predefined intervals.

Now you will get redirect to a page where you need to give a name to your Scheduled task and a description for it

After configuring the Scheduled Task, you’ll need to provide the URL of your public repository or connect directly to GitHub. In this case, I’ll be using the repository URL: https://github.com/isurul28/CleanTaskChoreo.

Next, we need to select the appropriate Build Pack. In this case, we’ll choose the Python Build Pack. Then, specify the directory where the profile file is located and select the Python version you’d like to use for building the script. Finally, click the Create button to complete the process, and this will create the scheduled task in Choreo.

After its created you will be redirect to below over view page of the created Scheduled task

Build :

Now it’s time to build our project. To do this, navigate to the left-side menu where you’ll find the Build option, as shown below:

In the Build page click on Build latest button to build the python project.

After you clicked it, the project will start to build and you can track the progress by view Build details below

You will see successful status once the build is completed

Before we deploy the project to Choreo, we need to create a Google Cloud project to enable the Google Sheets and Google Drive APIs, which are essential for our Python script to function properly.

STEP 3 : Configuring Google APIs

• Go to Google Cloud Console and select your project. If you haven’t created a project yet, you can easily set one up.
• After creating the project, navigate to the left-side menu, click on APIs & Services, and then select Library.

In the Library view you can search for Google Sheet API and Google Drive. Enable these API for your project.

After enabling them in your project you need to go to the left side menu and select IAM & Admin >> Service Accounts.

Create a Service Account by following the prompts in the Google Cloud Console. For this project, I’ve named my service account “TicketScan.” This service account will be used to invoke the Google APIs we enabled in the API Library.

Please note that a unique email address will be generated for the service account. To grant the service account access to your Google Sheet, you must share the sheet with this email address. Only then will the service account be able to read the Cleaning Time table.

Next, we need to obtain the key file for this service account, which will be referenced as /credentials1.json in our Python code. Here’s how to do it:

1. From the left-side menu, navigate to Service Accounts and click on the service account you created.
2. Go to the Keys section and select Add Key > Create New Key.
3. When prompted for the key file format, choose the JSON option. This will download a JSON file to your machine. Rename this file to credentials1.json.

Now, to send emails, you’ll need a Gmail account. However, you cannot use your regular Gmail password within the Python project to send emails to the cleaners. Instead, follow these steps to create an App Password:

1. Go to your Google Account settings.
2. Navigate to Security.
3. Under Signing in to Google, find App passwords and create a new App Password.
4. Make sure to note down this password, as it will be used as the email password when deploying to WSO2 Choreo.

Deploy :

Now it’s time to deploy the built project. To do this, navigate to the left-side menu and click on Deploy. In the deployment menu, you will see two environments: Development and Production.

Next, click on the Configure & Deploy option. A window will pop up on the right side, allowing you to configure environment variables for your project.

Since our project requires the following environment variables, we will add them now:

• emailaddress
• emailpass

When entering the emailpass, which you obtained from the Google App Password, make sure to check the Secret option. This will encrypt the email password for enhanced security.

Click Next, and you will be prompted to mount a file. For this step, add the key details you obtained from the Google Service Account and save it as /credentials1.json. Remember to mark it as a Secret as well, since it contains sensitive information.

After this, click Next, and you will arrive at the Scheduled Task Configuration view, where you can configure the following settings:

• Time Zone
• Triggering Frequency of the task
• Job Timeout

Alternatively, you can provide a custom cron expression.

Once you’ve configured these settings, you can deploy the scheduled task to the Development environment. In my setup, the scheduled task is configured to run every day at 9 AM Dubai Time.

But wait — it’s currently 10:25 AM. Do I really have to wait until tomorrow to check if the scheduled task is working correctly?

Absolutely not! You can manually trigger your scheduled task by navigating to the Execute option in the left-side menu.

In the Execute view, you will see the Run Now button at the top right corner. Go ahead and click it! We’ve all been waiting for this moment. Once you press Run, you’ll notice a new execution entry starting to appear in the execution list below.

You can click on the execution and it will give a left side popup window as below

Click on view log and it will give the full log what happen during the execution of the python project as below

Now, let’s check who received the email reminder. Today is 06/08/2024, and tomorrow will be 07/08/2024. According to our Google Sheet, Isuru Liyanage (ME) is scheduled for cleaning duty tomorrow.

Let’s check my email inbox to see if I’ve received the reminder. As you can see, I have received the cleaning reminder email, as shown below:

By clicking the Add to Google Calendar button, I can easily add a reminder to my Google Calendar, ensuring I won’t forget it.

This is how I created an email reminder task using WSO2 Choreo, Google APIs, and Python. I hope this guide helps someone out there.

Happy coding!