Cristhian Amaya

I make and break stuff in equal measure.

How I helped a company to save time using Gmail, Trello and Python

2017 Apr 15

TL;DR

I created a script that creates Trello cards from Gmail emails and it helped a company to save time by automating their information requests process. You can find the code on Github: gmail-to-trello.

Background

During the last couple of months, I've been helping a friend to automate some tasks of his business. They've got an online shop where they "sell" tourism packages. I put sell in quotes because they don't actually sell directly through the website. The people who are interested in booking something have to send a booking request using a contact form and a person would arrange everything for them.

When the users fill in the form, an email is sent to a particular email address with all the information. When they got an email, they forwarded it internally to the appropriate person to handle it.

Contact Form

This approach works ok but it had a big problem for them:

They get about 30/40 requests per day and every request can last days, weeks or even months so it is, at a minimum, uncomfortable to manage all of that with only a mailbox.

The first step was to find a way to manage all the requests easily and here's where I introduced Trello. There are multiple resources on how to manage a sales team with Trello and after reading a bit about that, I come up with a board like this:

Contact Form

After the Trello board was in place, the workflow changed a bit: after receiving a request by email, they had to create a Trello card manually and assigned it to the right person. The problem with this is that creating cards manually is time-consuming and error prone.

To avoid creating the cards completely manually we could have used this awesome Trello feature: Creating cards by email, though they would've still needed to do something manually. For a few emails a day that wouldn't be a problem, but when we talk about maybe 40 emails per day, it is a problem.

The other problem with that approach is that the card's description would be exactly the email's body and we didn't want that because the cards would be bloated as you can see in the following image:

Contact Form

We also could've used Zapier or IFTTT to create the cards. That would've saved the trouble of sending the emails to the Trello board, but we would've still had the issue with the bloated content.

Here's where Python comes to the court. The idea was to create a script to do the following:

  1. Fetch the information requests from the mailbox, Gmail in this case.
  2. From each email, extract just the information we actually want on each Trello card.
  3. Format the information we extracted from the emails.
  4. Create the Trello card for each email.
  5. Add a label to the emails that are added to the Trello board. This helps me to avoid fetching them over and over again.

The ingredients to do this are the Gmail API, the Trello API and Python. Let's get into the fun part, the code.

Code

Fetching emails from Gmail

This uses the Gmail API to fetch the emails. The q parameter is very handy to filter the fetched emails and avoid getting emails I don't want.

def get_messages_matching_query(service, q=None):
    response = service.users().messages().list(userId='me', q=q).execute()

    messages = []
    if 'messages' in response:
        messages.extend(response['messages'])

    while 'nextPageToken' in response:
        page_token = response['nextPageToken']
        response = service.users().messages().list(userId='me', q=query,
                                                   pageToken=page_token).execute()
        messages.extend(response['messages'])

    return messages


def get_mime_messages(service, gmail_msgs):
    mime_msgs = {}

    for m in gmail_msgs:
        gmail_id = m['id']
        message = service.users().messages().get(userId='me', id=gmail_id,
                                                 format='raw').execute()
        msg_bytes = base64.urlsafe_b64decode(message['raw'].encode('ASCII'))
        msg_str = str(msg_bytes, 'utf-8')
        mime_msg = message_from_string(msg_str)
        mime_msgs[gmail_id] = mime_msg

    return mime_msgs

Extracting information from each email.

I used regular expressions to extract only the information I wanted.

PATTERNS = {
    'request_date': r'Fecha:.*\n.*<TD.*?>(.*)</TD>',
    'client_name': r'Cliente.*\n.*<TD.*>(.*)</TD>',
    'client_phone': r'fono.*\n.*<TD.*>(.*)</TD>',
    'client_email': r'E-mail:.*\n.*mailto:(.*)\"',
    'client_city': r'Ciudad:.*\n.*<TD.*?>(.*)</TD>',
    'check_in': r'Fecha de entrada:.*\n.*<TD.*?>(.*)</TD>',
    'check_out': r'Fecha de salida:.*\n.*<TD.*?>(.*)</TD>',
    'number_people': r'mero de personas:.*\n.*<TD.*?>(.*)</TD>',
    'product': r'Producto.*\n.*<TD.*>(.*)</TD>',
    'inquiry': r'Consulta:.*\n.*<TD.*?>(.*)</TD>'
}

def extract_card_info(message_content):
    card_info = {}
    for key, value in PATTERNS.items():
        match = re.search(value, message_content)
        value = match.group(1) if match else ''

        if key == 'inquiry':
            value = value.replace('<BR>', '\n')

        card_info[key] = value

    return card_info

Formatting the extracted information

The template is a text file with the format I want.

PROJECT_DIR = os.path.dirname(os.path.realpath(__file__))
ASSETS_DIR = os.path.join(PROJECT_DIR, 'assets')

