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, 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-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::
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.
BONUS: Caching responses to save on costs
One thing I found often in working with clients to optimize their prompts is that I was wasting a bunch of money every time the spreadsheet reloaded. It also took quite a bit of time to run everything, particularly with GPT-4, which tended to run into rate limits as well, causing errors. This can be solved by a concept developers call 'caching', which is basically storing the result of a computation or API call somewhere you can access it easily, rather than re-running it. Google Sheets actually does have caching for app scripts but it only lasts for 10 minutes, and they hilariously even they say it's not particularly useful.
So the solution I found is to simply cache the results in the column next to the responses, using CMD + C (copy) then CMD + SHIFT + V (paste without formatting). Then with my ChatGPT function I first look at the cache column to see if there's anything there, and only run ChatGPT if not. This works well enough for me, because in my workflow I can paste across a decent result into the cache, and then delete it to re-run if I need. It saves me a bunch of cost in terms of tokens for OpenAI because I'm not re-running everything all the time, and also is more secure because I can delete my API key before sending the results to a client, without losing all of the example responses I have generated.
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.
