Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 27, 2019 06:36 PM
I was recently lamenting about the lack of a scripting model in Airtable in this discussion concerning actionable buttons and Jonathan Bowen called my attention to a pretty clever way to utilize multiple Zapier components to call out via URLs in fields and update the record (or records) in the table.
I wasn’t familiar with this approach and it opened my eyes to a number of possibilities for serverless integration processes. It reminded me of a similar approach that I am intimately familiar with that stems from my deep desire to script processes inside Airtable, even going so far as to create a rudimentary web service that would allow me to easily create script steps in long text fields and then execute those steps against fields via the API using a little thing I call Airdrop which I use pervasively with clients.
The idea is simple -
This is all driven from a Google Apps Script service which provides a free tier of 3 hours of server time per day (6 hours for G-Suite accounts). This doesn’t sound like a lot of server time, but it is. Most of my Airdrop “droplets” run in a few seconds and typically even far less than a few minutes for thousands of record updates.
A Simple Script Example
Note - this script (aka, a “droplet”) is not a Google Apps Script - it is simply a list of commands stored in an Airtable text field that is interpreted by Airdrop. (see table screenshot below)
// set the base to Airdrop Demo
set base to appAvzbF1dJ9OkgMn
// select the table
set table to Translate Demo
// translate the english field into the spanish field for any empty spanish fields
replace empty {{Spanish}} with {{English}}.toSpanish()
This is an extremely rudimentary concept, but it allows me to create very precise API-based processes without using any middleware services such as Zapier or Integromat. One of the reasons I developed this approach is to:
One of the biggest advantages is that I can create any script commands I want. This is a double-edged sword, of course; it takes time and some javascript skill to design the command language and build a script interpreter. But, if you keep it simple, you can create some very clever solutions that are wholly reusable across many tables and fields.
Using Jonathan’s example of a translation process, I instrumented this simple method and applied the source field (English
) to replace the target field (Spanish
). Adding this functionality to my Airdrop framework required just a few lines of code.
The outcome is shown below. If any new records are added or a Spanish cell is blanked, the Airdrop process will update those empty cells within 60 seconds or less.
To demonstrate the agility in this approach, imagine I needed a new process like this:
set base to appAvzbF1dJ9OkgMn
set table to Customers
replace empty {{latLon}} with ({{Address}} + ", " + {{city}} + ", " + {{State}} + " " + {{ZipCode}}).geoEncode()
The only code I need to write is this:
And prepare to be stunned (or deeply confused) - I don’t even have to write the geoEncode() method in Google Apps Script; I can drop it right into the Airtable script field like this:
set base to appAvzbF1dJ9OkgMn
set table to Customers
replace empty {{latLon}} with ({{Address}} + ", " + {{city}} + ", " + {{State}} + " " + {{ZipCode}}).geoEncode()
//
// geo-encode address
//
function geoEncode(str)
{
return(Maps.newGeocoder().geocode(str));
}
Airdrop will see the included javascript geoEncode()
function and use it as an eval()
code block. This makes it possible for me to write every-day javascript to update rows and fields and all without modifying the system that processes the updates.
Sep 27, 2019 08:11 PM
You’re my hero @Bill.French :slightly_smiling_face:
Nice work
Nov 01, 2019 05:02 AM
This. Is. Brilliant.
I definitely need to delve more deeply into this…in about a month…
Nov 01, 2019 06:40 AM
Ha ha! Thank You! I wasn’t shooting for brilliant; just being my usual lazy self about writing new code every time I needed a simple data transformation.
To be super transparent, Streamlit was the inspiration for this embedded script idea. And now that Streamlit has advanced a little further as a viable scripting environment, I think it may be the ideal processing engine for stuff like this.
I do a fair bit of machine learning and AI in some of my consulting projects and this tool grabbed my attention despite its Python underpinnings. However, Streamlit and my Airdrop example above is precisely where Airtable should be heading architecturally if they want to leapfrog these code-free platforms and put some competitive pressure on them.