Curious to explore AI formula generation, Grist’s R&D team recently experimented with OpenAI and a prompt generation strategy to see how well AI could write formulas in Grist. While this AI-assisted formula generation feature isn’t yet a part of Grist, this experiment proves its future value. The experiment demonstrates how AI can eliminate a common struggle for today’s spreadsheet user — writing formulas correctly.
Imagine if writing spreadsheet formulas were as simple as asking a question.
If you ask a spreadsheet fan what they like about them, there’s a good chance they’ll mention formulas. Spreadsheets are the original low-code platform – sprinkle in a little bit of code and you get a big benefit. But that little bit of code can be daunting and unapproachable.
We see plenty of Grist power users who use every Grist feature except formulas, which seems a shame. People may find it easy to write a formula to add two numbers, but how do they uncover in which quarter a date resides? Or how to fix those missing leading zeros in a column of zip codes? Not to be dramatic, but users are in for a time-sucking mind-numbing rabbit hole of searching the internet for tips. AI might be the solution to this common spreadsheet headache!
Spreadsheet formulas expressed in Python
In recent years, AI large language models have gotten quite good at interpreting language and writing code, with many programmers using them routinely. We wondered – could advances in AI be applied to writing spreadsheet formulas in Grist? We wanted to uncover whether AI could bridge the gap between what the user needs a formula to do, as expressed in plain English, and the formula that delivers the correct result.
While spreadsheet formula writing bots aren’t new, Grist is an evolution of spreadsheets with a unique opportunity because we support Python for writing our formulas. Grist formulas lift up Excel functions to a new level with the full power of Python. Current AI models are particularly good at Python because there are vast quantities of Python code on the internet to train these models on.
As the most popular programming language today and the best for beginners, Python was an essential choice for Grist, unlocking many possibilities for a large audience. Python is much more capable than spreadsheet functions at data analysis and data manipulation – greatly expanding what’s possible in Grist.
Relational Data
Another significant advantage of Grist over traditional spreadsheets is its clear structure, with named tables, columns, and relationships between them. Each document is essentially a relational database, with its schema neatly described in Python in Grist’s unique “Code View” feature. All this is available to the bot, helping it understand the data and make inferences.
With spreadsheet formula bots like ExcelFormulaBot, prompts must often specify which columns or ranges should be included in the formula because spreadsheet schema is undefined. In Grist, the prompt doesn’t need to mention specific columns. This is because the AI can look at the tables’ schema, interpret the data, and guess which columns to use in the formula.
The AI Formula Generation Experiment
The team tested the experiment using documents and formulas found in our template gallery. The AI-generated formulas were correct 36 out of 48 times in the minimal experiment test dataset. That experimentation suggested ways to improve this, such as writing column meta descriptions – an exciting path to look forward to. You can view the results of the AI experiment.
Even when the formula was incorrect, it was often close enough that a Python novice could read the formula to understand how to tweak it to produce the correct result. The AI-generated formula can also demonstrate new ways to write code, which teaches key concepts to beginners.
Need to write a formula? Just ask!
AI has left the realm of science fiction. The future may offer spreadsheet formula writing that is as simple as asking a question, thanks to AI. Today, many professionals, such as programmers and artists, use AI as a part of everyday tasks, reducing the friction of getting the job done. With AI-generated spreadsheet formulas, Grist users may soon join those who use AI casually daily – we think that’s pretty cool.
If you’re a Grist self-hoster with an API key to OpenAI, that day has arrived – play with the experiment yourself! You can find the AI experiment on GitHub.
