00:01 In a previous video I showed you how to use this API prompt and response to get AI into Google Sheets But I want to show you one more step because there sort of is a problem with this I already have a sheet set up with my API key if we type in a prompt here, write a sonnet about Google Sheets and we 
00:23 want to get the response from AI here, we have to go to extensions, Apps Script, we're going to paste this exact code here it opens up in a second and here we have our function AI now what happens is when we go into B1 we do equals AI and we'll do A1 and we'll get a response right um this function will
00:53 call the API pretty much like every ten minutes or so uh this is sort of just a function of Google Sheets and it's gonna get a new response.
01:04 each and every time like we got us on it about Google Sheets and what we want to do sometimes in Google Sheets is just get the value just get the text so we're just gonna like usually you can think about this like B1 I'm gonna command C copy or right click copy and then right click paste special and 
01:26 values only and now it won't call the API again it won't cost us any more money but what if we could get our script to do that well we have to add another function here and we can do this by function get AI text only I'll call it and we're gonna have some input a prompt actually we don't even need a 
01:52 prompt for this one what we're gonna do is get the active cell we can sort of do a lot of different things but in this particular case what I might want to do is just select the cell and right next to it get the text response but we're without the function calling it so this is gonna be gonna definitely
02:13 need to get this prompt so variable prompt equals spreadsheet app dot get active range so we're gonna select the range and get value okay that's our prompt we're gonna run it through the AI so prompt we're gonna call this variable output equals we'll put this on another here and then now we want to write
02:41 this output to the cell next to it so first off we want to go grab this range again variable column actually first row equals get active range get row and let's say we want the column as well let's just call it column equals we're gonna do exactly the same thing except get column at the end so now we
03:11 know the row and the column of the active range and we're just gonna go to the next column over and paste it so we're gonna say spreadsheet app dot get active sheet dot get range and our range will be row and our column will be column plus one and we're only going to use one cell and one cell we're going
03:36 to set value as output and this output is the AI prompt so we're gonna save this but now how do we call this function if we select a cell here let's write about spreadsheets we select the cell how do we call this function we want to create a function on open and in here we're going to get another uh 
04:09 snippet and this is going to be the on open snippet so we'll go here it's right here function on open we'll just copy all that we can actually paste over that and now our custom menu is going to call we're going to call this AI menu and the first item is get AI text only We're going to call this, uh,
04:32 get prompt and set value next to cell We're only going to have one thing right now, we're going to save this Oh, we didn't get the F in the function, we'll save it again But in order to get this on open tab, on open menu to show up, we need to close this and reopen it So I'm going to call this AI text
04:59 only and we're going to refresh this Once it refreshes, it'll open again and hopefully right next to this help menu, there's our AI menu We'll select A2 which has write us on and about spreadsheets See if we get any errors or if it works right away Of course, we need to authorize it the very first time
05:15 we run it, we have to authorize it And click allow And sometimes we have to do it again, the authorization just authorizes it, see what happens And there we go, we get our response right next to it Pretty cool, right?
05:31 So now, we don't have AI function in our sheet calling it every 10 minutes, costing us money every single time We actually just get the response of the text, API call, and get it right in the cell next to it We can do a couple of other functions here We can do a couple of sort of other user implementations
05:49 or user experiences if we want But I think this one should show you a really good example of creating an intuitive one Right, we wrote this so we know, hey select this as your prompt and it'll right next to it But with this setup, with this set value and just column plus one, you can maybe add it to 
06:07 the row underneath it instead of the column next to it. You can sort of select where you want to set the value You can also set this range as a set range, and this is how I create a sort of an AI writer in Sheets So if we have AI writer, we have an input, and then we have output, and we know every time
06:28 we want to run this we're going to have our output in C3 If we have that, let's do another one.
06:37 We'll just change this to get AI text in C3. And instead of get range and get active sheet, we're going to call this, we'll call it writer.
06:49 Just one word, writer. Git, we're going to rewrite all of this. Git active spreadsheet, git sheet by name, and we're going to call it writer.
07:00 Again, same name, git range, and in our case C3 is where we want to write it, and we want to set value as the output.
07:10 So we do not need the row and the column and the column anymore here. Save this, but make sure that git active, git ai text in C3, we'll change out our function name here.
07:30 Save it, and again, because we need to set this function on open, we need to refresh just to get this AI menu to show the next one.
07:41 Write a 3 sentence note. Write a haiku about Google Sheets. So now here's our input. AI menu, set value in 3, C3.
07:55 There you go. But also, we don't have to select this. Maybe we know this input is always going to be the same.
08:00 So let's change that a little bit, so it's not just an active range. So our input, our prompt, see right here, is spreadsheet app get active get value.
08:14 So instead of that, we want to do get sheet by name, writer dot get range, and our range is b3, b3 dot get value.
08:29 We have to make sure we get the value of that range instead of just the range. So now, I don't have to care about where my cursor is, I can write anything here, spreadsheets, and my cursor can be anywhere, go to get prompt and set value in c3.
08:46 Parameters, don't match the methods for get active sheet. Let's look at that, get active, oh this should be active spreadsheet, that's the issue.
08:58 Okay, go back and let's try this again. Dismiss it. Again, our cursor can be anywhere, get prompt and set value in C3.
09:09 Nope, uh don't match methods, string get active spreadsheet. Oh, that's why, get active spreadsheet dot get sheet by name. That's what we have to have.
09:26 Let's try it again. And get prompt. That should have changed there. Let's say about learning app script. Again our cursor can be anywhere.
09:49 And there we go. Awesome. What if we wanted to save these though? Ah so in this output what we might want to do is yes we want to set the value but before we set the value we want to copy C3 down.
10:03 Maybe all of these inputs, maybe we want to copy both of these B3 and C3 down. Down below so we see all of the past inputs we've done.
10:12 So let's do that. Let's do variable values to save, we'll call it that. We'll use exactly the same writer here, actually getValues.
10:31 We'll actually use the exact same, uh no we don't need to say b3 to c3. And in this case we'll do getValues.
10:40 We're gonna insert a row just in case we don't have any more rows here. So we'll insert a row before the third one, row after three. And then we're gonna paste the values.
11:01 So we're gonna get this, get range, will be be for before. for colon b uh c for set values. And the values are gonna be values to save.
11:19 Okay, so now instead of just writing over the text before, we're now gonna take this text, copy it down below, and then put our new one.
11:28 here. Uhm, just so that we save, uh, this information. Ah, I see actually an error. Before we try it, I want to set the values after.
11:41 Uhm, and we just want to set values, actually we want just B4, is going to be our prompt. And C4 will also be.
12:01 Our output. I think that's correct. Let's test it. Let's see. So write a haiku. So we have, when we start this we have no, nothing here.
12:10 So we'll say write a haiku about AI in Google Sheets. Set value in C3. We haven't changed that name. And we get an exception.
12:26 So let's see. Set values. Ah. Because I think we still have set values. That should be set values. Let's save it and run it again.
12:35 So that should have pasted here. Write a haiku about AI in Google Sheets. Like chat GPT. Dismiss. Let's try again.
12:49 We'll insert a row and paste our awesome. So now we can change any of this. And we can also maybe not even need this output section here.
12:58 But now we delete all of that and we have everything saved. So all of our past AI ah text. Is saved.
13:05 Right? Without formulas, without functions. And it's pretty much like a chat GPT sort of interface except we're not using the last uh stuff to put into the new input.
13:15 We're just creating new inputs here. Write a three haiku series about Google Sheets. Yep. Set prompt. It'll insert a row.
13:28 And there we go. We got our output. Three haikus. Pretty cool, right? All with just a few lines of Apps Script.