10x your Google Sheets skills with this custom GPT formula [FREE Apps Script]

Google Sheets is hands down one of the most powerful and versatile web apps out there, and it also stands tall if I look at the multitude of spreadsheets that live inside my G drive. All Google accounts combined the number of my sheets is easily in the higher echelons of triple digits so it’s fair to say that this app has earned a guaranteed and well-deserved spot as one of my favorite daily companions. 

‍

A treasure trove of functionality

Beyond ‘conventional’ number-crunching I also use it for tasks that are more on the text-heavy side of things as Google Sheets comes with lots of useful functions that make a pretty dope and well-rounded digital toolbox to get almost anything done (the more than 500 functions are testament to this - check out this list for a full overview).  

‍

That said, there’s still a couple of tasks where I feel the built-in functionality isn’t sufficient or at least not as convenient as I'd want it to be - especially when it comes to categorization/classification (e.g. keyword intent). Sure you can tinker with REGEXMATCH and nest it in a few IF statements but truth be told, it can get a bit messy at times, particularly when you revisit the sheet after some time and then desperately try to figure out what the hell this Frankenstein formula construct actually does. 

‍

The Swiss Army knife formula

Luckily with the advent of current-day LLMs and all their capabilities, tasks like the above-mentioned can be dramatically simplified in terms of syntax. That is if you have an appropriate formula at hand that can get the job done faster and/or more efficiently. That’s why I’ve written this handy Apps Script that gives me access to a custom formula that will call Open AI’s text completion endpoint and do whatever I want it to do (admittedly, I found the script online and rewrote parts of the code with ChatGPT - I'm a Growth hacker after all). 

‍

‍

Chat vs text completion endpoints

Now could I do most of this with ChatGPT? Sure but first of all I don’t want to copy and paste stuff back-and-forth all the time and secondly, the chat endpoint - although favorable for a lot of things - isn’t always the best option when it comes to (shorter) completions that don’t require a two-way conversation street and all the context stored in the message history. So I’d say for a specific set of tasks inside Google Sheets - this script is a more elegant solution for me. 

‍

Note that by the time of publishing this in October 2023, the script is already a couple of months old as I initially came up with this in January or February. I did however update the engine from the old text-davinci-003 to the 3.5-turbo-instruct series and I must say not only does it perform even better now, it is also significantly cheaper compared to the original version! I think at this point there are even some plugins available that come with a few preconfigured formula variations but they do pretty much the same as this script. As long as you are somewhat familiar with prompt engineering you’re good to go. 

‍

Now what can I used this for? 

Good question to ask. Answer: pretty much anything as long as you get the prompt right. Here's just two quick examples that I have used it for recently: 

‍

1. Guess a person's gender and language based on their name

‍

2. Classify companies with SIC codes based on meta description

‍

Step-by-step instructions

Step 1: Make a copy of the sheet

Click the link to access the spreadsheet and copy it to your Google Drive (you need to me logged in to your Google account to do so). Go to File >> Make a copy and then save it to your Google Drive. The spreadsheet already contains the Apps Script. More on that later.

Step 2: Get your API keys

Head over to OpenAI’s website and sign up or log in to your existing account. After you’ve signed up / logged in click on your name in the top right corner.

Next click on API keys and generate a new secret key.

Now head back to your Google Sheet and go to the tab api_key and insert your OpenAI key in cell A2. Note that the script will only be able to make the API call if the keys are in cell A2 in the tab api_key. It's also important to keep the naming convention - if you absolutely want to rename the tab api_key, make sure to also do this in the Apps Script file then.

Step 3: Give the script permission

In the top nav, click on Extensions >> Apps Script.

Next up we need to run the script once and authorize it to access files in your Google account. Click on Run >> Review permissions. A popup will open telling you that this script has not been verified by Google. Click on Advanced >> Go to GPT.

Now click on allow and the script will run. This is the last set up step.

Step 4: Formula syntax explained

You're all set now - feel free and make your first API call! If you need a little jump-start, let me quickly show you around and explain the formula syntax: 

=GPT(prompt, max_tokens, temperature)

• Prompt: pretty self-explanatory - your instruction on what you want the GPT 3.5 to do.
• Max_tokens (optional): with this setting you can steer the length of the completion (output). The higher this value, the longer the response will be. Keep in mind that the total context length of the model is 4096 tokens (input and output combined). As a reference 1 token =~4 characters in English. The default value is 100 tokens in case you leave this blank.
• Temperature (optional): setting for creativity or randomness - value between 0 and 1. For deterministic tasks set the temperature to 0. If you need more creativity bump this up towards 1 (I recommend to start lower and then see what value leads to a good result).
• All other settings (top_p, best_of, frequency_penalty and presence_penalty) have been hard-coded into the script as I thought this would complicate the formula too much from a usability POV.
• Example: =GPT("Say Hello", 50, 0)

Step 5: Save as values

Optional but this can come in quite handy to prevent the formula from making API calls every time you refresh the sheet (although the models have become significantly cheaper it can still eat into your cost if you drag the formula down 1,000 cells or so).

Simply select the range you want to insert as values and click Formulas to values >> Save as values (top bar navigation). This does the same thing as copy + insert as values but it's a bit more convenient. Don't forget that for this to work you need to select the range you want to apply this to.

Check out this video if in doubt 👇

‍

‍

‍

Conclusion

With the awesome might of LLMs right there, we can whip up some pretty fancy responses with just a simple prompt. It's like a secret weapon for folks who deal with spreadsheets all the time. Hope you've got the hang of how to plug in OpenAI into Google Sheets using Google Apps Script. Here's the link to the spreadsheet again - if you need help, feel free and reach out via the contact form.