Build Your Own AI Writer in Google Sheets

Advanced

About this Tutorial

Create your own AI writer with these google sheets tips and tricks and code.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

SUBSTITUTE()

Video Transcript

<div>00:00 So this video is going to show you how you can build your own AI writer. We're going to start very simply, and we're going to end up doing these five things. 00:07 One, we're going to write a prompt, get a response. That's the most simple aspect of AI in Google Sheets.

I'm going to show you where you can get that app script , which is actually totally free. 00:16 If you're watching this somewhere else other than BetterSheets, you can go to bettersheets.co slash snippets and find the OpenAI API prompt and response that I'll be using in this sheet. 00:23 Everything else, though, for members, if you're watching this on BetterSheets, down below, you can get this sheet and the completed sheet with all of the app script . 00:32 If are not watching this on BetterSheets, become a BetterSheets member today, and you can access the app script , you can get the sheet exactly to, you can copy it directly to your Google Drive, uh, when you're watching this on BetterSheets.co. 00:43 So first off, we're going to write a response, get a, we're going to write a prompt and get a response. 00:48 Then I'm going to show you how to write a, write a prompt, but then get the response as values only. 00:53 This is actually really a huge step, because if we just write a function inside of app script , that uses this URL fetch app, it's going to run every 10 minutes, it's going to cost us a lot of money after about a month. 01:04 If it runs for a month or more, every 10 minutes.

So we want to write as values only and then use the AI prompts when we want them. 01:13 We're also going to build essentially a UI for our AI writer, and I'll show you how to do that. So we're going to write the response to the same cell every time. 01:22 It's a little bit of a change, a little tweak that helps a lot to create an interface here. And then we're going to create a log. 01:28 This is very similar to ChatGPT. You may have seen other videos I've done like ChatGPT clone .

This is going to be similar, but we're not going to go so far as to include this. 01:36 We're not the past responses in the prompt, we're just going to log each one. This is very useful if you're creating your own AI writer. 01:43 You're going to use it a lot and you want to see the past prompts you've used, the past responses you've had. 01:48 If you want to save these things, archive them, this is what we're going to show in this section here.

Then the last thing we're going to do is we're going to write a prompt template inside of our Apps Script . 01:59 That way you can generate your own templates that you can use as custom functions . I'm going to show you how to do a really cool thing there. 02:08 And essentially, at the end of this, you're going to have all of the skills that you need to be able to build your own AI writer. 02:15 And possibly, if you're interested in it, create a sheet that you yourself can sell.

So I have an entire other course called Selling Specs. 02:24 You can check out sellingspreadsheets.com or bettersheets.co slash courses slash selling-spreadsheets. And it's also available on Udemy. It goes into all the things you need to do to sell a sheet. 02:36 But what it doesn't do is show you how to build a sheet like this video does. So, if you're really into AI, you love using ChatGPT, but you want to like write these prompts for others to use, this is the video for you. 02:47 Uh, and we're going to build our own AI writer.

So first off, again, uh bettersheets.co slash snippets get this open AI API prompt. 02:55 You can copy to clipboard. It's already in my Apps Script . You just go to extensions, Apps Script , and it will open up the App, Apps Script here. 03:04 Uh, you can copy and paste that function here. I have done nothing except, I think I changed the max tokens to a thousand for this one. 03:12 Double check, yeah. So, I just copy pasted it, changed the max tokens.

We're going to use a thousand for this one. 03:18 If you have any other changes you want to do, there's the changes temperature, top P, frequency penalty, all this kind of stuff that you may learn about AI. 03:26 You can write here, but we're just going to use max tokens. It's one thousand right now. We're using a temperature of one. 03:31 We may want to change that later on, but you'll see why.

You might want to lower this down to zero if you want to do something very specific. 03:38 But if you want to do some creative writing, which we'll do in this video, we're going to use one for our temperature. 03:43 Okay, writer's response. Write a prompt, get a response. So let's create a sheet called prompt plus response. And all we're going to do is use this function AI. 03:56 We're going to write a prompt here in A1.

Write a great idea, sorry, YouTube video idea about writing AI inside of Google Sheets. 04:15 And so if we, We want to get, use this like say on ChatGPT or AI, all we need to do is call our function equals AI and use this A1 as the reference . 04:26 It's going to give us our response. Let's open this a little bit more so we see what we get.

