Created a GPT function in Sheets

Advanced

About this Tutorial

Unlock the Power of OpenAI: Create Ultimate GPT Prompts for Seamless Interaction! Discover the Formula Inside, Explore APIs, and Boost Productivity. Watch Now!

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>00:00 Alright cool, so in this video we are creating sort of an ultimate GPT prompt inside, not prompt, but formula that includes prompt, that includes a temperature, that includes all this stuff. 00:13 Everything that you will be able to interact with OpenAI. In another video I have done the Apps Script that actually connects the, API to OpenAI from Sheets, so if you want to get that, watch that other video. 00:28 There's a couple videos, one called Quick Start in Quick Start, OpenAI Tutorial.

And then there's another one that, that one uses DaVinci 3 model, but there's another one called Upgrading. 00:40 So, we're going into 3.5 and 4.0, upgrading the GPT model. That takes a different sort of messaging syntax . So we had to do those updates. 00:52 In this video alone, we're going to just interact from the Sheet to the model much more interact- So, we're going to just interact from the Sheet to the model much more interact- directly between the Sheet and OpenAI's API. 01:25 Again, that all is in another video.

This one is specifically about the interacting between the Sheet and the Apps Script . 01:33 So, let's go over to our project editor and see this Apps Script in action. We have OpenAI 4. That's where we're calling it right now. 01:40 I might change that. Actually- I will change that in this video to GPT. We have just a prompt. And then you can see the messages here, which we went over in the upgrading video. 01:50 We have all of this hard coded in here.

All of this temperature, the max tokens, top P, frequency penalty, presence penalty, stop. 01:58 Now, if you're familiar with how to or what to use- in each of these cases, then this video is going to be perfect for you. 02:05 We're not going to go through each of these individually. That'll be in some other videos. And are available online in prompt engineering and everything. 02:15 We're just going to take a normal prompt. We're going to add all this stuff, but we're going to put it right here, up here in the function . 02:20 And we'll make sure- ... ... ... ... ...

We have it written so we know what's going on. You don't have to remember this order. 02:27 And you'll see why in a second in this video. We're going to create a custom function from this. So let's get started. 02:34 First things first, is I don't want to hardcode the API key. I have the API key on another tab. We can just do sort of tiny sheets. 02:43 Let's create a one by one sheet. And this is what we used to do. It's just call this API key. 02:48 But I don't want to put the API key in here.

I want to be able to put it sort of anywhere I want. 02:53 And then just reference it in the function . So first thing we're going to do, call API key, put a comma there. 02:59 And we're going to delete all of this. And in the function down below, again, go over that in a different video. 03:08 The API key is the variable name is API key. So that's all we have to do. Now we can put in an API key, put in a prompt and get a response. 03:17 Let's do that. All right. By the time you watch this video, I'll be deleting this. But here we go.

We're just using the function open AI for we have in quotes, our API key. 03:28 And then we have a question. What is an API key? And we're asking this to AI. And here's the answer. 03:33 And API key is a unique identifier, typically a long string of letters, numbers used to authenticate. This is the answer, right? 03:39 And all we had to do was change that API key to. Put it into the function . Alright, there's also this role we can do. 03:47 There's also a model. Actually, let's do that next.

I don't want to limit this to GPT for you can actually use GPT 3.5 turbo, if you want with this particular syntax or for. 04:00 So let's make that a variable as well. We'll call that model. And we're going to go up here and say model, comma. 04:06 Now we have three things in this function . API key model and prompt. And this model is right here. So we'll save this and actually at the same exact time I'm going to rename this function to GPT and we'll get an error here. 04:22 We'll just rename this GPT. And then we have our API key and now we need our model in quotes.

We'll do GPT four. 04:31 And we will ask a different question just so we see a different answer. What is an API get call? We'll ask that question. 04:41 This will take a moment to. We'll get back to us because it is GPT four. It seemed like when I was doing my test, GPT three point five turbo was much fast, much ish fat ish faster than GPT four. 04:52 And here's our answer and API get call. And here's all of our informations. But you know, as we're getting this response back, I realized let's actually use Google sheets to its maximum, right? 05:10 Let's take this API key.

