GPT in SHEETS

Unlock the Power of OpenAI: Create Ultimate GPT Prompts for Seamless Interaction!

Discover the Formula Inside, Explore APIs, and Boost Productivity. Watch Now!

Watch GPT in SHEETS on YouTube. Read the full transcript here.

Transcript:
(00:00) All right, cool. So in this video we are creating sort of an ultimate g p t prompt inside, not prompt, but formula that includes prompt, that includes the temperature, that includes all this stuff, everything that you will be able to interact with open ai. In another video, I've done the app script that actually connects the api to to open AI from sheets.

(00:22) So if you want to get that, Watch that other video. There's a couple videos, one called Quick Start in quick Start Open AI tutorial. And then there's another one that, that one uses Da Vinci three model, but there's another one called Upgrading into 3.5 and 4.

(00:43) 0 Upgrading the G P T model that takes a different messaging syntax. So we had to do those updates. In this video alone, we're going to. Just interact from the sheet to the model. Much more interaction than we can do. We don't have to go into, basically this makes it so that we don't have to go into app script and make those changes.

(01:02) We can do that all from the sheets here. There is a couple of plugins or rather add-ons that sort of do this. But I wanted to take out that middleman and make sure that we were able to interact directly between the sheet and open AI's api. Again, that all is in another video. This one is specifically about the interacting between the sheet and the app script.

(01:25) Let's go over to our project editor and see this. App script in action. We have open AI four. That's where we're calling it right now. I might change that, actually. I will change that in this video to G P T. We have just a prompt and then you can see the messages here, which we went over in the upgrading video.

(01:41) We have all of this hard coded in here, all of this temperature, the max tokens, top P frequency penalty, presence, penalty stop. 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. We're not going to go through each of these individually.

(02:00) That'll be in some other videos. And are available online in prompt engineering and everything. We're just gonna take a normal prompt. We're gonna add all this stuff, but we're gonna put it right here, up here in the function, and we'll make sure we have it written so we know what's going on.

(02:16) We don't have to remember this order, and you'll see why in a second in the, in this video, we're gonna create a custom function from this. So let's get started. First things first is I don't want to hard code the API key. I have the API key on another Tab, we can just do tiny sheets.

(02:33) Let's create a one by one sheet. And this is what we used to do is just call this API key, but I don't want to put the API key in here. I want to be able to put it anywhere I want, and then just reference it in the function. So first thing we're gonna do, call API key, put a comma there and we're gonna delete all of this.

(02:51) And in the function down below again, go over that in a different video. The API key is the variable name is a p i 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. Let's do that. All right. By the time you watch this video, I'll be deleting this API key, but here we go, where're, just using the function open AI four, we have in quotes, our API key, and then we have a question, what is an API key? And we're asking this to ai and here's the answer.

(03:20) And API key is a unique identifier, typically a long string of letters, numbers used to authenticate, blah, blah. This is the answer, right? And all we had to do was change that API key to put it into the function. All right. There's also this role we can do. There's also a model. Actually, let's do that next.

(03:37) I don't want to limit this to G P T four. You can actually use G P T 3.5 turbo if you want, with this particular syntax or four. 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. Now we have three things in this function, api, key model, and prompt.

(03:56) And this model is right here. So we'll save this and actually at the same exact time, I'm gonna rename this function to G P T. And we'll get an error here. We'll just rename this G P T. And then we have our a p i key. And now we need our model. In quotes, we'll do G P T four, and we will ask a different question just so we see a different answer.

(04:20) What is an api? Get call. We'll ask that question. This will take a moment to get back to us cuz this is G p T four. It seemed like when I was doing my test, G p T 3.5 turbo was much fast, much ish, fa ish faster than g. PT four. And here's our answer. An API get call and here's all of our informations.

(04:41) As we're doing this and as we're editing this G p T function here and we're getting this response back, I realize let's actually use Google Sheets to its maximum, right? Let's take this API key, let's move it out to another cell and actually reference a cell in this cuz we can we are gonna do this in b.

(05:02) Two actually. We're gonna paste that function, but we're actually gonna put this call, let's call this AI here and we're gonna call this API key. So just in case we might want to use different API keys down the line. And we might wanna do other things here. Let's move this model, and actually we're gonna change the model too, but we're gonna change it to c2.