Okay, we're using MaxTokens 1000, so it's going to keep it pretty short. 04:37 Uh, we're also going to use Temperature 1, so it's going to keep pretty, not very random, but it'll do something. 04:44 I almost forgot to mention that we are putting the API key in A1 of our sheet called API key right here, so this won't give us an error . 04:53 If we didn't have this API key, let's hit uh there, uh and let's actually use MaxTokens 100 for this. This will be probably quicker as well. 05:03 We're using cheap PT 3.5 turbo, so it should be pretty quick.

Uh we should get a response pretty quick here. 05:11 And so we get the response error that we don't have an API key, so of course when you go back to API key, put in our API key, and now let's try it. 05:20 Try writing this again. Equals AI, A1, we'll see if we get an error . Get truncated error , you didn't provide an API key. 05:31 We will probably have to see this again, API key get value. We are here. And there we go. We have our answer. 05:44 So again, we're just recalling the AI and getting a response with this function AI.

Hey, so how do we actually? 05:52 Get this to just produce values so we can, of course, get the response. Do command C shift command V. And we're going to get the paste values . 06:03 But how do we do that straight from here? Well, we need to add another function , so we're going to do function . 06:08 Write values only. And we need to be able to run this without having to run this in a function inside of our cell. 06:20 We're going to have to add a menu on open.

So we can go actually to registry.co slash snippets and we have a custom function with two, two features or functions here. 06:31 We'll take that, we'll paste that up here and we'll call a function write values only. Go write values .

We'll have a custom menu called custom menu and what are we going to put in here? 06:46 Well first off we need to actually return, actually no not return but write into a cell, spreadsheet app dot get active. 07:00 Spreadsheet dot get range , that range we'll figure out later, set value and the value will be the, we'll call it output and the variable output equals a i and we'll put in a prompt here. 07:17 Where are we going to get our prompt and where are we going to write out to our set values?

What we could do is have a, you know, intuitive uhm user interface. 07:28 Where we just have this cell here, TikTok. Let's change that to TikTok and let's change this to LinkedIn. So we have some different ideas here. 07:40 We can say A2, if we have selected A2, we'll write in B2, uh. The answer to this. So let's get our uhm active sheet . 07:51 So that'll be our active sheet , active range . So our prompt, variable prompt, equals SpreadsheetApp .getActiveRange. getValue . So we're just getting the value in the active range . 08:10 And we also want to get the uh row number .

So we just put it in the column next to it. 08:24 So So we know what row we're on. And the output is in same row but in column. One over.

So we can also do this column two if we knew, okay, we're only going to use column one or we can grab that column uh programmatically, same way we do the row here. 08:43 Okay, so in this function , whenever we click this uh right values in this function on open function menu, we're going to get the value of the prompt wherever we are. 08:53 We are going to get the row, then we're going to go to AI, grab that prompt response, and then uh here we say return that prompt response down here. 09:04 And then we're going to go to the same row we're on, go to the second column and set the value. 09:09 So let's see, first thing we need to do is refresh our page so that it opens up again and we have our custom menu right here. 09:17 We have custom menu .

We select . Select our A2 and let's see what happens. We may have to authorize sometimes the very first time we run something when we add new functions is we have to authorize it. 09:32 So let's run that again once we have it authorized and we should have. Right here, the response. So we have some errors. 09:41 Ah, let's go grab, let's go see what those errors are. It is in spreadsheet dot get range . Dismiss it. Let's go back to extensions, Apps Script and troubleshoot or debug our code. 09:57 We can also look at our extensions executions here.

See why it failed, gives us sometimes more information on code line 15. 10:12 15 is set value. I'll put git range . Ah, we probably need git active sheet . There we go. Not spreadsheet . The spreadsheet is the entire file and it doesn't know which, which tab we're in unless we do active sheet . 10:30 So active spreadsheet is the entire file. Active sheet is the exact tab we are on. So let's run that again. 10:36 Again we are on A2 and ideally if this works the first time we'll have our values in B2. Not first time. 10:43 Second time we're running this now.

Run our script and there we go. We have some response. May not be perfect as you want but you can always redo it. 10:52 Add some examples. Be like hey we want only the title. Write only the title. And let's run that again and it'll write over B2 because we're in the same line. 11:08 There we go. We have the power, the power, unleashing the power of AI. Change that row we're on just by changing which one we have selected. 11:16 And we're now writing to our values instead of the function .

Again, if we have to make any changes to this inside of Google Sheets or, That's right. 11:30 Or Excel . If we want. Write those values again, see if we get something else. Okay, we got something else. So so far we've created We did the prompt and the got in response. 11:46 We write the prompt and we get our responses values . Only we do it in an intuitive way where we have the same row. 11:52 But let's say we want to write to the same cell every time.

