Creating an out-of-office reminder system

There are several ways to manage a team. In the modern start-up era, it is not uncommon to see HR departments replacing the old, stricter model, with a "responsible freedom" approach. Netflix popularized the principle of treating employees as fully-formed adults.

In order to have such autonomy in place, automations can be implemented, ensuring that your team is able to run independently without concealing relevant information from each other.

Consider an out-of-office system where each employee is responsible for picking their days off. Based on Netflix's principles, there is no need to strictly oversee such task, especially if some basic rules are respected (say, you should book your day off at least one week in advance, no more than X people from the team can be off at the same time, etc).

A simple system can be easily implemented with a shared Google Calendar, Slack notifications, and Pipedream as the automation engine. Today, I'll show how you can:

1. Notify your team whenever someone book some time off;
2. Notify who is out for the day.

Let's get started!

Notifying new entries in the calendar

Start by creating a new workflow and searching for Google Calendar as trigger. A few options will show up, but for now let's select New Event Created.

You will need to connect your Google Calendar account (select the account which has access to the shared calendar).

Next, choose the frequency Pipedream will check if a new event has been created. Pick what works best for you, but as we are not aiming for real-time here, 15 minutes should be more than enough.

Finally, in Calendar ID you will be presented with a dropdown to select which calendar will be monitored. Pick the shared calendar and you are good to go!

After creating the source, you will be prompted to select a listened event in order to test the automation. Go ahead and create a new event in the calendar. Give it a few moments and the event should pop out! Select it and you will be able to inspect all the details.

Generating a Slack message

The data from the trigger step is all we need to build the notification. In particular, we want to know who is out, when they will be out, and how long until they are out.

Let your creativity guide you through this step. All three values can be generated with a simple Python script – hence connect a new Python step to the trigger and add the following code:

from datetime import datetime, timedelta, timezone

def parse_datetime(date_str):
    try:
        dt = datetime.fromisoformat(date_str)
    except ValueError:
        dt = datetime.fromisoformat(date_str + "T00:00:00+00:00")
    return dt.astimezone(timezone.utc)

def count_weekdays(start_date, end_date):
    days = (end_date - start_date).days
    weekdays = 0
    last_weekday = start_date
    for i in range(days + 1):
        day = start_date + timedelta(days=i)
        if day.weekday() < 5:  # Monday = 0, Friday = 4
            weekdays += 1
            last_weekday = day
    return weekdays, last_weekday

def handler(pd: "pipedream"):
    event = pd.steps["trigger"]["event"]
    creator = event["creator"]["email"]

    start = event["start"].get("dateTime", event["start"].get("date"))
    end = event["end"].get("dateTime", event["end"].get("date"))
    start_dt = parse_datetime(start)
    end_dt = parse_datetime(end)
    now = datetime.now(timezone.utc)

    duration_days, last_weekday = count_weekdays(start_dt.date(), end_dt.date() - timedelta(days=1))
    days_until = (start_dt.date() - now.date()).days
    start_date_formatted = start_dt.strftime('%A, %B %d')
    last_weekday_formatted = last_weekday.strftime('%A, %B %d')

    if days_until == 0:
        time_phrase = "Today"
    elif days_until == 1:
        time_phrase = "Tomorrow"
    else:
        time_phrase = f"In {days_until} days"

    if "dateTime" in event["start"] and start_dt.date() == end_dt.date():
        # Case 1: The creator is out only for some hours within the same day
        if days_until <= 1:
            slack_message = f"{time_phrase}, {creator} will be out of the office from {start_dt.strftime('%H:%M')} UTC to {end_dt.strftime('%H:%M')} UTC."
        else:
            slack_message = f"{time_phrase}, {creator} will be out of the office on {start_date_formatted} from {start_dt.strftime('%H:%M')} UTC to {end_dt.strftime('%H:%M')} UTC."
        event_type = "only during the day"
        duration = 1
    elif "dateTime" not in event["start"] and start_dt.date() == end_dt.date() - timedelta(days=1):
        # Case 2: The creator is out for a single full day
        slack_message = f"{time_phrase}, {creator} will be out of the office."
        event_type = "only during the day"
        duration = 1
    else:
        # Case 3: The creator is out for more than one day
        if "dateTime" in event["start"]:
            slack_message = f"{time_phrase}, {creator} will be out of the office from {start_date_formatted} at {start_dt.strftime('%H:%M')} UTC to {last_weekday_formatted} at {end_dt.strftime('%H:%M')} UTC ({duration_days} days)."
        else:
            slack_message = f"{time_phrase}, {creator} will be out of the office from {start_date_formatted} to {last_weekday_formatted} ({duration_days} days)."
        event_type = "more than one day"
        duration = duration_days

    output = {
        "creator": creator,
        "start": start,
        "end": end,
        "duration": duration,
        "days_until": days_until,
        "event_type": event_type,
        "slack_message": slack_message
    }
    return output

