There’s a lively conversation about querying data that is not ideally shaped to answer the desired question here on Table Forums. It is related to an earlier time shortly after scripts were made possible in Airtable when I recommended (in 2020) that a script should be afforded the ability to become a custom formula. I’m not the only developer who has thought about this.
@kuovonne provides an approach to answering the question that started the conversation. But I have and still would love custom formulas to do this.
Imagine a different approach that requires only your data and one API call to OpenAI that costs about 1/60th of a penny.
Given this GPT query:
This is a straightforward idea, but when performing queries across data tables that may not be ideally shaped, we will increasingly see LLMs provide answers where scripting or formulaic approaches are complex and require skills and added development time (of course).
gptPrompt("Given this list of dates, determine which is the latest:", [{field1}, {field2}, {fieldn...}])
Sadly, this formula is not possible, but it could be if custom scripts could be the underlying logic of custom formulas. In my view, this is one of the most significant missed opportunities for Airtable in this decade.
@Kuovonne says - not so fast. There are issues with custom formulas.
Airtable attracts a lot of novice script writers
So what? Google Sheets have attracted almost a billion novice script writers from its 1.85 billion user base, but it found a way to support this idea more than ten years ago. Insulating a platform from a poorly-crafted script is a process known as sandboxing. Script sanitation has been proven effective since 2007.
formula fields update with each key press
And this is good, not bad. There are currently about a hundred formulas in Airtable, and some update every 500ms. You can use Now() to get into all sorts of trouble. The platform is rife with ways for novices to make a mess. But even when users do stupid things, the Airtable platform doesn’t crash.
There is absolutely no reason that custom formulas would not exist in the Airtable platform. There are vast reasons they should exist. Airtable has feeble financial formulaic capabilities. On this dimension alone, entire aftermarket libraries would exist that could plug-and-play.
SideBar: I tried to respond to her comments where this post was originally created but it was [still] a shit-show. I reluctantly published this article there to see if it was any better, hoping no one would comment.
While I like the idea of a formula field that is computed by a custom script, I think there are lots of thorny issues in trying to implement this idea.
I think two issues are
Airtable attracts a lot of novice script writers
formula fields update with each key press
Novice script writers often have a hard time writing efficient scripts and dealing with edge cases. When a formula needs to recalculate on hundreds of thousands of records, you need efficient code. When you have long chains of back-and-forth rollups, you need efficient code. When key presses can result in inputs that the script writer didn’t anticipate, you want the code to handle that gracefully.
Airtable has a history of not implementing features for which there are workarounds. Currently the workaround is an editable field (with edit permissions restricted automations) + automation + script.
Thanks for this reference. I hadn’t known about custom functions in Google Sheets. I rarely use it. I did a quick look at the documentation, and it looks cool. Too bad I don’t have a use case for this right now.
I doubt Google Sheets has anywhere close to a billion novice script writers. I’d believe that number of users, but the vast majority of users are not novice script writers because they don’t write scripts.
Google also has access to more engineers and more QA people than Airtable, and a lot more history and resources related to cloud documents. So it isn’t surprising that they could figure stuff out.
Functions in Google sheets also tend to not be called on every single row in a sheet.
Note that I agree that custom functions would be awesome. I just don’t see it happening any time soon.
But go ahead and advocate for cool features. I’ll be happy to get them. I just don’t expect them.
I have no clue of programming but several of my Airtable formulas are 100’s of nested formulas and I am hitting the 30k characters in the formula field in several fields and have no issues. Of course I am not doing any foolish things that would cause looping calculations. There is no reason Airtable couldn’t just disable a formula calculation if it is causing issues. That’s what I mention should be also in Automations. Right now Automations only get disabled if they fail but not if they run with no end because there is a loop. Airtable needs better catch up these things because they cause frustration to end user and likely also put a strain on resources. I agree as end user, before the controls are in place, it could be more trouble with field scripts as these would then run but a notice programmer or somebody with zero programming skills would not know what is happening.
Can you please share where you learned about a 30k character limit in formula fields?
I know that formula fields have length limits, but when I last looked at this, I could not find a published limit. And when doing experiments, the specific character count didn’t seem to be the issue, but possibly the number of branches.
Upon additional thought (after a nap) I wonder if that one of the reasons this hasn’t been implemented is because engineers at Airtable may be afraid of the code driving formula fields. Any changes to how formula fields are calculated can potentially have huge repercussions. It has been a while since a new formula function has been released. And there is demand for formula functions to be available in automations, but that also has not been implemented.
Novice or otherwise, it doesn’t matter after you reach a few million. The magnitude of custom formula use is what compels me to share the numbers to demonstrate that many other platforms support custom formulas, which means the concept is in relatively high demand. The concept has been proven to work as well.
There were 1.875 billion users of GMail as of 2022-12-31. Their data shows:
Almost 2/3rds use sheets (1.26b)
47% of Sheets users have created at least one script (~590m)
39% of the scriptwriters have used them as custom formulas (230.1m)
So, about a quarter billion Sheets users have created custom formulas, but most users create more than one, and all scripts are callable as custom formulas. Ergo, we don’t have precise data on the number. I suspect it’s close to a billion different scripts in use to satisfy the unmet demand for features that Google has decided it has no dog in the fight.
Both Google and Microsoft allow this because they know they cannot envision every possible use case for formulas. Airtable, by its actions, apparently believes otherwise.
Irrelevant. If anything, custom formula support would eliminate the need for so many engineering and support resources. Implementing and testing a sanitized framework is not more complex than doing it for three scripting features they already support.
Yeah, this is a never-likely possibility. But that’s precisely the point - lacking custom formulas, everything new is out of reach until Airtable decides it’s not. That’s the difference between a platform and a product.
So if the numbers are not fake, then if Google Sheet custom script formula writing was a religion than it would be 3rd largest religion in the world. What else do we need to know? Soon non-GS-script-writers will feel like a minority.
Airtable missed so many opportunities for so many things, funnily enough, I never thought that reusable formulas were one of those. But I guess it’s simply because I focused on other things that were so much more important than that.
That being said, it is true that if Airtable had allowed us to build our own reusable formula, and cherry on cake, had allowed us to publish those scripts in the marketplace, not only could I have made a lot of money, but I’d have made my life much easier in the process.
All that copy and pasting is something I had to learn to feel okay about, but I’m pretty sure it upsetted me at the beginning.
You’re not alone. But let’s agree that you meant “extensible”. I think you meant scripts reusable as formulas.
I’ve never gotten much support from the community about this concept. I guess I see the world through the lens of functionalities that make possible OTHER functionalities that do not exist and which are almost always unanticipated or unpredictable. These core extensibility constructs transform a product into a platform while also insulating the development team from having to build every feature. The most successful products are designed as platforms because they extract the muscle and excitement of the masses.
Costs associated with opportunity losses are generally unseen; the hidden side of everything is substantial. But it’s also a double whammy; the time we spend working around missed opportunities to do things more elegantly is time we cannot use for something more beneficial. Lack of innovation time is a sizeable tax on future productivity.
And that all could have been avoided. But the historical cost is small compared to the future cost of working with a product that is in a stasis about advanced ideas like this. And by “advanced”, I mean smart stuff that is now commonplace for many platforms.
Currently you can publish scripts (not formulas) in the “marketplace”. There there is no monetization system in place. The only thing I’ve received from having a script in the “marketplace” is support requests. So you wouldn’t have made any money. The term “marketplace” was more aspirational than actual.
This assumes the channel for new functionalities is only the marketplace. You are selling outside the private branded Airtable marketplace, right?
It also equates “making money” with only selling something. In many cases, we earn [more] money because we build systems faster, more efficiently, and more advanced. The ability to extend the platform is what gives some developers a significant edge over others.
Had Airtable scripts been callable by custom formulas there would have arisen a marketplace of function libraries the way it has occurred with other aftermarket services that transact completely outside the private Airtable “marketplace”.
Ha ha! It’s not pennies. I earn close to $1,000 per hour on average because of the way I extend platforms.
Make sure you pay close attention as the global economy tries to overcome a massive spending spree that is all debt. We will soon rise to $50T here in America. The only out going forward, avoiding massive financial collapse, is probably productivity increases through AGI. You can’t buy down the debt when the interest is 33% of the nut. You can only raise productivity to whittle it away.
