How to Mail Merge with Python

If you don’t have Microsoft Outlook, there are loads of Gmail add-ons you can use for mail merge. In fact there are so many that a popular one is called Yet Another Mail Merge.

But they typically have a learning curve. And it looks like a lot of them require some kind of Chrome extension or some other gobbledygook to get it to work.

I don’t want to spend time figuring out how to use someone else’s software when I know I can do it on my own in about as much time.

Let’s break mail merge down into its component parts…

  • a text document (or email body) which is populated with data from a
  • CSV or Excel file (or a database if ya want)
  • an email, sent for each row of the above data, with content populated by the data

Those are all things you can code up on your own with Python efficiently, especially if you lean on a couple of libraries.

Email Body

To craft your messaging, use a triple-quote string so that spacing will be preserved. Each part that will be populated by data can be replaced with a “{}” (i.e. curly brackets). You’ll use Python’s string.format() method to fill in the fields from the dataset.

Data Fields

You can import the csv library to and use it to pull your data fields from a .csv file, like so:

Simple Mail Transfer Protocol (SMTP)

For this, you’ll need to import smtp. This uses your own Gmail account, and sends an email from it to the specified recipient, with subject and body specified. You will probably need to adjust the setting in your Gmail account to allow less secure apps to access it.

For each row in your data, you can use a function like this:

Put It All Together!

The full code would look something like this. I’ve modularized the CSV function and the mailer function into two separate Python files, named CSV_actions and mailer1, respectively, since I use them in other programs.

That’s it! A super simple mail merge, with no fees or sign-ups needed.

Teaching My First Improv 101 Class

Last Friday the Improv 101 class I’ve been teaching had their class show. They absolutely crushed it. I’m so proud of them. I’m lucky to have taught such a fun, smart, and just plain nice group of folks. Thank you Brandon, Frank, Allie, Jennifer, Jeff, Thaddeus, Rose, McNeil, Maggie, and Olivia. You’re all improvisers.

If you’re in the Durham area, check out an improv class!

Navigate a List of URLs with URList

I recently published two new Chrome extensions, one of which is URList. It allows Chrome users to navigate their own list of URLs one at a time.

What is this for?

At work lately I’ve had a few tasks that involved manually checking a list of URLs from an Excel file. I was copying and pasting them one by one from Excel into the URL bar, which is just as lame as it sounds. I figured there must be some Chrome extension I can use to speed things along. I wanted something where I could paste all the URLs at once and navigate to each by clicking a button.

The closest solutions I found were extensions that opened a new tab for each URL, but that is a massive waste of RAM. If I need to peruse a dozen (let alone several dozen or several hundred) URLs, it would be ridiculous to use all that memory up front. I wanted a “just in time” solution.

How it Works

So I set to work on URList. Once you’ve added it to Chrome, you can type or paste your list of URLs into the text box and click “Start” to save them and then click “Next URL” to navigate to each web page in the list in the order you added them.

While that is the basic function of it, I added a couple more enhancements. First, I wanted a little bit of data persistence. To that end, the extension saves the URLs to localStorage so that you can exit Chrome and come back to your list later. It will pick up right where it left off in the list.

I also added a “Hide List” button, which allows users to view only the navigational buttons, and made it so that users can enter their URLs in multiple ways.

Use Cases

Use cases for this extension go beyond what I designed it for. I could see folks using it to quickly navigate through their favorite news sites, for example. The URLs are saved in localStorage, so they will already be there. I could also see it being used for a quick, browser-based slide show.

End Notes

The nice thing about this project is that it provided me a refresher for core Javascript. Lately at work I’ve been using Python quite a bit and not nearly as much Javascript as I was a few months ago.

Try it out! Give it a good review if you like it. You can find it in the Chrome store here.

The other Chrome extension I published recently is WikiPik, which displays images for Wikipedia articles that don’t have any.

Quizzly Updates

I’ve been working on other projects, including a dating app, but I recently decided to make improvements to the site I launched last year.

Last year I wanted to make a website that does one thing very well. With that in mind, I launched Quizzly – a site that people can use to make their own multiple choice quizzes. I knew that there were already a lot of quiz sites out there, but in my opinion they were all aimed at personality quizzes. Fun, if you’re into that kind of thing, but highly subjective.

I wanted to make something that teachers, students, and anyone else can use as a study aid. More in that realm lie sites like Sporcle, which are fantastic for trivia. They offer a wide variety of quiz types, but the interface is a little crowded in my opinion.

I wanted something clean and minimal, so there would be no distraction while taking quizzes. And again, I wanted something that did one thing very well, which to me meant having only one quiz type – multiple choice.

Last Fall, I believed I had a product that wasn’t perfect, but it worked well. Users could make their own multiple choice quizzes and users could take them, get a score, and see which answers they missed.

I then completely ignored the site for about six months. I worked on other projects, one of which is a dating app that matches based on the bands you like. More on that project soon.

As Quizzly started to get more traffic and people made their own awesome quizzes, I took another look at it and felt it needed some updates. So I made a list, started the local server, and got to work. The changes include, but aren’t limited to:

