Generate Google Sheet formulas with GPT-3

10/03/2022, GPT-3

Generate Google Sheet formulas with GPT-3


This free Google Sheet script uses GPT-3 AI to generate complex Google Sheet formulas based on any plain English description.

You simply describe the formula you want into a cell. The script then generates a valid Google Sheet formula based on your description.  

Here are a few example prompts:

  • Add the values of a2 and b2

  • Add the value of all rows in column a

  • Count the number of rows in cells A2:B20 with a value greater than 20

  • Split the text in cell A2 where the “/” symbol appears into two columns

  • Forecast the sales for five years ahead using the dates in cells A2 to A18 and the sales values in cells B2 to B18

  • Return “FOUND” if the string “product” is found in any row of Column A or “NOT FOUND” if “product” is not found in any row of Column A

 

Where is this data coming from?

It’s important to understand that GPT-3 is not simply querying a database of known facts or using Google’s search index.  

GPT-3 was trained with data from CommonCrawl, WebText, Wikipedia, and a large corpus of books. It extracts data from multiple sources instantly and combine them to assemble your request. The model was trained until the end of 2019. Therefore, it may not be familiar with any new functions introduced after this date.  

How can I use it?

1) The first step is to register a free account at OpenAI.com

Upon registration, you will be given $18 worth of free API credits.

Once registered, grab a copy of your secret API key and save it on your device.

 

2) Download a copy of my free Google Sheet and script designed to make this process as easy as possible without having to understand or write any code.

3) Enter your API Key from Step 1 in Cell A2 of the “Settings” sheet.

4) Describe the formula you want into Cell A2 of the “Formulas” sheet. For example, “Add the value of all rows in Column A.”

5) Enter =GETFORMULA(A2) into cell B2.

You may be asked to grant the script permission if this is your first attempt.

6) After a short delay, you should see the formula appear in cell B2.

Do take a look at the script behind this function by selecting Extensions > Apps Script from the main menu and feel free to adapt it for different use cases. 

I am interested to hear about any interesting formulas you create with this script. Please let me know how you get on.

 

Danny Richman