Rock Trembath

How to: Connect OpenAI ChatGPT to Google Sheets [FREE App Script Tutorial & Starter Template]

My previous video on how to connect GPT-3 to Google Sheets was well received on Youtube. It received thousands of views and brought in a lot of excellent feedback that guided my subsequent content on building a blog post with AI and creating a PR generatorNow that Open AI has made the ChatGPT API available to the public, I took some time to update the App Script to use the latest model. 

Transcript of this ChatGPT Video:

[00:00:00] In this video, I’m gonna show you how to connect ChatGPT to a Google sheet so you can have access to its natural language processing right within the Google Workspace.

I’ll be providing all of the code that you need, plus a simple template so you can test it out and see how it works for you.

[00:00:18] If you’re a tech savvy person who’s not scared to read the comments in code, you can skip this whole video and just go to the link in the description below. It will take you to my GitHub page.

I’ve added comments throughout that tell you exactly how to set it up. So just paste it in your app script window and get going.

[00:00:35] If that sounds too technical or a bit intimidating, don’t worry I’ve also created a starter template anyone can use to bring ChatGPT into their Google sheets.

[00:00:45] The new ChatGPT API. That’s a bit of a mouthful. Brings all of the improvements they’ve made with ChatGPT out into your applications. The new 3.5 turbo endpoint will automatically route your requests to the most recent stable model, and it can be up to 10 times cheaper than processing the tokens with the Davinci model and in my tests, the results have generally been faster and better.

[00:01:11] I created this quick start template that you can get via the link in the description or typing it out in your browser url. It’s a very basic layout. You type your prompt in and the output is generated here.

[00:01:24] You can use the checkbox to enable or disable the ai, so you’re not using tokens unless you’re ready to generate some content.

[00:01:32] Once you’ve loaded the sheet, you can get started by making a copy of the document, giving it a unique name. You’ll see here, it’s letting you know there’s an App Script attached to it, and that’s how we connect to the API.

[00:01:43] Click make a copy.

[00:01:44] And now you’re free to start setting up and modifying the template.

[00:01:47] Second step is to add your API key.

[00:02:01] Next thing we’re gonna do is set our open AI API key by going to the app script in extensions. And right up at the top of the file here, just paste in your open AI key. The second line is the max tokens you want to process with your requests. I have it set for 200, which is for quite small jobs. You can set it as high as 4,000. Just keep in mind that number will affect the costs of using the api.

[00:02:29] You’ll see here we have the model set to GPT 3.5 turbo, and you can adjust the temperature to change how creative the responses you’re getting are. By default I have it set at 0.5.

As you move it closer to two, you will see it start to get overly creative and verbose. So play around with it and see what works best for you. Now that the settings are in there, I’m just going to click the save. And return to my starter template.

[00:02:54] This is a very basic template, just to give you an idea of how you could use it. The prompt is where you put your question or command, and the output is where the answer or text generated will come in. As an example, what year did the drummer from Def Leppard lose his arm? I hit enter, nothing happens. I click the button and it starts working. As you can see here, Rick Allen lost his arm in 1984.

[00:03:21] As long as you have this check box engaged, anything you type into the prompt field. will generate an output and cost you tokens.

[00:03:29] If I wanted it to generate content, I could say something like, write a tweet about how awesome Def Leppard song Let’s Get Rocked is. When I hit enter it loads again. 

[00:03:42] When you un-click the checkbox, the text goes away. So make sure to save anything it generates that you like somewhere outside of that field.

[00:03:50] if you’re curious about how I’ve made this sheet interactive, there’s a simple if statement, checking the status of my checkbox and changing the text to enabled or disabled, depending on whether or not it is checked.

[00:04:03] There’s also some conditional formatting happening, which you can see by going in Format > Conditional Formatting, and then looking at the rules, which colors. all of Row one. If B one equals TRUE.

[00:04:15] Now these little user add-ons aren’t necessary, but if you’re building generators for team members or clients, these little UX items can make a big difference in the way that your content generator is perceived.

[00:04:27] When we look in the output cell, you’ll see I have an IF statement checking the status of the checkbox, and then I’m simply running the AI function and passing the prompt forward.

[00:04:38] If you want to generate additional fields, you can simply click into any cell. Put equals AI open bracket. And then in quotes, pass it. A prompt close quote, close bracket, hit enter, and there it is.

[00:04:52] If you’d like to reference cells inside of your prompt. It’s also quite easy. You just open the AI function. Write the first part of your prompt, close the quote, use an ampersand, reference the cell. Use ampersand. Then an opening quote. You can then write the rest of your prompt for this one, I’m just gonna add a period. Put a close quote, close bracket, and let it run.

[00:05:17] Now you’ll notice when I created this one, I did not include this if statement to have it check whether or not I want the sheet to be active.

[00:05:25] So when I uncheck the box, it will just generate some random text and use up tokens.

[00:05:30] So be mindful of how the different cells work together.

[00:05:34] If you’d like to see real world examples of how I use this, you can check out my video on generating a blog post.

[00:05:40] Or generating a press release to understand better how you can use these algorithms to generate content. If you’ve already created documents and generators using the Da Vinci model code I previously provided, you can just take this AI function and paste it over the AI function in your old documents, and it should work just fine.

[00:06:00] And it really is that simple.

[00:06:02] As always, I’d like to remind you content generators are just a tool. You need to check any statistics or facts that it provides because it likes to lie, and your success with this type of content is a hundred percent dependent on how you edit and adapt it for your needs.

Basically just don’t copy and paste everything without checking it and expect good results.

[00:06:24] If you found this video interesting and helpful, I’d like to ask you to, like this video, subscribe to my channel, and if you’re feeling really generous, there’s also a link in the description for you to buy me a coffee so I can stay highly caffeinated and keep cranking out content.

Thanks for watching, and if you have a question, just put it in the comments below. I’m looking forward to hearing about what you built with this code.

Want to collaborate?

Fill in a few details below and I’ll follow up to find a time to chat.