Switching to HTTPS

Making the site more secure was the top priority. While not necessary for static sites (this blog, for instance) Quizzly has user logins and user-generated content. Furthermore, most browsers flag pages delivered without HTTPS and give users scary sounding messages. You don’t want people to be scared of your website. To fix this, I purchased an SSL certificate, changed the domain name servers, and in my application server, redirected all requests for HTTP to HTTPS.

Switching to non-WWW

This was purely an aesthetic choice. Most site visitors probably wouldn’t notice whether the URL starts with “www” or not, but to me it looked ugly. The TLD is “.co”, which looks like the site is going for the whole brevity thing. Having the “www” countermanded that, so I decided to switch to having a non-www, or “naked,” domain.

Style Improvements

While I wanted something minimal, I didn’t want it to look boring. The style improvements were pretty minimal, and in fact this is an area that will get more attention. The trick here is for the site to look like something that users can interact with.

User Management

Stormpath was a good user management service, and Quizzly relied on their API for user authentication and management. They shut down their API yesterday, because they joined Okta. I checked out Okta but it looked pricey. I ended up choosing Firebase, a Google product, for user authentication and management. User management and authentication is an area where you shouldn’t reinvent the wheel if you don’t have to.

SEO Edits

I took a tip from the big ecommerce sites and added canonical link tags on my sort pages. Every quiz topic, i.e. Astronomy or Film, on my site has 10 URLs associated with it, each of which renders the quizzes in that topic using a different sort method. Now, all 10 of those topic pages reference a single sort page. That gives the search engines a better idea of which sort page to index.

Quiz Images

The original way users added an image to their quiz was by via an image URL. I stored the URL in my database and rendered the quiz with that exact URL. That is a bad idea. I admit it was just a quick hack because I didn’t yet want to spend time figuring out where to host images. Now, users upload images from their own device, they are stored in the cloud, and delivered via CDN. I’m using Cloudinary for image storage and couldn’t be happier with it.


Spaces are fine in URLs, but they do render as “%20”. In order to make the links to my site more human-readable, I wanted to make sure that all spaces were replaced with a dash. I couldn’t just write a redirect, because my database queries come from the URL. A query for a quiz with the spaces changed to dashes would fail.
I likewise couldn’t change the URL’s dashes to spaces just for the query because of the cases in which a quiz title is supposed to have a dash in it. As in, a real dash. Not a slug dash.
First, I wrote a function that turns each user’s quiz title into a slug. A quiz titled, “Hemingway’s Novels”, for example, would have the slug, “hemmingways-novels”. I did the same for all user topic tags. For the tags, I added a “tag” collection in my database so that tags can be queried faster, without needing to use any aggregation pipeline to dig through the “quizzes” database. The tag collection consists only of tag names, e.g. “American Authors”, and their corresponding slugs, e.g. “american-authors”.

Westworld Podcast – New Episode!

I’ve been a frequent guest on my friend Craig Carter’s Westworld podcast over the past year or so. He started this project with Heather Barefoot and Jonathan Yeomans, and Jonathan is the guest in the episode that came out today.

They talk about the three new Westworld cast members and the fact that Jimmi Simpson will return next season. That means more scenes from the past, which is intriguing. They also give a preview of next week’s discussion of Jurassic Park, which has a lot in common with Westworld, namely that it is about a theme park that goes haywire, causing the visitors to become prey.

I love this podcast, and not just because I’m on it. It has some great interviews with actors from the show, original player piano music from Alex Thompson, and reviews of other titles in the Western and Sci-Fi genres. Its been super fun working with Craig on this project, as well as the other guests of the show (Brian Sutorius, Heather Barefoot, Wil Heflin, and Jonathan Yeomans).

If you’re looking to sate your Westworld fix for the long haul between now and season 2, then this is the podcast for you.

Palindrometer: The Twitter Bot that Finds Palindromes

I wrote a bot recently that searches tweets for palindromes. A palindrome is any word, phrase, or sequence of numbers that is the same when written backwards. The number 101, the town of Wassamassaw, SC, the word “madam”, and the band name ABBA are all palindromes. The most famous one is, “A man, a plan, a canal – Panama.”

This isn’t the first Twitter bot I’ve written, but it is the first one that I feel is interesting enough to share. You can take a look at it on Twitter to see what it is up to.

I set it up so that it only finds multi-word palindromes (so “Hannah,” “Anna,” “mom”, and “dad” are all out unless they are part of a larger palindromic phrase) and they must be 9 characters or longer, excluding spaces. That way its activity is somewhat throttled and the quality of palindromes found is higher. Theoretically. This is Twitter we’re talking about.

Why is this something that exists?

Purely for fun. Given enough time, the bot could find the next, “A man, a plan, a canal – Panama.” That would be pretty cool. Since I last checked it this morning it has retweeted tweets that include:

  • “forever of”
  • “never even”
  • “did it, I did”
  • and my favorite, “dammit I’m mad”

For now I hardcoded those into the bot so that it doesn’t repeat them, but when I get to it I will hook a database up to the bot so that it can add found phrases to the database and then check new ones against that set so it doesn’t repeat itself.