Let's move it out to another cell and actually reference a cell in this because we can. 05:18 We're going to do this in B two actually.. We're going to paste that function , but we're actually going to put this call, let's call this AI here. 05:32 And we're going to call this API key. So just in case we might want to use different API keys down the line. 05:39 And we might want to. Do other things here. Let's move this. Model and actually we're going to change the model to, but we're going to change it to C2 and put the model here. 05:52 Put GPD4 and call this model.

And then we're going to use the prompt. Take that away and put in D2. 06:01 There we go. And there's our prompt. But let's change this prompt just so we know we're actually getting some answer here. 06:09 Alright. We have prompt. Okay, so now we have API key, our model, our prompt. It's all in a sheet here. 06:17 What else can we do? We want our rule. So this is the system rule.. Again, we're just gonna delete this and put it into here system. 06:29 And I'm actually gonna change that to system.

Because we want to do one more thing, we might want to put assistant in here. 06:38 It's gonna get a little complicated. But let's just do that first. We got a system. We need. To make sure it is the third thing here. 06:48 So we'll do E2. And we can leave this blank. It, it, for now, it actually, I think 3.5 almost sometimes sort of ignores the system. 07:02 But let's, we have the prompt. We have the system. And our max tokens right now is only 500. Let's change this to an input as well. 07:11 So we have, we're gonna get, we might get an answer here. Doesn't really matter.

We're gonna keep working on this. 07:19 Maybe we want to put our prompt in a different place. We can in a moment, but let's put temperature. I'm actually, we'll call it just temp. 07:27 And instead of one here, we'll do temp. And now we know we have a prompt. Let's actually move this over so that this is sort of an order BC, BCDE system prompt. 07:38 And then we'll have what do we call temperature? And we'll have, we are always gonna get an error as we go through this because we don't have our F2 for temperature. 07:54 What do we want here? Actually, let's just zero.

We had it one in the hard-coded, but here we can also change it, but we can say zero to one. 08:05 And I do want this to go faster a little bit so we're gonna do GPT 3.5 turbo as the model. 08:13 And see, instead of having to go into the App Script , edit that, save it, and come back, we can now assign it in this text field . 08:22 And that was pretty quick. So we have, our prompt. We can ask, you know, what is the translation of. You can do something like, what is the translation of heart? 08:41 AKE to, cling on. I don't know.

I don't know what the answer will be, and I don't know if it will even be correct. 08:51 But you can sort of see the GPT 3,. We'll see if GPT 3.5 is faster than that 4 was. Yeah, that was much faster. 08:59 Let's just format this to here. Oof. Okay, I guess that. That's an answer. Let's keep going. We have max tokens. 09:08 We're gonna go tokens. We're gonna do tokens. And again, I'll show you a little bit of helper we can do here. 09:15 Once we get a few more, we just have a few more to do. Tokens. Tokens.

And we're gonna put in actually 100 to make it a little bit shorter. 09:25 We have, We have top P. So we'll do top P. And we'll just add this comma top P. And we'll just do a few more frequency presents. 09:37 And then stop. And we'll just do that. Frequency. Press. And stop. Okay, see, all we have to do is, Create right this variable up here in this function area here, knowing that whatever is there when we enter that function in the sheet, we'll go through all the way. 09:57 So we have tokens, then top P. Then frequency. Penalty. Then, And we have, what's that? Presence penalty?

Presence. Penalty? I don't know if I spelled penalty correctly. 10:18 Penalty. Frequency penalty. Then what was it? Last one was stop. Right. Cool. And again, we have to go through this GPT, and now we know where , we're just in the right order, hopefully. 10:34 G2, H2, I2 G2. I think I want to have top U1, 1. We might have, we need 0, 1. We can look back and see if there was any difference. 10:50 But we have top E frequency presence. So right now, if we take this formula , we want to do equals GPT. 10:58 Notice how nothing happens.

