Update Fields with Integrated Scripting Approach
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 -
- Airdrop is a server script that runs every 60 seconds looking for tasks to process.
- It is guided by an arbitrary base (named Airdrop) that contains a table which lists process tasks that Airdrop must evaluate; (i) runs every 60 seconds.
- When it encounters a task that needs to be processed, it reads the script and carries out the defined [script] steps on the targeted tables/fields.
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:
- Avoid adding to the true cost of Airtable for my clients.
- Avoid security risks transmitting sensitive data across third-party platforms here and abroad.
- To sustain precise control over the processes with minimal new code required for new processes.
- Provide lights-out processes so users don’t have to actually do anything nor do they see any special formula or link fields with URLs that open additional tabs.
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.
