ai()

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.

Example Uses of ai() custom formula.

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.

Population of a city.

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…