How to Save OpenAI API Calls Inside of Google Sheets with Apps Script
Learn how to efficiently save OpenAI API calls within Google Sheets using Apps Script, reducing costs while maintaining functionality. This tutorial guides you through creating a custom function to retrieve AI-generated text without repeatedly calling the API.
In a previous video I showed you how to use this API prompt and response to get AI into Google Sheets. 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 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. And here we have our function . 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? This function will call the API. Pretty much like every 10 minutes or so this is sort of just a function of Google Sheets and it's gonna get a new response each and every time.
Like we got a sonnet about Google Sheets. And what we want to do sometimes in Google Sheets is just get the value, just get the text. Usually I'm going to command C copy, or right click copy, then right click paste special , and 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 going to have some input, a prompt, actually we don't even need a prompt for this one. What we're going to 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 without the function calling it. Need to get this prompt. So variable prompt equals spreadsheet app dot get active range . So we're going to select the range and get value. Okay. That's our prompt. We're going to run it through the AI. So prompt, we're going to call this variable output equals. We'll put this on another here. And now we want to write 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 going to do exactly the same thing except get column at the end. There we go.
So now we know the row and the column of the active range , and we're just going to go to the next column over and paste it. So we're going to say spreadsheet app dot get active sheet dot get range , and our range will be row. Our column will be column plus one, and we're only going to use one cell and one cell. We're going to set value as output, and this output is the AI prompt. So we're going to save this, but now how do we call this function ? If we select the cell here, let's write about spreadsheets. If 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 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 come. We're going to call this AI menu. And the first item is get AI text only. We're going to call this. 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 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. Let's see if we get any errors or if it works right away.
Of course, we need to authorize it the very first time we run it. We have to authorize it. And click allow. And sometimes we have to do it again. The authorization , just authorize it, see what happens. There it is. And there we go. We get our response right next to it. Pretty cool, right? So now we don't have AI function in our sheet calling 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 or user experiences if we want, but I think this one shows you a really good example of creating an intuitive one, right? We, 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 in just column plus one you can maybe add it to 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 we want to run this, we're going to have our output in C3. If we have that, let's do, Another one, 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, just one word writer, get, we're going to rewrite all of this, get. GetActiveSpreadsheet, getSheetByName, and we're going to call it writer, again same name, getRange , and in our case C3 is where we want to write it, and we want to set value as the output.
So we do not need the row and the column anymore here. Save this, but make sure that getActive, getAi, text in C3, we'll write that here. GetPrompt and setValue . In C3, and we'll change out our function name here, 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, right? A three sentence, right? A haiku about Google sheets. So now here's our input. And then you set value in three, C3, there you go. But also we don't have to select this. Maybe we know this input is always going to be the same. 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.
So instead of that, we want to do get sheet by name. Writer dot get range , and our range is B three. B three get value. 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. Parameters , don't match the method, get active sheet . Let's look at that. Get active. Oh, this should be active spreadsheet. That's the issue. Okay, go back and let's try this again. Dismiss it. Again, our cursor can be anywhere. Get prompt and set value in C3. Nope. Don't match the method, string, get active spreadsheet. Oh, that's why. getActiveSpreadsheet.
getSheetByName That's what we have to have. Let's try it again. And getPrompt. That should have changed there. Let's say aboutLearningAppScript Again, our cursor can be anywhere. And there we go. Awesome. So what if we wanted to save these, though? 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. Maybe all of these inputs, maybe we want to copy both of these, B3 and C3, down below so we see all of the past inputs we've done. 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, get values . We'll actually use the exact same. No, we don't need to set B3 to C3. And in this case, we'll do get values .
We're going to insert a row just in case we don't have any more rows here. So we'll insert a row before the third one, or under the third one. Insert row after three, and then we're going to paste the values . So we're going to get this, get range will be B4, B4 colon B C4, set values . And the values are going to be values to save. Okay. So now instead of just. writing over the text before, we're now going to take this text, copy it down below, and then put our new one here. Just so that we save this information. I see actually an error before we try it. I want to set the values after.
And we just want to set values , actually one just before. C4 is going to be our prompt and C4 will also be 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. So we'll say, write a haiku about AI in Google Sheets. Set value in C3. We haven't changed that theme. And we get an exception. So let's see, set values . Ah, because I think we still have set values . That should be set value. Let's save it and run it again. So that should have pasted here, write a haiku about AI in Google Sheets. Like chat GPT. Dismiss. Let's try it again. We'll insert a row and paste our answer. Awesome. So now we can change any of this and we can also maybe not even Saving OpenAI API Calls inside of Google Sheets Writer Better Prompts Build Your Own AI Writer in Google Sheets Perfect Use of AI in Google Sheets
need this output section here. But now we delete all of that and we have everything saved. So all of our past AI text is saved, right? Without formulas, without functions . And it's pretty much like a chat GPT sort of interface, except we're not using the last stuff to put into the new input. We're just creating new inputs here, right? A. Three haiku series about Google Sheets. Set prompt. Insert a row. And there we go. We got our output. Three haikus. Pretty cool, right? All with just a few lines of Apps Script .