0:00 I'm going to show you really quick how to create an AI-generated poem based on an input, like a name here.
0:06 We have a sheet called poems, we have two columns, name and poem. When we enter any name here, like Francine, and we hit enter, a poem is going to show up in the B column here that is generated by AI.
0:23 How do we do this? It's through AI, it's through some Apps Script. I'm going to show you every step of the way, and I've already coded some of this for you that you just have to copy-paste.
0:34 So sit back, relax, and enjoy this ride through creating an AI-generated poem based on a name. We're going to need a few things.
0:44 One is an API key, you're going to get that at OpenAI. The second thing is over at betasheets.co.uk slash snippets, I've already created this updated OpenAI to actually delete this, and we're going to paste our AI prompt.
1:18 We're going to do a couple of things to it. One, instead of a prompt, we're going to just need a name.
1:25 Instead of API key here, I'm going to add it as a global API key equals this here. By the time you watch this, this API key will be deleted.
1:41 We have prompt here. We still need to add back prompt variable, prompt as a variable, and it's going to be pretty simple.
1:49 Create a short two-line rhyming poem with this name, period. then name. Quo, uh, colon plus name.
2:05 So this name here in this AI function will be here. And then we're going to use this exact prompt. We need to change API key as well.
2:13 Let's do that here to change it to that. And let's go get an API key. I've already gotten an API key.
2:21 Copy it. I'll have deleted this by the time you see. We're going to put it in quotes here as API key.
2:30 We can test if this is going to work actually by just saying variable name equals Nancy. And let's run just AI right now.
2:41 The first time you run it you'll have to generate, uhm, you'll have to authorize it. Thank you. And instead of return, let's actually go and logger.log this last piece, this json.choices.message.content.
2:59 And let's run it again and you'll see it right here in this log. Nancy dances with endless glee, her spirit wild and fancy free.
3:07 There we go. So it works. We know it works now with this logger. Let's delete that. And go back up and delete this name and put it as a variable here.
3:17 So we need one more thing. We need a function that actually goes to that AI function, returns the poem, and then enters it where?
3:26 Well, we're going to have a name in the A column, and we need to enter it in the B column, wherever that row is.
3:34 So, we're gonna call this function, createPoem, and we're gonna need an event, because what we're gonna do is, basically, we create this function, then we create a trigger that says, when something happens, go do this, this function.
3:51 So the very first thing we need is the function to trigger, though. And then we'll build the trigger in this video.
3:57 It's gonna be super easy. We need a few things, though. Variable ss equals spreadsheetApp.getActive, getActiveSpreadsheet. That's just gonna be the entire file.
4:09 We're gonna need that, generally, most of the time when we're creating functions here. We need to know what row we're on with this e event that's going to be triggered, So, e.range.getRow. I wonder if you know it.
4:26 Got column. There it is. And we need to know what sheet name we're on, just to make sure we're on the correct sheet name.
4:34 Equals ss.getActiveSheet.getName. Now, we can log this, but I'm just gonna keep going.
4:48 Thank Bye. Now, we're gonna do if sheetName is equal to poems. So let's put an ampersand here and col is equal to one.
5:18 We also want to make sure we're not in the header row. This is just a little bit of a optimization.
5:23 We don't want to be changing the header and then it creates a poem there. Uh, there is one more thing we can add here is essentially we don't want e.value to be blank.
5:35 Thank you. Exclamation point equals means not equal to, two quotes with nothing in it means blank. But now, once we have the, these all are yes, true, well, we want the poem.
5:54 But we want the poem in a very specific place. We want it in the same row we're on in that sheet.
6:00 So we're going to go ss. ss.getSheetByName, poems, because that's where we are, getRange, we are in row, whatever row we're on, and in two, the second column, we want to set value is the poem.
6:20 Well, how do we get the poem? We'll go variable POM equals AI and put in the name. But how do we get the name?
6:27 Oh, that is just variable name equals ss.getSheetByName, poems, getRange, whichever range we're in, in the row, first column, getValue.
6:46 So, here we're getting the value of the first one. First column, whichever row we're in, and here we're setting that value as the poem.
6:54 And here we're getting the poem. Pretty simple, right? Let's save it. Now if we save this right away and try to use it right now, this createPoem has no trigger.
7:06 We're not triggering it anywhere. So now I'm going to show you how to create the trigger. over on the left side, go to triggers.
7:13 We already had created one before. It's a test. So let's delete that. This is what it'll look like when you open your triggers.
7:19 We're going to add trigger. It's on the bottom right, big blue sign. We're going to choose which function to run.
7:25 It'll be createPoem. Now this event source will be from the spreadsheet. Keep it as that. But this event type, we're going to change this to onEdit.
7:36 Let's click save. Once that's saved, we have it here and we'll see if there's any errors. So let's do something like this. If we write a poem.
7:49 This is a poem. Nothing's happening. Let's write a name. George. Does anything happen? Yes. It took a moment because it does have to do a little bit of a, a run over to AI, get the response and come back and then set that value.
8:09 In a land of dreams and old folklore stands the bold and brave Sir George. I don't know if George and folklore actually rhyme.
8:16 We can try that again. Let's just delete that. And go George again. See if it comes up with another one.
8:25 Let's delete, delete, George. Will it work this time? Let's try again with a Gary. Seems like it might be just a little bit slow.
8:45 Gary, with dreams that never tarry, finds adventure in worlds unwary. Okay, it's a little weak, but it is generating these poems based on the name all in this script here with the trigger.
8:59 So, to review, definitely need an API key. We need to create the function AI, which I've already written for you over at bettersheets.co slash snippets.
9:08 This one is the updated OpenAI model to Omni. we need to get that AI function, but then we need to trigger the poem creation, which is just grabbing whatever we're editing, making sure we're in the right place, getting that name, putting it through AI, getting the poem back, and setting that value.
9:27 So it's this three steps here. Thanks so much for watching and learning along with me. If you're watching this on bettersheets.co, down below is this sheet and the code all written for you.
9:41 If you're not yet a member, become one today over at bettersheets.co.