I take an api call to ai and turn it into the Ultimate Prompt Writer and Prompt Rater using Google Sheets Apps Script to save all my prompts, and responses into a sheet.
Watch Writer Better Prompts on YouTube
Read the Transcript:
(00:00) Hi, welcome. So in this video we're gonna create the ultimate prompt writer. What I mean by that is that we are going to write a prompt here in b2. We're going to execute that prompt to AI through an API call. We're going to get exactly what we write in b2. We're gonna put in B four. We're gonna take the response that we get out of that.
(00:20) Put it in CFA four here. And then we're gonna insert a line each time so that our writing space is exactly the same place and we're going to be able to rate these. And that rating is gonna stay with the prompt and the response as it goes down, as it as more lines come inserted into here. Why I thought of this is because I just finished a headline writing ultimate headline writing sheet.
(00:48) And I realized as I was doing that, that I was going from the top down and I, all of the writing and all of the prompt, engineering that I was doing, all of the writing was I did it on the next line down. And so I had to scroll up through the past resp responses. I also didn't feel like. Sorry. I knew that the way that I was using the custom function of G P T, which again, in, in another video I wrote, G P T, the function that you can put all of the inserts in, I realized like one every time that is sits in
(01:30) a cell, you're gonna get every 10 minutes it's gonna do an a p I call. That's just what happens. What we really want to do when we're writing AI inside of Sheets is we want to write the AI prompt, get the response and done right. There are some other situations we might want to create where we might want to create chains of prompts or sequencing or prompts in parallel.
(01:55) That's not part of this video. This video is merely. Trying to figure out a better interface for writing prompts. And then also we want to be able to rank these and rate, rate them, and then ultimately rank them or understand what are the changes that we did to elicit this response. Because I remember as I went through that headline video every time I would move a.
(02:18) Row or even add a column. As I was creating that and adding more and more stuff, the prompts were prompting again. And if you have a your temperature, let's say is at one and you're like, I'm gonna, it just indiscriminately, reapplies the prompt to ai, the response you get might be different. It will.
(02:38) Make it different. So I wanna see what happens the first time. We can always regenerate the prompt by just generating the prompt again, the same prompt again and again, and see if there's different responses we can rate that. Say, Hey, there's large variability in this. It's variability of format and also why I talk about best or better or when I say these words, it really means to you.
(03:00) Or if I'm writing it, what is best to me? Does that mean that is it is consistent or is it inconsistent? Do I want more variability in the prompt or do I want more consistency? In addition to that is the response I'm getting what I expected. Yes or no? Or is it vastly different than what I expected? Is it longer or shorter? There are so many ways to rate it.
(03:26) So in this case, I'm not gonna show you too many sort of things. I have some other videos about rating and ranking rating domain names where you have a series of numbers that you calculate say length of plus. Is it good? Is it money making? Is it interesting? And all of those are added together into one score.
(03:46) I have that in another video, so I'm not gonna necessarily do that In this video. This video, we are just gonna cobble together this app script that is gonna call the api. We're gonna write this prompt here. We're gonna move this prompt to down to, we're gonna get the response here, and we're gonna be able to read it here.
(04:02) So watch as I do this, I'm going to, first off, I'm gonna do a couple things to clean up the sheet or. Create our situation that we need. We need app script, which I haven't written a single app for app script for this yet. I also want to just go ahead and delete all of these rows and columns that are extra.
(04:21) I do that now because I know I'm adding rows to this. I might actually end up a adding columns as well, but for this particular instance, I know I'm gonna be inserting a row right there above row four. I am also going to need to set up this sheet. I realized from for the prompt and for accessing ai, I want to go up to extensions, tiny sheets.
(04:45) I need a one by one sheet and I'm gonna call this API key. By the time you watch this video, I will have deleted this API key. So don't worry about it. You can see it now, but this is where I put the API key. I'll put a little message there. It's insert API key. We also need, so we need that.
(05:00) We have our app script. I took it from the, if you watch the video upgrade Google Sheets a open ended promp to gp, GP T four. We, you're gonna be using GPT four. And it is gonna be a few. We're just gonna insert a prompt and get a response. I have tokens at 2000. I have, I think all of the like temperature I'm gonna put to 0.5.
(05:22) I think I'm gonna put everything to 0.5. We're not gonna really mess around with that in this video. So if we look at our code we also don't have any system message. It doesn't really need it right now. But go, these are all types of variables that we can look through and see if maybe at the end of this video we'll go through them, or in another video we'll walk through different versions that we did that in the headline video.
(05:44) So I don't really wanna do that right in this video in. We're gonna use the function, G p T. So that's all we need to write in our function here. And we'll be able to write g p t, put in the prompt and get a response. And this should be pretty cool, right? We need to execute some script here with insert.
(06:02) We're gonna need a image as well of a button or like achey, PT button, maybe. Okay, so I got the cut. Background color of Che G P T. We might change this up here, but I just wanna go insert an image over sales. I'm going to grab the screenshot that I have here, put that in. And then we're gonna ultimately assign the script here.
(06:26) But I just wanted to show you this, what it looks like. We might end up changing this background to the whole thing. Make it look a little bit in more interesting, or at least closer to chat, g p t. All right. So we wanna see some interaction with this script. We want to make a script. Let's call it chat.
(06:40) We'll have prompt we don't need prompt actually here, we need variable prompt to be Let's call our sheet chat down here. And so we're gonna do spreadsheet app dot, get active spreadsheet get sheet by name. We're gonna get the sheet chat. I hope I spell it. Yeah, all lowercase dot get range.
(07:05) We want the range to be b2. In quotes, B2 dot get value. So we make sure that we actually get the value of that whatever is written inside of b2. We want to run a variable response sequel to G PT Prompt, and this prompt will be whatever's in b2. We're gonna run it through this a, this G P T prompt and get a response.
(07:35) We wanna set that value. So one thing to we want to do too, the moment we press that button, we need to insert a row. So we're gonna do. Spreadsheet app again, dot get active spreadsheet. We want to get actually all of this again, so we don't wanna type this. We're gonna do chat. Variable chat equals all of this.
(07:54) Now go back here, write, chat and now we don't have to write that all again. We can just do chat dot, get range our range. Actually we don't need to do that. Insert row before. And then what is the row? We want to do it before. I think it's four. We can double check this four. Yeah. We want to insert a row before four and then we wanna go to chat dot get range.
(08:21) We want, we wanna write in B four, right? Every time we insert a line, all the other lines are gonna go down and we're always gonna have B four here. So we just can literally write before set value. And we want response. Literally the response we get from this G p t prompt and this function. G P T, we wanna write it in B four.
(08:43) Is that what we want? Actually, no, we do not wanna do that. We want it in c4. Sorry. C4. What we want to write in B four actually is what we have in is the prompt. We want the prompt to be written there. What we have in b2. Let's go back and see what we have in b2. We want written in B four. We get the response.
(09:03) We want it in c4. All right. Whew. That was a close one. Wait. Oh, we didn't do that correctly. B four. Set the prompt, set the value as the prompt in c4 set the value. So we're doing three things. We're inserting the row. We are setting the prompt, literally just taking what's in b2, putting in B four, and then taking the response that we get from the AI and where we're putting it, setting it value in c4.
(09:26) Do command s and our function has no variable in here. It is just called chat. So we can ideally go to our button, go to a sign script, and we should be able to just write chat Now what is the app script for writing to a cell? Let's just ask that here. We might want to wrap this all around and then we wanna run this script.
(10:00) It's running the script. We do have to do authorization. One second. Once we did the authorization we had to run the script again. It is running script. It's taking a while. I think it might be because it's G PT four. We might wanna change this to GPT 3.5 Turbo, which was a little bit faster. We got a response.
(10:43) Yep. We did all that. Set the row and column numbers where you want to write the value. Okay. That's, this is too verbose, but variable value. Then write the value to a spec to a specified cell. Yeah, sort. Yeah, you can do it there. That's what we did, right? It writes Hello world to sell a one of sheet one.
(11:06) Yeah. Perfect. Not really, but gets the job done, right? If you were learning APS script and you're like, oh, how do I do this here? Yeah. Now you can piece together all the pieces, right? But like right now we're rocking and rolling, right? We have our prompt. We can maybe make a new chat. Let's what if we wanted to start like a, not a chat.
(11:30) I'm not recreating chat, G p T here right now. We want to rate these prompts, right? We want this some rating here, some system. For my own sake, I'm gonna create a dropdown. I'm actually gonna create a ratings here. I only need one column and I'm gonna go Good. Me bad. Like the result was just bad.
(11:58) We're gonna take this dropdown from a range, we're gonna call it ratings. Exclamation point, a colon A, we can do that because it's only gonna get the unique function, the unique versions. We go back to our chat and we see. Good. Me bad. All right. Let's say, this was me. Wasn't that great? It was good actually.
(12:18) It did exactly what we did. We needed, right? It was good. Yeah. Pretty much. Good. Let's see. Now if this dropdown copies over. When we insert a row, so I wanna do a whole nother thing. I wanna just change it up and say, what is app script? Just ask a simple question. I'm gonna run this script. We're gonna get the response.
(12:44) I'm actually gonna go after we do this, I'm gonna change. I'm gonna change the tokens to much less because I want it to be a little bit faster. I want these tokens to be me. It should be a very quick answer. So like 300 will save that, but it will give a verbose answer probably now. Yeah, let's call that scripted scripting platform developed by, yeah.
(13:09) Cool. That's met, I think this is a short prompt. I think we can do way better and I think we can. Also be better suited towards whatever we need, right? If we're creating headlines if we are creating headlines, we might include some examples. If we're writing some, introduction, if we're doing some SEO optimized thing, we are gonna wanna write much more in this prompt.
(13:32) We're gonna write things and we're gonna see that response, right? And we're gonna see is it different? I wanna move this actually. Here. Actually, I wanna make this much bigger now that we've made this. That looks weird, but it's fine. And you know what, I will do this. I will change the text color to gray, change the background to this, change, this background.
(14:01) I wanna change this background actually to white. I wonder if this is gonna mess up anything. We'll see. Change this text. Actually needed to be gray. There we go. Very interesting. Maybe we want titles. What is a good title for a video about writing? The ultimate prompt writer for AI in Google Sheets. And we're just gonna get one answer here.
(14:27) We're gonna run this script. Oh. Oh. We wanted to change the Make it a little faster, right? What is good to creating the ultimate? Okay. If I wanna make it shorter, what is a good short title for a video? I'm gonna do more than short, but for right now, that's the only change I'm gonna do. And let's see what it comes up with.
(14:50) Great. But I actually wanted two words, A good short two word title. Let's see if just adding those two words, literally the words two words, makes a big difference. Prompt wizard. Yeah, it does. Makes three words. Good shirt, three word title for a video. Maybe adding YouTube. For a YouTube video about writing the ultimate prompt for ai.
(15:20) Let's see if that changes anything. Oh, nope. Doesn't change anything at all. Okay. This is. It is still me. All of these are still me. And if we wanna rank and rate these differently you're more than welcome to rank and rate these in a different way. We can put in dropdown menu with like maybe a thumbs up, a thumbs down.
(15:39) We can then filter for all the good here. And we can say equals filter range is going to be, maybe we'll take all of that and we'll say condition one. Chat. A colon a is equal to good. Yeah. Let's change this one. Back to, eh, this is, man, it could have been better, but now we have no good. So if we do, if if error, let's just, Put an if error there.
(16:09) Now we have nothing but we are going to get the results here. So we'll have prompt and response. And this is only the good ones. We can delete all the other columns. We can even delete actually all these rows. We don't need these rows either. It will add rows if needed. Delete rose. We want some more space.
(16:31) Let's get some good ones, I think. What is a good three short three word? Let's see. Actually four words. Short title for YouTube video about writing the ultimate. Good examples include great. Way to go. I don't know. You would put in whatever you want here. Nice looking sheets. Deo that, that's one word.
(16:54) Dao. Great looking. Let's do that. Let's give it a couple of examples. That is, those are not good, but let's see if it's still going off of this AI prompt wizard unleashed. What is a weird, let's see if that's better. AI writes prompt magic. Okay, let's just call it good. Let's call this one me.
(17:16) Let's say, what is a silly forward title for a YouTube video? YouTube tutorial video. Maybe that might help more like educational. Let's see a prompt wizardry unleashed. Okay, let's just call this good. Now we can see all of our good ones are right here. Our good prompts are here, our responses as well.
(17:40) And we're really rocking and rolling, right? We have a rating system, a very, a fairly simple rating system, right? Some three options. We can see all the good ones. We can also duplicate this and say what are the, like the bad ones. If there are bad ones, maybe we can see a difference between good and bad.
(18:00) And there are none, right? I think actually that was pretty good. This one, let's call it bad for there. Ready? Go. So now we have a bad one and we can be like, okay, here's good ones, here's bad ones. We don't wanna see the man ones. We wanna get through all the cut all those out. We have are using filter, we're using if error.
(18:18) If we have zero This is pretty cool, right? This is a pretty awesome way to be able to write some prompts, get better at prompt engineering, get better at prompt writing and see all of the examples. I think the last thing we wanna do is we wanna be able to duplicate this chat. Maybe you wanna start this all over, and I would say one way you could do that is just to create more sheets.
(18:42) But let's just say we want. Different tabs for different prompt writing exercises, right? Maybe we're trying out different examples. Maybe we try out different functions or out a different model. I did want to change this to 3.5 turbo to make it a little faster. So now that these are all These are all the using G D four, and now I want to use GPD 3.5.
(19:06) Maybe I want to split them up a little bit. So let's make an extension app script that is going to duplicate the page and create a new page. So function new chat. We'll call it new chat. I don't know if it's actually a chat we want to take. Variable chat. We want that page. And we probably wanna keep the new one called Chat and archive.
(19:33) The other ones, like we want, we might want to take this Go chat dot copy two and using copy two, what we can do is we'll copy it to this exact sheet. So our destination is gonna be this. Destin, the sheet we're in now spreadsheet. We need all of this. I think we can test this out. Let's save this.
(19:56) We can run new chat. We pro, oh, we want to set the name, set name as chat, and then. Plus new date. We are gonna get a bunch of formatted, non formatted information here, so we don't really want to that date. We want One second. Actually, what we're gonna do is we're gonna take the date, we're gonna use utilities dot format, date, new date gmt.
(20:24) We're gonna use for just the sake of using this. And so now we just do date. This is using the day. We might wanna also add in the time, especially if you're gonna do multiple of these in Within a day. But if you're like, I'm gonna come to this once a day, I'm gonna try to little bit of prompt engineering and I just wanna save it for each day.
(20:45) Let's see what this runs. If we run into any errors, we will see that in a hot second. And there we go. We have our chat. So now we have copied what we have done. We're not gonna lose that, we're gonna copy that page. But what we might want to do is we might wanna delete. Rose five down and we want to clear out what we have in four and b2.
(21:10) Okay? So in this new chat, we're gonna copy that first. Then we are going to go to chat, dot get range b2, right? Set value insert prompt here. You can just give a little message. We're going to do chat dot delete rows, and let's look at how delete Rose works. We have a row position integer, and then how many this starts at.
(21:39) One is gonna delete the first one. Two is gonna de delete the first two rows. What we want to do is we wanna do five, we wanna delete the fifth row. And then down to we need to know how many rows are, so we're gonna do chat.gi max rose, so we know this variable. Rows equals that. And we're gonna do rose minus five.
(22:07) I think that's what we have to do. Rose minus five, go that many down. Let's see what happens, right? We have chat. What we want is five down to be deleted. We're going to go back to our new chat. We're gonna run it again. Oh, we're gonna get an error here because it already exists. See, this is the issue with having these only the date we, maybe we'll add the time back or we'll just delete these as we go.
(22:36) Let's run that again. New chat. Let's see if we get any errors. This time? No, on our chat. Oh, we have something else here. Oh, this was probably the ro. I think we had this row at the bottom here. Yeah, this is it. Okay, so we did everything except the fifth row or the very last row. I think we need then to do minus four here.
(23:04) Let's run it again. Let's add some more. What happens if I want to delete or how do I delete rose in Google Sheets? We just want to write something. We just want to get some prompt here. Let's see it do its magic. Yeah, so we have a response here. Now. We have five and six. We also want to write the code to clear A four, B four, and c4.
(23:32) So let's do that chat dot clear. Contents, I think, oh, nope. Do get range. Dot clear contents. So we're gonna use range A four to C four and that will hopefully clear out whatever left here. So we want to make sure delete this one cause it's gonna have the same name. Okay, now we saved it and we run new chat.
(24:07) Do we get any errors? Yes, we get an error. Because clear contents is not a function clear content. Okay. That is why I think we also need to go and delete this. Okay, great. So we have all of this. Okay. We, yeah, we, it did everything except this clear content. Let's do that again. Just make sure it will also do that.
(24:35) Those. Yeah, because we don't have anything to delete. Okay. So we want to only delete the rows if rose is greater than four. So we'll put a little gateway there. If Rose is this Max Rose, if it's over four right now it's four. If it's over four. We wanna delete everything from five down, so we don't need that unless we have these rows.
(25:05) Cool. Let's save that. Let's run it again. Wait, no, we don't wanna run again. We wanna go and delete this. Gotta remember, delete this before we do it again. If we wait a day to do it, it'll be fine. Alright, let's run our new chat. Shouldn't get that error anymore. Great. Let's go to our chat.
(25:25) We have a clear thing. We have our. Dropdown still exists. We have a blank insert prompt here. Message. We are still running the script. I don't wanna actually run that. It very, okay. That's what it did. Cool. I'm so happy we got this ultimate prompt writer down. We can rate and rank our rate all of our.
(25:49) Prompts. We can write them, we can edit them. We can see the difference between these prompts as we go. We can add much more. We can add a bunch of characters into these. Hey, watch more videos here. Watch how to make that viral plant, that money tree inside of Google sheets. Check out how to program in Google sheets.
(26:06) I do a very quick crash course here. And that leads into more kind of spreadsheet automation stuff, but check out this video too. If you want to learn how to write apps script.
Become better at Google Sheets every day too!