How it works

The fun part for me was writing the code that parses tweets and then finds symmetry across multiple words in the tweet. First, the bot parses each Tweet it can get (it can’t get all Tweets) by removing any punctuation, multiple spaces, and capital letters. That leaves it with just the words and numbers in the tweet.

Next it puts each word or number into an array, and from that array creates a new array of every possible combination of two or more sequential words or numbers. For example the 4 word tweet “hey what is new,” would be broken up into these 6 segments: “hey what,” “hey what is,” “hey what is new,” “what is,” “what is new,” and “is new.”

The bot then runs a function on each segment that looks for symmetry. That function, as you might have guessed, starts with the first and last character of each segment and works its way to the middle character (or pair of characters if the segment contains an even number of total characters) checking for matches. If they all match, then there is symmetry in that segment and the bot has found a palindrome.

Embeddable Quizzes

I made a small update* to Quizzly today that will make it much easier for bloggers to embed quizzes into their site. Let’s say I have a blog about coffee. I can embed a quiz into my blog post, which will make it more interactive and thereby increase the amount of time visitors spend on my site – an important engagement metric. After a few paragraphs of content about coffee, I might place the quiz here:

At this point in your blog post it is a good idea to engage with your visitors about their score. Encourage them to post their scores or discuss the quiz in the comments. You can create quizzes specifically for your blog post on my quiz maker site.

*Specifically, I added in a few lines of JavaScript to the quiz pages that checks if it is in an iframe, and if it is, removes all other page elements except the quiz itself. I also added a “get embed code” button at the end of each quiz.

Adwords Script for Limiting Monthly Spend

I want to share this Adwords script I wrote a few months ago because I thought it might help some account managers who were having a similar problem. Adwords allows you to set daily budgets for your campaigns, but there is not a way to set a monthly budget.

There are some features that come close, but in my opinion don’t quite do the trick. For example, there is Manager Defined Spend (MDS) but that is only useful for agencies that manage multiple accounts, and even then it might not be the ideal method. The shared budgets feature lets you set monthly limits for campaigns that share a budget, but what if you don’t want to use a shared budget? Nine times out of ten you will want to allot varied daily budgets to your campaigns, so that you can reward the high converters with a greater share of the budget.

I wrote the script below to add what I consider to be a basic feature to Adwords. With this bit of code, you simply provide your monthly budget and Adwords will pause all active campaigns in the account if their total spend month-to-date meets that number. Then, on the first day of the next month, it will enable those campaigns once again.

To use this script, copy and paste it into your Adwords account under Bulk Operations >> Scripts >> New Script.

//Author: Jake Ratliff
//April 14, 2016


//NOTE: set MONTHLY_BUDGET to a number slightly less than
//your actual monthly budget and set this script to run
//hourly. Setting this variable to less than actual budget
//will keep you from going over between hours.

//This is the main function, which Adwords calls when the
//script is run, so it must be named main. In our main
//function we are logging the total cost month-to-date,
//checking if that number is greater than the specified
//budget, and if it is, we apply a label to all active
//campaigns and then pause all active campaigns. Finally
//we check if it is the first day of the month, and if it
//is, we re-enable all the campaigns that have the label
//that we applied earlier.

function main() {
    var itsFirstOfTheMonth = ((new Date()).getDate() == 1);
    var totalCostMTD = getTotalCost().toFixed(2);
    Logger.log("Total cost this month: $" + totalCostMTD +
        "; monthly budget: $" + MONTHLY_BUDGET

    if (totalCostMTD >= MONTHLY_BUDGET) {
        Logger.log("spend has reached monthly budget");

    if (itsFirstOfTheMonth) {


function getTotalCost() {
    var campIter = AdWordsApp.campaigns().get();
    var totalCost = 0;
    while (campIter.hasNext()) {
        totalCost +="THIS_MONTH").getCost();
    return totalCost;

function applyLabel() {
    var labelName = 'Active Last Month';

    var campaignIterator = AdWordsApp.campaigns()
        .withCondition('CampaignStatus = ENABLED')
    while (campaignIterator.hasNext()) {
        var campaign =;
    Logger.log('labels applied.');

function pauseCampaigns() {
    var campaignIterator = AdWordsApp.campaigns()
        .withCondition('CampaignStatus = ENABLED')
    while (campaignIterator.hasNext()) {
        var campaign =;
    Logger.log('enabled campaigns paused');

function reenableCampaigns() {

    var label = AdWordsApp.labels()
        .withCondition('Name = "Active Last Month"')

    var campaignIterator = label.campaigns().get();

    while (campaignIterator.hasNext()) {
        var campaign =;
        campaign.removeLabel('Active Last Month');
    Logger.log('First of the month: campaigns reenabled')


Last week I launched Quizzly. It is a free web app you can use to quickly make, distribute, and save multiple choice quizzes. Log in to create a quiz. Find a topic you like and explore.

It is in Beta, so it is very close to its final form, but some new features and design changes might happen. I might even change the name. All of your quizzes will still be there.

Thanks for checking it out, have fun!