(05:28) And put the model here. Put G P T four and call this model. And then we're gonna use the prompt. Take that away and put in D two. 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. All right. We have prompt. Okay, so now we have API key, our model, our prompt, it's all in a sheet here.

(05:55) What else can we do? We want our role. So this is the system role. Again, we're just gonna delete this and put it into here system. 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. It's gonna get a little complicated, but let's just do that first.

(06:18) We got a system. We need to make sure it is the third thing here. So we'll do E two and we can leave this blank. It, for now, it actually, I think 3.5 almost. It sometimes ignores the system, but let's, we have the prompt, we have the system and our max tokens right now is only 500. Let's change this.

(06:39) To an input as well. So we have, we gonna get, we might get an answer here. Doesn't really matter. We're gonna keep working on this. Maybe we wanna put our prompt in different place we can in a moment. But let's put temperature. I'm actually, we'll call that just temp and instead of one here we'll do temp.

(06:59) And now we know we have a prompt. Let's actually move this over so that this is an order BC B C D E system prompt. 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 F two for temperature. What do we want here? Actually, let's do just zero.

(07:23) We had it one. In the hard coded, but here we can also change it, but we can say zero one. And I do want this to go faster a little bit. So we're gonna do G P T 3.5 turbo as the model. 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.

(07:46) And now it's pretty quick. So we have our prompt. We can ask, what is the translation of we can do something like, what is the translation of heart ache to clinging 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, but you can see the GP D three point.

(08:07) We'll see if GP D 3.5 is faster than that four was. Yeah, that was much faster. Let's just format this to here. Ooh. Okay. I guess that's an answer. Let's keep going. We have max tokens. 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.

(08:28) 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. 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, presence and then stop. And we'll just do that frequency press and stop.

(08:55) Okay. See, all we have to do is create, write this variable up here in this function area here, knowing that whatever is there when we enter that function in the sheet will go through all the way. So we have tokens, then top P, then frequency penalty. Then we have, what is that? Presence, penalty presence.

(09:21) Penalty. I don't know if I spell penalty correctly. Penalty frequency penalty. Then what was it? The last one was Stop. Cool. And again, we have to go through this G P T and now we know where we're just in the right order. Hopefully G two, H two, i two J two. I think I want to have Toppi one. One. We might have, we need zero one.

(09:50) We can look back and see if there was any difference, but we have Toppi frequency, PRI presence. So right now, if we take this formula, we want to do equals G P T, notice how nothing happens. There is no auto complete. There's no message in here there. It doesn't tell us anything about G P D. We would have to remember all of this, and particularly we have to remember all of this order, but that's not very helpful.

(10:16) What we can do is add this text in front of it, which is called JS Doc. It's this slash. Asterisk, asterisks, and it's the 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, but automatically all of this will be available to us with auto complete inside of our function when we're right inside of our cell when we're writing it.

(10:43) So we're gonna do equals G P T, and now. Right away we have autofill. I dunno if you can see that. We'll scroll in a little zoom in a little bit, but here it says, G P T generates a response from an open AI prompt. And when I hit the parenthesis sign, now we get to see each and every one of these variables.

(11:03) So all of these variables that I shortened, we can actually make longer and be able to now exactly know what it is. All right, so we can change this to from temp to temperature. And the only thing we have to do is change this variable here that we use temperature tokens. We can't even call this max tokens maybe.

(11:27) And that reminds us, oh, that's max maximum tokens. So let's go back here. Let's do that parenthesis again and see now we have temperature max tokens. This top P, we can actually. Call it top top p instead of top with two Ps. It can live better in our memory of oh yeah, that's top P instead of top.

(11:52) Do we misspell that frequency? We can take the entire text here, change that, change frequency, change presence, penalty. 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. So we don't necessarily have to remember this. This makes it so much easier to use this G P T function inside of sheets.

(12:17) And our a p i key can be anywhere we, what we can do here is have some settings. This sort of is a fun thing to do. Add one column to left. Let's add another like five rows and we're gonna do API key here. We're gonna do what was the next one model. And this is if you wanna set these individually and only once, right? We could take this G P D function and just copy and paste down.

(12:46) Now we can copy our API key over here. We can even try different models. Let's actually, we're gonna get some error because we don't have all of this stuff copied here, right? We don't need the system. We can see what is the difference between these two prompts with this same prompt, different models.

(13:04) 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. We can see compare, the difference. We can take this whole. Row, copy it again a couple times and change the temperature to 0.5, to point to one. We can then, keep ch, keep changing each of these things and say, now we can see the difference between each of these.