We want to create a UI where we have some writing, some maybe examples we write and we take all that. 12:00 That information then we get it in a response the same way. Just like online web apps work as well. So now we're going to write a response at the same cell every time. 12:08 Let's re- duplicate this and write to same cell every time.

So we want to make sure we're on this page so let's create a second value Write to same cell. 12:26 We're going go function Write to the same cell and the output, the sss regimental The last thing we want to do is almost the same thing here but spreadsheet app.get active spreadsheet .get sheet by name. 12:41 We'll put that sheet right to same cell every time. Get range . We always want to put into let's say d2 we'll say set value will be our output. 12:55 And our output will be variable output equals ai prompt. Okay?

We're still running this ai- I prompt but we're same here giving you an output. 13:07 But what do we want to put in here? Well, we want to end up in d2 here. So let's delete all of this and start over again and say ai writing. 13:22 I'm going to minus these a little bit. We want uhh I want to create sort of some multiple inputs. We'll put this in some border. 13:36 We will view free uhh view show no good lines. There we go. We'll have our AI writing in here. And what do we want to insert in? 13:47 Well we might have just a prompt. Right? One idea of a tick. Tock video.

That shows how to write AI prompts. 14:00 One title idea. There we go. So we want A2, always get it into D2. So let's get- give it that uh A2. 14:10 So we say our prompt. So variable prompt. This is going to be very simple and we'll add onto it. We'll be in spreadsheetapp .get.get.get sheet by name. 14:20 Thank for your and it will be the same right to same cell every time. Same sheet name . Get range . Will be A2 I believe. 14:36 What is it? Yeah. And get value. Okay.

Now when we write to same cell every time, we will get the same cell every time. 14:47 Let's refresh our page so that we have the new custom menu that has that option. Oh, I think. I think we haven't renamed it. 14:57 I think we have it right into the same place. But we didn't rename it. Yep. Right in D2. We'll call it right in D2 so we know that it's different than right value. 15:12 Again, refresh. Now we have a custom menu right in D2. So we'll take A2, run that as a prompt in D2. 15:20 And there we go. But let's say we want to do some more stuff here, right? Of a, type.

So this is our umm template . 15:37 And this is our input. Actually we will do, Thanks for Inputs. Let's merge these. Yep, it's fine. And, And we'll say type is dictac. 16:06 Okay. Here we'll use substitute. And we're gonna substitute this search for, we're gonna create our own little uhh substitution here. 16:18 We'll look for type and we'll substitute it with whatever is in B4. So let's center these up. And so now we have a template , right? 16:33 One title idea of here. So let's also add title. Or umm style. And title. Instead of title idea. Alright.

Style. 16:49 And all we have to do is wrap this substitute with another substitute. And we'll wrap this substitute. The output of that substitute will be this one. 16:57 And we'll write. Style. And style. That style will write b5. Okay, so we have a template here. We can also write, we get our prompt here. 17:14 So we're writing all these inputs, we're writing a template , getting some styles, adding these. Extra things, and then getting this prompt here. 17:22 And we're always writing it into d2. So instead of title, let's say weird. Custom menu , write in d2.

So every time we write it, we're taking a2, running through the prompt. 17:36 Getting d2 out of it. Running that script. There we go. Talking to myself. And I'm prompt writing tutorial. Cool. And every time we run this prompt, we may want to save them, right? 17:49 We want to save the prompt. We want to save the output, and we're going to do that in a log. 17:55 Okay, so we create a log, we say prompt, and we say uh output, say. And that's all we need. Just two things. 18:04 We may actually want some kind of time stamp or something. Let's add that as well.

So every time we do it, we'll delete all these rows just in case. 18:17 So every time we run right in D2, we also want to log it. Let's actually create a new, um function that will log when we log it. 18:29 So we don't have to redo this. So let's take right to same cell, call it right and log. We'll add another item up here. plus log. 18:54 We'll call it right and log. There we go. And so in- in addition to writing this output here, we also want to do one more thing, which is on the log sheet, insert a row, a2, and then write our response there. 19:10 Right? Get active spreadsheet .

cheat. Get sheet by name log dot insert row before 2. Okay, in order to make this a little easier to read, I'm going to create a variable here. 19:28 Thank you called variable log equals this. And every time we log, we'll go here log. Now log dot get range . 19:38 We'll always put it on the second row. To row. In the first column we'll Put the prompt. Set value. We'll be the prompt. 19:53 We're using the same exact inputs uh just putting them in the log. Now we can take this.

