ai()
A simple custom Google Sheet formula that changes everything?
As we await the briefly anticipated release of Duet for Google Sheets, it occurred to me - why wait? Can’t we start today by building custom formulas that integrate directly with PaLM 2 to perform text completions?
So, I built ai(), a Google Apps Script function that transforms prompts into data. And it mostly works pretty well.
Use Cases
As demonstrated above, I configured some simple tests to determine the viability of using AGI for spreadsheet data. The usual suspects are things like sentiment analysis, and composing content.
I’m certain I have not begun to understand the magnitude of this capability. Using ai() it to generate lat/lng map locations was a bit mind-blowing considering all the effort I’ve gone through to generate lat/lng coordinates from the Google Maps API. The formula is simple and elegant.
=ai("What is the lat/lng of this city?
Example: 51.507222, -0.127500",
H2
)
This approach seems to handle numbers just fine as long as it is not performing computations. I added a column for population and set this prompt to the task. It’s like taking candy from a baby.
A Tricky One
The first experiment involved extracting the surname (last name) from a full name column. This is tricky with international names like Miguel de Cervantes. PaLM 2 apparently has difficulty with the concept of last name and assumes it is the text after the final space. To overcome this, I used this prompt. Worked great (as far as I know). You be the judge.
=ai("extract all but the first name", A2)
Another tricky situation is generating a random number like the age of a person. This, I thought, would be easy since LLMs are so good at hallucinating. I was wrong. Would love to learn why it doesn’t work well calling the PaLM 2 API. It works fine in Bard. Puzzling.
Computations
I said earlier that computations were not likely with this approach, but there are some exceptions. In this example I ask the ai() formula to compute the distances between adjacent cities.
=ai("what's the distance in miles between these two cities? Provide only the number as an integer.", H2:H3)
It gets it right in all but the Atlanta to St Petersburg (Russia) test. This is on me, the nearest St Petersburg is Florida, just 600 miles south.
Caveats
Scale - I have no idea how well this will perform with thousands of AI requests. Be cautions.
This script is sensitive to PaLM’s API allowed pace of 30 requests per minute. As such, there’s a pace limiter that smartly detects if you encounter 429 errors and backs off automatically.
Numbers - it’s a shit show with LLMs. But you knew that.
No warranties on this code. Be cautious.
The Code
This is all of it. Create a sheet, drop it into a Google Apps Script project, add your API key, and start playing around.
Enjoy…