Although the code is slightly big, there is nothing complicated going on. We are basically just formatting the message in a friendly manner so we highlight if the event is near. For example, we say "Today, john@company.com is out of the office", or "Tomorrow, jane@company.com is out of the office". If the event is farther away, the output is "In X days, mary@company.com will be out of the office".

The script also handles the cases when the person will be out for only part of the day, and when they will be out for many days in a row. Take some time to read through the code and make sure to adjust the wording to your needs!

Sending the Slack message

The Python step conveniently returns a dictionary with relevant values allowing for a finer control of the next steps. For now, let's simply add a new step, search for Slack, click on Send Message to a Public Channel, connect your Slack Account, select the Channel ID and add the message generated in the previous step.

And that's it! Publish your workflow, create a new event in the shared calendar and in up to 15 minutes you will see the notification in Slack.

There are many enhancements to be made. For example, the days_until variable returned by the Python step can be used to conditionally trigger a new workflow. Suppose you have a hard rule that no time off can be booked with less than 5 days in advance. Then you can leverage the Continue based on condition step to send a warning, or even to delete the event from the calendar.

Listing everyone out for the day

Notifying when someone book time off is progress, but it's not uncommon that a long-planned trip will be forgotten by the time it takes place (suppose you book a week off with two months in advance. When it's finally time, nobody will remember you are out).

Let's build another workflow, this time triggered at a fixed time on workdays. It will list all events in the calendar for that day and send a Slack message informing who is out. You can set it to run at the beginning of the day, so by the time your team show up to work there is a message letting them know who is unavailable.

Triggering the workflow

Start by creating a new workflow and selecting Custom Interval as trigger. Next, select the time that better suits your team schedule.

In Results you can see the output, in particular the exact timestamp of when the workflow was triggered – this will be useful to restrict the search within Google Calendar's API.

Generating the boundaries

We don't want to list all events in the calendar, just the ones for that day. For example, if the event triggered on 2024-04-26T09:00:00+00, we want to list entries between 2024-04-26 and 2024-04-27 (we'll talk about timezones in a moment).

Let's use a Python script to get those values. Hook up a new step to the trigger and add the following code:

from datetime import datetime, timedelta, timezone

def handler(pd: "pipedream"):
    # Get the timestamp from the trigger event
    timestamp = pd.steps["trigger"]["event"]["timezone_configured"]["iso8601"]["timestamp"]
    
    # Parse the timestamp into a datetime object
    dt = datetime.fromisoformat(timestamp)
    
    # Convert the datetime to GMT (UTC)
    dt_gmt = dt.astimezone(timezone.utc)
    
    # Get the current day at midnight GMT
    min_time = dt_gmt.replace(hour=0, minute=0, second=0, microsecond=0)
    
    # Get the next day at midnight GMT
    max_time = min_time + timedelta(days=1)
    
    # Format the min_time and max_time as ISO 8601 strings
    min_time_str = min_time.isoformat()
    max_time_str = max_time.isoformat()
    
    # Return the dictionary with min_time and max_time
    return {
        "min_time": min_time_str,
        "max_time": max_time_str
    }

The code is simply getting the timestamp of the triggered event, converting it to UTC, setting it to midnight (lower boundary) and then adding 1 day to it (upper boundary).

Listing all events

Add a new step and, as before, search for Google Calendar. This time, however, select List events.

Note that the values returned in the previous step are used as Min time and Max time, and that the Time Zone matches the one used during the data transformation.

Click to test and, if there is any event for the current day in the calendar, you will be able to see it.

Generating the Slack message

Let's use the same principle of the first example and use another Python script to generate a Slack message. Add a new step, select Python and add the following code:

def handler(pd: "pipedream"):
    events = pd.steps["list_events"]["$return_value"]["items"]
    people_off = [event["creator"]["email"] for event in events]
    num_people_off = len(people_off)

    if num_people_off == 0:
        message = "Good morning! No one is out today."
    elif num_people_off == 1:
        message = f"Good morning! Today {people_off[0]} is out."
    elif num_people_off == 2:
        message = f"Good morning! Today {people_off[0]} and {people_off[1]} are out."
    else:
        message = f"Good morning! Today {', '.join(people_off[:-1])}, and {people_off[-1]} are out."

    return {"num_people_off": num_people_off, "message": message}

This short script is all you need to generate a helpful message to your teammates!

Conditionally sending a Slack notification

Finally, let's notify the team, but only if there is people off. Fortunately, the num_people_off return value can be used for it.

Select the option Continue based on condition for the next step and configure it like this:

Now, any subsequent step will only run if the condition is met. That's exactly what we need, and the final act is to send another message to a public channel.

And that's it! Test the workflow and you should receive a message like this:

There are several other calendar apps that can be integrated using Pipedream. They are just a few clicks away from powering your automated workflows.