Copy it two more times and instead of column one two we will write the output. 20:07 Instead of column one we'll put three and we'll log a time stamp. Time stamp will add another variable. time. Time stamp equals utilities dot format date and then we're going to just copy this from inside here. 20:26 This is a good GMT we'll do minus five for New York time. And so now, again, because we've added a new custom menu we'll close this, refresh, refresh our sheet. 20:42 We now have a custom menu with d2 plus log.

So anything we put here we'll write A awesome custom menu d2 plus log. 20:53 And we'll have two things happen. It will write to d2, but it will also log it on log. And we'll see here. 20:59 Let's see. Why didn't that happen? Let's look at our app scripting And make sure we edited the correct one. Write and log. 21:11 Oh, I see what we did. We wrote it in write only. Alright, take these out. Put it in write and log. 21:28 Let's save that and we'll try again. Write and log. log. Wait for it. Wait for it. Finish script. Do we have a log? 21:43 Yes we do.

We have the prompt, the output and time stamp here. We can always make this a little bit bigger. 21:48 So far we've written a simple prompt. And response using AI in our Apps Script . We've gotten it as values only. 21:56 We write to the same cell every time so that we can create a nice UI. Uh something much better than this. 22:02 But uh a simple UI that we can add some interesting stuff to. And now what if we want it to create our own AI writer that others are going to use. 22:12 But they don't necessarily want to see all of this stuff.

What if they want to create, you want to create a function that is a brand new function for them to use. 22:21 Not in a custom menu , but is just like. Like AI, this one AI down here. But it includes all of the stuff they want to do. 22:30 Okay, so we need to create function . And in this case our name is going to be very simple but useful for our users. 22:40 So we can't write out just sort of random things here. We're going to say write underscore AI, uh write title idea. 22:54 We're going to give it to things.

We're going to give it the style and we also, what did we want to do? 23:02 Style and platform. Okay. So given that, how do we write this? So we might be able to take something like this, write one awesome idea, and do variable prompt equals, put it this in quotes, and instead of awesome, we'll put quotes here, plus plus, but inside the plus we'll put the style. 23:28 Bye. And instead of tick-tock here, we'll put in quotes here, plus plus, and put platform.

So now we have a custom prompt, we can write as much as we want, maybe we have examples, maybe we have a few shot prompt, maybe lots of stuff we'll write here. 23:43 and and then we'll get the response. So we'll return ai prompt. We can also call this output, make this a little easier to read output. 24:01 Or response variable response equals ai prompt. So again, we're using the same thing as we did before, right? The ai prompt, we're using the same exact, get a- insert a prompt, get a response. 24:13 But we're writing the prompt inside of our app script .

and we're using right title idea so that our users can know what they are doing. 24:22 But let's go look and see if this works. Right title idea? We'll go here, we'll say equals. Right title idea? 24:32 We'll put in a style. Weird. And we'll put in a platform tiktok. And even though we had this red line unknown function , we hit enter, it says it's loading. 24:44 It says loading. And now it gives us our response.

That's pretty cool, but our third party, they're not going to really like that they have to write out the word and not know if it works or not. 24:55 I'm going to show you one little thing that I absolutely love is writing a little comment above it and write at custom function . 25:02 That's all we need to do. hit come.

command S save it now it can do equals right and this shows up right title idea and it says what are the inputs style and platform. 25:17 So now our function that we've written In our AR writer our prompt that we know we only need to add two things style and prompt in here shows up for the user as a completely custom function native to Google Sheets they don't know anything else they don't know what that prompt is they don't know what's 25:34 in it they just know that they can use it this is really.

cool for your internal tools is really cool for writing and using AI inside of a company but using letting others use it and get the responses very simply. 25:50 So in this video I showed you five really cool things you can do I think that the custom menu or automation menu or any menu you add here is probably one of the one of the best uh use cases of AI because it's not gonna run you into problems of having that API call call all the time. 26:06 Also I think this last one this custom function is absolutely really really fun and cool you can use it to create app scripts inside of your sheets that you can then give to other people and they can use without having to access the app script at all and no they just have to know a few. 26:24 put and use a prompt that you have.

so hopefully this has allowed you some really cool ideas to build your own A writer in Google sheets again check out selling spreadsheets that's the course that I have that allows you to build a spreadsheet like that. 26:40 like this build your own A writer and then sell it to a marketplace willing to buy it pay willing to buy it. 26:46 uh even if you're a teacher there's teachers paid teachers platform there's absolute marketplace if you're trying to sell to entrepreneurs who are too busy to write their own prompts or create or- if you have a really interesting idea on how to create your own UI in here umm really fun stuff hope you 27:02 are successful.</div>