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

Did you find this helpful or fun? Please donate!

donate via btc or eth

btc: 18jCzwsZDGQYcs6Kyv92pd4683cnnxm1Dd
eth: 0xC285F21Cb271Cb4B3F70c4C47B2f7B26063AF590
paypal: 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