GPT3() Function in GSheets

November 8, 2022
Michael Taylor
Facebook Twitter WhatsApp LinkedIn

New AI tools like GPT-3 promise to have a huge impact on marketing, and the pace of innovation is accelerating. Tools like Copy.ai and Jasper.ai are democratizing access to AI, but I’ve found it hard to incorporate these powerful tools into my own workflow as a marketer, and as a result I’m not leveraging the benefits of AI as much as I should. To make GPT-3 more accessible, it needs to be available where I live, and that’s in Google Sheets. So inspired by @fabianstelzer I decided to build my own GPT3() function, which you’ll find in this template:

> GPT3() Function Template

When you visit the template it’ll ask you to make a copy including the Google Apps Script, and if you click ok you’ll have your own version of the Spreadsheet to follow along. This function is a work in progress that I plan to keep updating, but also feel free to modify it for your own use. I’ve used this function in two courses on Vexpower so far, so it might be worth checking those out if you’re trying to learn:

How to use GPT-3 in GSheets

In order to use GPT-3 you need an OpenAI account, which you can set up on their website. As it stands, using the most advanced da-vinci model costs 2 cents per 1k tokens, which is ~750 words in English. Note that the tokens count both the text going into the prompt you send GPT-3, as well as the response you get back. Also whenever you recalculate the spreadsheet, all the prompts will re-run, which can cost you money. When you sign up for a new account you get $18 in free credits, which should be enough to last you for a while, and in my experience the costs are trivial even for heavy usage, but just be careful and monitor your spend. 

Once you have an account you can visit the API keys page to get a secret key related to your account, which you’ll need to copy and paste into the cell B1, which says “API KEY HERE” in red text. This is the equivalent of a password, so keep it safe, and don’t share it with anyone, or accidentally leave it in a public facing document. My recommendation is to have a spreadsheet where you do your ideation in GPT-3, then copy and paste the results into a new sheet to share.

Once you have the API key set, running GPT is the same as running any other function in GSheets: you simply use GPT3() with the only required parameter being the prompt you pass GPT-3. For example when I passed “Once upon a time…” I got back “ a beautiful princess was born.” which is how GPT-3 decided to complete the text. 

The craft of working with AI is called “prompt engineering”, which is a fast moving field with lots of innovation through trial and error. You’ll find GPT-3 does a decent enough job of anything you ask it to do, but sometimes goes off on a tangent or comes back with something weird. I’d still recommend you have a human check the results of any prompt, and edit or rerun any bad responses before you use the output in the real world. 

The other two parameters I’ve implemented in the function are “max_tokens”, which controls how long the response will be, and “temperature”, which controls the randomness of the response. If you’re finding GPT-3 is rambling on for too long, shorten the max_tokens (default: 100), and if the results need to be more predictable and less creative, dial down the temperature (default: 0.7). If you turn on the Show Probabilities setting in the GPT-3 Playground in your OpenAI account, you can see what it’s doing: by dialing the temperature up, it’s more likely to pick a lower probability keyword as the next one in the sentence, and by dialing it down it makes more predictable choices.

How does the GPT3 function code work?

If you want to see how the code works for this function, so that you can modify it or build your own custom function, click on Extensions > Apps Script in the navigation bar. That’ll take you to the attached Google Apps Script, which handles talking to OpenAI’s GPT-3 API when the function is used. It reads the API key from the cell B1 in the current sheet, then inserts that into the authorization headers so OpenAI know it’s you. 

The code then makes the API call to OpenAI, with the prompt, max_tokens, and temperature from the function parameters.If you’re creating your own function, or updating this one, you should know that the comment in green directly above the function at the end is what populates the function help text when a user clicks into the function bar. Stick to this format and it should work for you too. Finally I’ve found the best way to handle defaults is to put them directly in the function parameters, so that the underlying function is always getting a value for each parameter no matter what the user inputs.

More to read