20 September 2021

How to escape variables in formulas with the Airtable API

I usually describe Airtable to other people as the combination of two ideas:

  1. what if we built Excel today by considering how Internet applications work
  2. what if we focus focus on all the use-cases that aren’t strict financial models or analysis

One amazing consequence of this is the Airtable API is a delight to work with! Since you create tables instead of sheets, everything maps very cleanly into a ready-to-use REST API.

While working in Python3 with the airtable-python-wrapper library, I discovered, however, that there’s not much documentation for and I couldn’t find helpful libraries to properly escape variables when passing them into formulas with the API. I ultimately asked on the Airtable forums and got some helpful pointers (and some downright incorrect ones), but was unsatisfied with what felt like one-off, handwavy suggestions. So, here’s a helper function I wrote to escape variables that you want to use in Airtable formulas:

You might use it like this:

from airtable import Airtable
from airtable_escape import airtable_escape_variable

# given the variables: base, table, email

client = Airtable(base, table, api_key=settings.AIRTABLE_KEY)
client.get_all(formula=f'{{email}} = {airtable_escape_variable(email)}')

Note: the double brackets inside the f-string, e.g., {{email}}, are how you escape brackets, so for email = "[email protected]", that formula string would result in'{email} = "[email protected]"'. Click into the gist for more info and some test cases too.

Let me know if you try this out and encounter some edge-cases that I missed. Ideally something like this would be part of the various Airtable API libraries—it’s too easy for people to mess it up and it’s a waste of time for people to re-implement it.

This wouldn’t be complete without an appropriate movie poster:

Escape from the Airtable API

Interested in working with me at Superset to help shape the future of virtual fitness and to empower people to launch their own businesses online? We’re hiring a full-stack engineer. 💪🤓💻 Apply online

Did you find this helpful or fun? paypal.me/mrcoles
comments powered by Disqus

Peter Coles

Peter Coles

is a software engineer living in NYC who is building Superset 💪 and also created GoFullPage 📸
more »

github · soundcloud · @lethys · rss