ChatGPT in GSheets

August 13, 2023
Michael Taylor

New AI models from OpenAI and others promise to have a huge impact on marketing, and the pace of innovation is accelerating. Tools like ChatGPT, and 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-4 more accessible, it needs to be available where I live, and that’s in Google Sheets. As an update to my previous GPT-3 template, I decided to update the function to support the latest models in the ChatGPT format::

> CHATGPT() 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:

If you want help learning how to prompt ChatGPT and work together with it, I have partnered up with James Phoenix (who I work with on Vexpower) to build a Prompt Engineering course on Udemy, with over 9 hours of content.

How to use ChatGPT in GSheets

In order to use ChatGPT you need an OpenAI account, which you can set up on their website. Check out the latest pricing on the OpenAI website (note: cutting-edge models can cost an order of magnitude more per 1,000 tokens). The tokens count both the text going into the prompt you send ChatGPT, 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 may get free credits, but in my experience the costs are trivial even for heavy usage compared to doing the work yourself manually. 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 with ChatGPT, then copy and paste the results into a new sheet to share.

Once you have the API key set, running CHATGPT() is the same as running any other function in GSheets: you simply use CHATGPT() with the only required parameter being the prompt you pass ChatGPT. For example when I passed “Once upon a time…” I got back “ a beautiful princess was born.” which is how ChatGPT 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 ChatGPT 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 important 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, as well as the system message, which act as additional instructions for how ChatGPT should behave.. If you’re finding ChatGPT is rambling on for too long, shorten the max_tokens (default: 100), or try to give instructions to respond briefly in the system message. If the results need to be more predictable and less creative, dial down the temperature (default: 1). If you turn on the Show Probabilities setting in the OpenAI 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 CHATGPT 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 ChatGPT API when the function is used. It reads the API key from the cell A1 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 and any other 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