(13:30) Items. Sorry. One, 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. So instead of our B five is our API key, we can set it API key only once, and we don't have to copy it all the way down here.

(13:53) Say API key is there, and now change this b2. Two B one 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 one that way, wait that way dollar sign in front of the B dollar sign in front of the one. Dollar sign hit enterer. And now as we copy and paste it down, you can see that the B one for settings doesn't change.

(14:21) That cell is now referenced all the time, but the c2 c3 see it changes C4 all the way down. Ij all of them change the row without changing the without changing. The column. All right. But other settings we can do as well. Cause we're not gonna, we might not want to test different models. We might want say, Hey, we're just gonna use 3.5 turbo all the time.

(14:43) And we can just place that there. Another thing we can do with sheets is instead of having to type this out, so say we want to change it to G PT four and we're like, do we really want to have to remember, is it G P T hyphen four? We can go to a create a dropdown and just do G P T. 3.5 Turbo. Actually, should I did spell that wrong.

(15:07) 3.5 hyphen 3.5 hyphen Turbo. And we only have to get it right once. And then G P T hyphen four. There we go. And 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 ops script, we can keep it the same in the settings. And we can also have this dropdown menu that changes this from GT 3.5 to four anytime we want.

(15:32) And it'll change all of our everything. We just have to make sure that we are setting this correctly. So we do same thing we did before, settings, exclamation point. I think it was b. Two 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. We're gonna use G P D 3.5.

(15:56) It is B two for the model. So these columns don't have anything to do. We delete them and now they have no impact whatsoever here. So that means it, we have a lot more room to put our system prompt to put our prompt to put temperature to play around with tokens a little bit. We can see what is the difference if we do a hundred tokens, 500 tokens, a thousand tokens, we don't have to deal with, changing that model.

(16:22) And as we do that, maybe I'm not gonna do it in this video, but each of these, as we test them out, we'll find the ideal setting, right? And then go add it to the settings. Do exactly what I just did, add it here. Change it in our G P T here. Now, why would we do that and not put it in the app script? This just makes it more flexible.

(16:44) Easier. It is flexible because we can ch edit it in the app script, right? We a hundred percent. You can keep it in the app script and say, we are just going to keep it right here. If you need to edit it, edit in the code. That's all well and good, but maybe we want to create a little test. We wanna test between temperatures from zero to one and we want to do it, maybe 0.1, 0.2, 0.3, but maybe we also want to do 0.01.

(17:11) Point zero two, 0.03. We wanna see what is the re, like where can we really hone in tokens? We might wanna, test between a hundred, a thousand, 4,000, but also we might want to test between a hundred and two, a hundred three, a hundred four, right? 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.

(17:36) I had done in a previous video or post, I did 168 jokes, right? Because I wrote different inputs and also different prompts, and I created a grid. That can be done now with this G P T function that we've now created and it makes it so much easier to use this function cuz we added this JS doc, this custom function text right here that makes it really cool so we don't have to come back to this app script time and time again to remember what do we have to put into this function.

(18:06) Really cool, and 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 and we're like, okay, we want to change these variables, especially with ai, especially with G P T, now we wanna be like playing with this stuff, right? There is a playground on open ais website, but we wanna create our own playground.

(18:25) What, what happens when we change this temperature? What happens when we change the max tokens and the top p? What happens when we change the stop? I haven't gotten into it yet, but there are also, how do you change the system role? 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 wanna do, create a real chat experience in here, but maybe we don't.

(18:49) Maybe you're using chat g p t right now to do a lot of writing and a little microcopy or some kind of copy, and you're like, Ooh, I wanna see what, how it twists three or four different ways, right? Maybe you're not looking to test the temperatures, from all the way from zero to one, but you do wanna see what is three or four different.

(19:05) How, what do three or four differences make in writing in G p T and chat? G PT might not be. You might be copying and pasting already all of this information and you're like, I wanna just scroll down and just copy this once and for All right. Use Google Sheets to aid you in your writing on in G P T. And I hope this was exciting for you and hope you'd learn something with G P T in sheets.

(19:29) 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. And also the original video where we did. Updated at 3.5, this API call. You can get that and you can get the original one as well that we just did to Da Vinci.

(19:47) Three. Hope happy writing and hope you enjoy working with G P T in sheets.