Introducing your words are data too

"We used copytext for Planet Money Makes a T-Shirt.

Most of our work lives outside of NPR’s content management system. This has many upsides, but it complicates the editing process. We can hardly expect every veteran journalist to put aside their beat in order to learn how to do their writing inside HTML, CSS, Javascript, and Python—to say nothing of version control.

That’s why we made copytext, a library that allows us to give editorial control back to our reporters and editors, without sacrificing our capacity to iterate quickly.

How it works

Copytext takes a Excel xlsx file as an input and creates from it a single Python object which we can use in our templates.

Here is some example data:

And here is how you would load it with copytext:

copy = copytext.Copy('examples/test_copy.xlsx')

This object can then be treated sort of like a JSON object. Sheets are referenced by name, then rows by index and then columns by index.

# Get a sheet by name
sheet = copy['content']

# Get a row by index
row = sheet[1]

# Get a cell by column index
cell = row[1]

print cell
>> "Across-The-Top Header"

But there is also one magical perk: worksheets with key and value columns can be accessed like object properties.

# Get a row by "key" value
row = sheet['header_title']
# Evaluate a row to automatically use the "value" column
print row
>>  "Across-The-Top Header"

You can also iterate over the rows for rendering lists!

sheet = copy['example_list']

for row in sheet:
    print row['term'], row['definition']

Into your templates

These code examples might seem strange, but they make a lot more sense in the context of our page templates. For example, in a template we might once have had <a href="/download">Download the data!</a> and now we would have something like <a href="/download"></a>. COPY is the global object created by copytext, “content” is the name of a worksheet inside the spreadsheet and “download” is the key that uniquely identifies a row of content.

Here is an example of how we do this with a Flask view:

from flask import Markup, render_template

import copytext

def index():
    context = {
        'COPY': copytext.Copy('examples/test_copy.xlsx', cell_wrapper_cls=Markup)

    return render_template('index.html', context)

The cell_wrapper_cls=Markup ensures that any HTML you put into your spreadsheet will be rendered correctly in your Jinja template.

And in your template:

    <h1>{{ COPY.content.header_title }}</h1>
    <h2>{{ COPY.content.lorem_ipsum }}</h2>

    {% for row in COPY.example_list %}
    <dt>{{ row.term }}</dt><dd>{{ row.definition }}</dd>
    {% endfor %}

The spreadsheet is your CMS

If you combine copytext with Google Spreadsheets, you have a very powerful combination: a portable, concurrent editing interface that anyone can use. In fact, we like this so much that we bake this into every project made with our app-template. Anytime a project is rendered we fetch the latest spreadsheet from Google and place it at data/copy.xlsx. That spreadsheet is loaded by copytext and placed into the context for each of our Flask views. All the text on our site is brought up-to-date. We even take this a step further and automatically render out a copytext.js that includes the entire object as JSON, for client-side templating.

The documentation for copytext has more code examples of how to use it, both for Flask users and for anyone else who needs a solution for having writers work in parallel with developers.

Let us know how you use it!

Never miss a gig

Join the Visuals Gigs mailing list to get an email when we post internships and full-time jobs.

Your membership will be kept confidential.



Meaningful analytics for journalism.


A command-line tool to get election results from the Associated Press Election API v2.0. Elex is designed to be friendly, fast and agnostic to your language/database choices.


A JavaScript library for responsive iframes.


On The Team Blog