def get_formatted_card(template, card_info):
    card = {}
    card['name'] = '{0} - {1}'.format(card_info['client_name'], card_info['product'])
    card['description'] = (template
        .replace('#PHONE#', card_info['client_phone'])
        .replace('#EMAIL#', card_info['client_email'])
        .replace('#NUMBER_PEOPLE#', card_info['number_people'])
        .replace('#CHECK_IN#', card_info['check_in'])
        .replace('#CHECK_OUT#', card_info['check_out'])
        .replace('#INQUIRY#', card_info['inquiry']))
    return card


def load_template(name):
    path =  os.path.join(ASSETS_DIR, name)
    with open(path, 'r') as f:
        return f.read()

Creating the Trello card.

This uses the Trello API to create the card.

BASE_URL = 'https://api.trello.com/1/'

def create_card(name, description, list_id, labels_ids=None):
    endpoint = 'cards'
    query_string = {'name': name, 'desc': description, 'idList': list_id}

    if labels_ids:
        query_string['idLabels'] = ','.join(labels_ids)

    r = _post_api_response(endpoint, query_string)
    return r.json()


def _post_api_response(endpoint, query_string=None):
    url = BASE_URL + endpoint
    params = _get_params(query_string)
    r = requests.post(url, params)
    return r


def _get_params(query_string=None):
    params = {
        'key': API_KEY,
        'token': OAUTH_TOKEN
    }

    if query_string:
        params.update(query_string)

    return params

Adding a label to an email added to the Trello board.

Again, this uses the Gmail API to add the label.

def set_message_label(service, msg_id, label_name):
    labels = get_labels(service)
    label = next((l for l in labels if l['name'].upper() == label_name.upper()), None)

    if label is None:
        label_object = make_label_object(label_name)
        label = create_label(service, label_object)

    msg_labels = {'removeLabelIds': [], 'addLabelIds': [label['id']]}
    service.users().messages().modify(userId='me', id=msg_id,
                                      body=msg_labels).execute()


def get_labels(service):
    response = service.users().labels().list(userId='me').execute()
    return response['labels']


def create_label(service, label_object):
    label = service.users().labels().create(userId='me',
                                            body=label_object).execute()
    return label


def make_label_object(label_name, mlv='show', llv='labelShow'):
    label = {
        'name': label_name,
        'messageListVisibility': mlv,
        'labelListVisibility': llv
    }
    return label

Finally, this is how everything is glued together.

def main():
    trello.API_KEY = settings.TRELLO['api_key']
    trello.OAUTH_TOKEN = settings.TRELLO['oauth_token']

    json_keyfile_path = os.path.join(ASSETS_DIR,
                                     settings.GMAIL['json_keyfile_name'])
    gmail_svc = gmail.get_service(settings.GMAIL['account'], json_keyfile_path)

    q = 'from: {} '.format(settings.GMAIL['from_account'])
    q += 'to: {} '.format(settings.GMAIL['to_account'])
    q += 'subject: {} '.format(settings.GMAIL['subject'])
    q += '-label: ' + settings.GMAIL['trello_label']
    q += 'after: ' + datetime.date.today().strftime('%Y/%m/%d')
    msgs = gmail.get_messages_matching_query(gmail_svc, q)

    if msgs:
        mime_msgs = gmail.get_mime_messages(gmail_svc, msgs)
        trello_card_template = load_template('card_template.txt')

        cards_created = 0
        for key, value in mime_msgs.items():
            msg_content = value.get_payload(decode=True).decode('utf-8')
            info = extract_card_info(msg_content)
            card = get_formatted_card(trello_card_template, info)
            trello.create_card(card['name'], card['description'],
                               settings.TRELLO['list_id'],
                               settings.TRELLO['labels_ids'])
            gmail.set_message_label(gmail_svc, key, settings.GMAIL['trello_label'])
            print('card {} successfully created.'.format(card['name']))
            cards_created += 1

        cards_label = 'cards' if cards_created > 1 else 'card'
        print('{} {} has been created.'.format(cards_created, cards_label))
    else:
        print('There are no new messages')

Here are a couple of images of how the board looks after this script runs.

Cards after script

Final card details

The script is hosted on an Ubuntu Server and there's a cronjob that executes it every 5 minutes. This solves the two issues I had, the cards are created completely automatically and they have exactly the information I need.

Summary

Creating the Trello board to manage the requests was a life changer for them and the automation definitely made their life even easier. Now they spend their time on the right tasks, giving a great customer support.

You can find all the code on Github: https://github.com/camaya/gmail-to-trello. Keep in mind that this is my first real usage of Python so feel free to point me out any mistakes or improvements, I'd really appreciate it.

If you have any comment, suggestion or question you can reach me on Twitter @_camaya or you can send me an email to <cam at camaya.co>.

Cheers.