There is no autocomplete, there's no message in here. It doesn't tell us anything about GPT. We would have to, Remember all of this, and particularly we have to remember all of this order. 11:10 But that's not very helpful. What we can do is add this text in front of it, which is sort of called JS doc. 11:16 It's this slash, Asterix, Asterix, and it's these sort of comments . And the key one here is at custom function .

We'll write a little, Description here just to give us a little bit more information. 11:29 But automatically, all of this will be available to us with autocomplete inside of our function when we're writing, Inside of our cell when writing it. 11:39 So we're going to do equals GPT. And now, right away we have autofill . I don't know if you can see that. 11:46 I'm going to zoom in a little bit. But here it says GPT generates a response from an open AI prompt. 11:51 And when I hit the parentheses sign, now we get to see each and every one of these variables.

So all of these variables that I shortened, we can actually make longer and be able to now exactly know, Oh, what it is. 12:08 Right, so we can change this like to from temp to temperature. And the only thing we have to do is change this variable here that we use, temperature, tokens. 12:18 We can even call this max tokens, maybe. And that reminds us of that's max. Maximum tokens. So let's go back here. 12:30 Let's do that parentheses again and see now we have temperature max tokens.

This top P we can actually call it top. 12:41 Top P instead of top with two P's it can sort of live better. In our memory of like oh yeah that's top P instead of top. 12:51 Did we misspell that frequency? We can sort of take the entire text here. Change that change frequency change presence penalty. 13:02 There we go save all of that and now. When we go into our parentheses all of this is the exact order that we need. 13:11 So we don't necessarily have to remember this.

This makes it so much easier to use this GPT function inside of sheets. 13:19 And our API key can be sort of anywhere. What we can do here is have some settings. This sort of is a fun thing to. 13:27 To do add one column to left. Let's add another like five rows and we're going to do API key here. 13:35 We're going to do what was the next one model. And this is if you want to sort of set these individually. 13:46 And only once, right? We could take this GPT function and just copy and paste down. Now we can copy our API key over here. 13:54 We can. Even try different models.

It's actually, we're going to get some error because we don't have all of this stuff copied here, right? 14:05 We don't need the system. We can see what is the. The difference between these two prompts with this same prompt different models. 14:11 We can say all of this stuff is also going to stay the same. And now the only thing we're changing is the model. 14:19 We can see sort of compare, you know, the difference. We can take this whole row , copy it again a couple times and change the. 14:27 Temperature to point five to point to one.

We can then, you know, keep, keep changing each of these things and say, you know, now we can see the difference between each of these items, right? 14:41 One. See if that changes anything. But the other thing we can do in sheets. Is use Google sheets to reference different cells all the time, the same cell all the time, sorry. 14:54 So instead of our B5 is our API key, we can set it API key only once. We don't have to copy it all the way down here. 15:03 Say API key is there and now change this B2. 2B1 on settings.

And what we'll need to do is do a dollar sign in front of the B and dollar sign in front of the 1. 15:19 That way, wait dollar sign in front of the B, dollar sign in front of the 1, dollar sign , hit enter. 15:28 And now as we copy and paste it down, you can see that the B1 for settings doesn't change.

That cell is now referenced all the time. 15:36 But the C2, C3, C8 changes to C4 all the way down, IJ, all of them change the row without changing the without changing the column. 15:48 Alright, but other settings we can do as well, because we're not gonna, we might not want to test different models. 15:52 We might want to say, hey, we're just gonna use 3.5 turbo all the time.

And we can just paste that there. 15:59 Another thing we can do with sheets is instead of having to type this out, so say we want to change it to GPD4, and we're like, do we really want to have to remember it? 16:08 Is it GPD-4? We can go to create a dropdown , and just do GPD3.5 turbo. Actually, I did spell that wrong. 16:20 3.5-3.5-turbo, and we only have to get it right once. And then GPD-4. There we go.

So now, instead of having to type that out, especially type it out in every single prompt, or even keep it the same inside of our Apps Script , we can keep it the same in the settings, and we can also have this dropdown 16:46 menu that changes this from GPD3.5. And then we can keep it for any time we want. And it'll change all of our everything. 16:53 We just have to make sure that we are setting this correctly. So we do same thing we did before, settings exclamation point. 17:01 I think it was B2 with dollar signs in front of the B and in front of the two.

Now we can copy and paste this all the way down. 17:13 We're going to use GPD 3.5. It is B2 for the model. So these columns don't have anything to do. We delete them and now they have no impact whatsoever here. 17:24 So that means it, we have a lot more room to put our system prompt. But our prompt to put temperature to play around with tokens a little bit. 17:33 We can see what is the difference if we do a hundred tokens, 500 tokens, 1000 tokens.

We don't have to deal with, you know, changing that model. 17:41 And as we do that, maybe I'm not going to do it in this video, but each of these as we test them out, we'll find the. 17:49 Ideal setting, right? And then go add it to the settings, do exactly what I just did. Add it here change it in our GPT here. 17:57 Now, you know, why would we do that and not put it in the app script ? Well, this just makes it more flexible easier. 18:05 It is flexible because we can edit it in the app script , right?

We 100% You can keep it in the app script and say we are just going to keep it right here. 18:15 If you need to edit it, edit in the code. Well, that's all well and good, but maybe we want to create a little test. 18:21 We want a test between temperatures from zero to one and we want to do it, you know, maybe point one point two point three. 18:28 But maybe we also. We want to do point zero one point zero two point zero three. We want to see what is the. 18:36 Like where can we really hone in tokens?

We might want to, you know, test between a hundred a thousand four thousand, but also we might want to test between a hundred hundred and two hundred and three hundred and four, right? 18:50 . Same as top P same with frequency. We might want to create a grid where instead of doing just one test at one time, we can create a grid of all these tests all the time. 18:59 I had done in a previous video or post, I did like a hundred and sixty eight jokes, right?

Because I wrote different inputs and also different prompts and I created a grid . 19:11 That can be done now with this GPT function that we've now created and it makes it so much easier to use this function because we added this JS doc sort of custom function text right here. 19:24 That makes it really cool. So we don't have to come back to this app script time and time again. Remember what do we have to put into this function ? 19:32 Really cool.

I hope you learned a lot in this video about just making this app script a little bit easier to use once we have the app script done. 19:40 And we're like, okay, we want to change these variables, especially with AI, especially with GPT now. We want to be like playing with this stuff, right? 19:47 There is a playground on open AI's website. But like we want to create our own playground. What happens when we change this temperature? 19:53 What happens when we change the max tokens and the top P? What happens when we change the stop, right?

I haven't gotten into it yet, but there are also how do you change the system role? 20:05 How do you do assistant and user prompts? How do you add? In multiple prompts, if they are, do exist. So we have to do a little bit more here if we wanted to create a real chat experience in here, but maybe we don't. 20:18 Maybe you're using chat GPT right now to do a lot of writing and a little micro copy or some kind of copying. 20:25 You're like, I want to see what how it twists three or four different ways, right?

Maybe you're not looking to test the temp. 20:30 Which is, you know, from all the way from zero to one, but you do want to see what is three or four different, how, what do three or four differences make in writing and GPT? 20:40 And chat GPT might not be, you might be copying and pasting already all of this information and you're like, I want to just scroll down and just copy this once and for all. 20:51 Right? Use Google Sheets to aid you in your writing on in GPT.

And I hope this was exciting for you and hope you learned something with GPT in Sheets. 21:00 And if you are a Google Sheets member, if you are a better Sheets member you definitely can get this sheet that you're looking at right now down below on better sheets. 21:09 And also the original, original video where we did updated it to 3.5, this API call. You can get that. And you can get the original one as well that we just did to DaVinci 3. 21:20 Hope happy writing and hope you enjoy working with GPT in Sheets.</div>