Hey there stranger!

Sign up to get access.

Why Different Cell References in AI Integration in Sheets?

About this Tutorial

Find out How to Optimize your Apps Script functions. Learn the trade offs between flexibility, edit-ability, and protection. Different cell references allow you to do more with your apps script.

In the context of the video we're talking about an AI prompt and how I used different Cell References when writing different Apps Script functions. I'll show you why I chose those syntaxes and why you might choose different ones.

This video is more about Apps Script than writing Prompts but can help you create better web apps inside of Google Sheet, utilizing the best of Apps Script and creating your own custom functions.

Video Transcript

00:00 Hello, oh, so, Terence, BetterSheets member Terence, had a question about cell references. When I have been doing AI integration inside of Sheets and sort of writing a GPT function or writing sort of just open AI function we have this function here, we have function, it's literally- called
00:20 AI, and then we have the prompt, some text here. And he was asking, you know, he's been following three or four or five different videos where I talk about AI, and he probably hasn't seen yet this video where I create sort of a, a chat- it sort of looks like a chat interface, but what it really
00:39 is is to look at all- of your prompts in a row and see which ones are good or bad. And this one might actually surprise him because it doesn't have a function inside of B2 here.
00:51 Where we write this prompt, we, we execute a function that's completely separate. So this user fun- this user experience might be very interesting.
01:01 But he was looking at, Terrence was looking at past videos where I wrote a custom function called openAI and then had some prompt in here, but had a variety of different cell references across many different videos.
01:18 Sort of each video I made a different way. One time I did Q and then I wrote ampersand A1 and then ampersand and then had space A and then had this.
01:39 This sort of fun, this sort of. Syntax here. Let me sort of try to make this a little bit bigger.
01:46 And with this syntax, I think the reason I did this is because that is literally how the AI prompt was written.
01:53 I was actually using an example from open AI's own documentation and they said, you know, make the prompt in this way.
02:00 Put Q. Put your question and then put literally the text a colon and that's the end of your prompt and then it will complete because what this AI is doing and what this LLM is doing is text completion.
02:15 It is taking this question and answer going from a colon and then just completing. That it is not necessarily answering the question.
02:25 It is saying what in this vast database of knowledge would be the next token after a after this answer or what would be this answer.
02:38 And so that was what I originally did and then. I've been sort of doing different cell references like for instance, right now for this particular app script, which is just AI prompt.
02:54 I know in my head and how I wrote this app script, I know that all I need is the prompt.
03:00 I don't need to add any more. I need to add any more extra curricular information with the GPT function that I wrote.
03:07 I put all of the tokens, the temperature, the top P, the frequency penalty, the presence penalty. I put that all in here.
03:14 So I needed to reference all of those cells to get all that information into the function name. But for this particular app script, I needed to add any more function, AI, that I wrote.
03:26 I literally just need the prompt. I don't need to put in all of this. I could write this app script where all of this message is some kind of variable here.
03:42 And then I write message here, and in this case, I would need to recreate all of what I just deleted.
03:49 I would need to write in all of here, in here probably in quotes. Whoops. That's the APA. There it is.
04:04 I would need to put into the cell, into the cell here. But right now, because I wrote this function as just ai prompt, and then put this prompt variable in here on line seven, I only need to put in some question, or write, I can put in.
04:22 Here, write, write a summary of the ten commandments in three words. I don't think this is gonna work, but we'll see.
04:36 There we go, done. Right? I just have some text, put in quotes, and that's it. But now I'm like, I have to, I need to write this equal sign, ai, I need to write anything I want to write, I have to go into these quotes, between these double quotes, and I have to write it.
04:52 What I really want to do is use all of the power of Google Sheets. I want to utilize anything and everything.
05:00 I want to utilize multiple variations of, this. So I want to start doing cell references. I want to say, okay, I'm going to reference, actually, I'm going to put in a1 the prompt.
05:13 What it summarize the 10 command, let's do that, in five words. That's the prompt I want to do. So I'm going to do equals, in b2, I'm going to go over here, ai, and I'm just going to do cell reference a2.
05:30 That is all I have to do. I don't have to put in any extracurricular information. I want to make this as simple as possible, and I have done that by way of the function.
05:41 But sometimes, maybe there's other things, so we'll, talk about that in a second. But let's just get this done, right?
05:45 We have a cell reference to a2. And here's the answer, right? There's five words. What if, for instance, this is a good example, a2, just putting a2 in.
05:57 If I'm like, I'm going to write the whole thing here, and if I copy and paste this down, I'm going to have a blink.
06:02 This is going to be a3. I have nothing here. But I want to maybe copy and paste this and put in ten words.
06:09 Maybe I want to do in twenty words, and I'm going to, Or maybe I want to even do in three bullet points.
06:23 See if that does, That's it, right? So I can also, Yeah, let's do that. Let's make this a little easier to read.
06:33 See everything. Right? So now I'm referencing this A2 here. I'm referencing A3, but what if, What if I was like building some kind of, interface, and I was like, this is gonna be some other user, or me in the future, and I'm like, I know in this particular sheet, with this particular Apps Script, I'm
06:56 gonna write summarize the 10 commandments, and I'm always gonna have some variable here. Okay, so in this case, I can cut that, summarize the commandments, the thing that's the same thing, all the way through, I can just put that, right here, in the B2, right, in the function, put the ampersand 
07:18 I know I'm gonna use the word in, and now all I have to do, I can copy all of this down, is write 10 words, actually I need a space, right after the in I don't need the period, I don't need that, I need 20 words, 3 bullet points, in, maybe I wanna do in 10 emojis, something like that, right
07:49 , so now I know I am building this sheet, that is the 10 commandments summary sheet, I know the prompt is summarize the 10 commandments summary, and that x is a2 or a3, but like.
08:05 You know, this is a different use of this prompt, okay, I am getting maximum execution time, oh, it is probably because I just did it all at the same time, this is actually wrong, three bullet points ends up being 10 bullet points right, so now we have this.
08:25 We are using this in a different way, right, we are using Google Sheets to do the prompt, we have some text in the function here, we have some text and syntax in the app script, right, we have this role system, we can also write our role.
08:45 Here we can say something like, you are an expert in the ten commandments, you can, you write like Charles Bukowski, I don't know no, probably, maybe Shakespeare, you write like Shakespeare, and I am, bic- pentameter.
09:09 You tell it like it is. I don't know, this is all crazy talk, but in this role, maybe this role, we want to, this is the system role we want to edit that on the fly, we want a different system role all the time, so we'll add that.
09:25 To our function, so in this case we're going to see, is there any actually difference? Okay, so there actually is a sort of a little, those emojis are hilarious, right?
09:40 Maybe we want to do a different system role, right? So instead of putting. This here, I'll just comment it out.
09:47 We're going to do prompt comma role. And now in our function, we need another column here that says system. You are Salvador.
10:05 Salvador Dali. You are a member of the Starfleet Academy in Star Wars. Right? And in our here we need actually two things.
10:28 We need B2, comma, and then the actual plot. We're the Bible, right? Put that there. Copy all these down. And now, right, we have different information all over.
10:54 We have messages, we have role prompt, we have that prompt and role here in our function. We write it in here in our function here.
11:01 We have a little bit of text here, and then we put our system function here. Prompt here. Actually, it's not our prompt, it's our sort of input.
11:12 Okay, we are getting some weird answers here. I am not solving our dolly. I have a language model. Oh, that's creepy.
11:22 Great. So, you know, we can have all this. We can have all this information in many different places, and it's our choice to put it in different places based on what is the user want.
11:33 Are we building this for ourselves? Do we just want a very simple prompt writer? We might. We might do that by just not having the role here in our function name.
11:46 And we might just put the variable role here as nothing, and we're just going to get the prompt. And we don't need all of this column we just need here.
12:03 And maybe we do want to write the entire, we want to. See the entire prompt. If we're writing this, if we're creating this app inside of a Google Sheet for other people.
12:15 Say it's our own organization. We don't necessarily need to write the prompt in Apps Script to sort of hide it.
12:25 We might want to write it here because we want. Some flexibility. We want to edit it at some points, but we don't necessarily need the user to see exactly that prompt.
12:34 They can definitely have access to it. And putting it in text in sort of A2 all together. That means every single time we sit here and write, we have to write it out.
12:46 We have to. Write out all of that stuff, but maybe we have half the prompt. Or we have some examples and we're like, you know, we just want those examples somewhere.
12:55 We can even put them on a completely different sheet. We can say examples, right? And point to this example in A1 and combine it here.
13:06 In our prompt. We can say and examples include. Ampersand. Examples. A1. Right, and if there were some examples there. It would include that in the prompt in the function.
13:25 We have a reference to it. Right, so if somebody were coming in here and say I want to edit these examples.
13:31 Where would I edit it? They would look at this. B2. They say oh, here's examples. A1. They can follow the chain.
13:38 Okay, so it's a little bit harder to edit. In theory. But it actually ends up being easier to edit because literally I'm like I just want to edit the examples.
13:47 Okay, go here, do examples, and write input. Three words. Put response. Do not murder. Kill. Right. I don't know if that was, that's an actual one, but.
14:06 There we go. So that's interesting. So we can use all of the power of Google Sheets in a number of different ways for a number of different reasons, right?
14:14 I would say, sort of all of these things, balance, protection versus editability versus flexibility.. And you can be as flexible, as editable as you want.
14:28 Or, not at all. You could take all of this and everything not the API, in cell reference. You could not have a prompt here.
14:37 And you could say, and right here, variable prompt equals, And you could go ahead and write your entire prompt. Everything you want to put in.
14:48 Examples you want to put in styles, you want to put in tone. All of that right here. You can put all of your role in here.
14:53 The entire system you can put it all in Apps Script. And then the user, all they can do is write AI and they'll get, they'll get something.
15:04 Maybe your temperature is one. Maybe you're like, okay, every time that you put in AI. I put in this prompt.
15:10 We want a different result. We want, you know, different things. Maybe we increase the max tokens, but we don't want this necessarily editable.
15:19 It is editable. I can edit it. I'm editing it right now. But someone who was familiar with Google Sheets. Might not know exactly, they might know that Apps Script exists, but they might not know that your AI is editable in Apps Script and where to do that.
15:40 They can't tinker around with it. Tamper with it. If you want to make it easily findable in. Typeable, because maybe you have this function, it's not AI, maybe it is write the 10 commandments in a funny way.
16:02 This is a horrible way to do it, but maybe that is the function. What I think it is. Is, I'm gonna do this on the fly, I'm gonna do a comment, at custom function.
16:14 And now, if I start typing in here, write, there it is, write the 10 commandments in a funny ways. And maybe you have two functions, right?
16:26 Maybe you have another function called. Write it in a silly way, write it in a serious way. And you create these functions that someone, all they have to do is write this, write the 10 commandments in a funny way.
16:37 And bam, they get a response. They don't know it's AI, they don't care, they don't care all of your prompt, they don't care everything, but they do have access to it, right?
16:44 Anyone who has access to it is trading off a lot of protection, but not 100% protection, for editability, for the person to write it, for flexibility as well.
16:57 And if you watch, the complete opposite of this particular function that I have written right here is the GPT function in another video which I write the GPT function.
17:05 And literally write all of the possibilities of roll the- the prompt, the model, the temperature, all of that is in the function variables.
17:14 And you have to put those in if you want to use this function inside of Google Shades. In a funny way.
17:29 Here are three examples. And we're gonna write the three examples. And maybe we write in the tone of George Carlin.
17:39 I mean, that's particularly funny. But write in, in the style of some. Other Richard Pryor, right?. Write three sentences. Make them full sentences, right?
18:01 No, not bullet points, not anything else, not emojis, right?. We can write that entire prompt here and we don't have to put any in.
18:09 Puts in there. And almost the complete opposite is the GPT function that I wrote. That includes all of the functions parts of the functions, all in the particular order.
18:20 But also, you can put prompt here, right? You can say, oh, I don't want it in here. I want to be able to edit it.
18:29 So I'm gonna, write here prompt, and I'm not gonna write the 10 commandments. I'm gonna write write. Maybe I just want to say write.
18:37 Now, You go equal, right? Oh, you need to, get saved. Yeah. Equals, right? And that's all we have. It's right.
18:49 Our prompt. Prompt goes here. Right? So, we can put this information in many different ways. We can use app app script.
19:00 We can use Google Sheets. Also check out if you haven't yet. Spin text, content spinner. This might help you a lot.
19:09 So instead of doing, Like this kind of thing, you would do equals spin all. Some text that is like 10, 20, 30 words.
19:33 And so just by doing that, just typing in this spin syntax, this curly brackets, 10, 20, 30 words it automatically creates those there for you.
19:44 So you can spin some interesting content here. I'll save that. You should watch that video as well. About the spin tax.
19:52 And it's a free Google sheet add-on you can add to your Google sheet. Anyway, alright thanks for watching and I hope this answers your question to some extent of why do we put that why do we use self references?
20:03 Where do we put that self reference? We are trading flexibility or trading editability and protection against. Somebody else editing, tampering it or ourselves.
20:14 Oh, we might want to edit edit. We might want to prevent our future selves from editing something or just making it a step or two harder.
20:24 It is not, you know, completely fixing protecting this text from someone. So. Hopefully that answers your question. Bye.


Sheet Stories / Video Notes + ADDED: Email Notifications



ChatGPT Clone in Google Sheets Part 2

Create an Internal Google Sheets Add-on

Why Different Cell References in AI Integration in Sheets?

Show Sheet Tabs Based on Edit

Add Title Case to Google Sheets

Getting Started Coding in Apps Script

How to Power Testimonials with Google Forms and Sheets

Seek Errors When Coding Apps Script

Think Like a Programmer: Develop The Mindset of an Apps Script Coder

ChatGPT Clone in Google Sheets Part 1

Embed a Number in a Website from a Google Sheet

Create Navigation Like A Book or Presentation

Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet


Hold a Giveaway Raffle in a Google Sheet

Capture Emails from Website Form to a Google Sheet (Without Zapier)

Embed a Headline in a Website from Google Sheets

Quickstart Tutorial OpenAI API in Google Sheets

Create a new Spreadsheet from just a Name in a Sheet.


Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?


Email Yourself a Cell from a Google Sheet, Every Day

OpenSea Data Inside Sheets

Create an Email Campaign Stats Calculator


Twitter App Clone in a Google Sheet

Dylan Asks: How to Automatically Delete Rows If Cell Contains Value

Highlight Row as You Move Your Cell Selection

Create a Timer with Apps Script

LinkTree in a Google Sheet


Password Protecting Data In a Google Sheet

Automatic Weekly Backup of Google Sheets

Create a CPM Custom Function (Create Better Calculators!)

Move Entire Row when a Cell is changed to "Yes" - The $75,000 Google Script


What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users

Sync Two Tabs Without ImportRange()

Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets.


Password Protecting Data In a Google Sheet Part 2 The Basics

Benoit Asks: How to Convert Case


Learn to Code in Google Sheets, For Programmers

Add a Checkbox to Turn on Dark Mode


Write Your First Script


Find Keywords in Any Column. Create quick search dropdown to find keywords


Basic CRM - Add a Powerful Script To Move Row Based on Status

How To Improve: 1,000 Business ideas: Business Idea Generator


Let's Make a Bookmarklet!


Troubleshooting Bitly in a Google Sheet Script


Unique Features - Design a Better Dashboard Part 2


How To Set Up Stripe Webhook to Google Sheets with Google Script


How to Edit a Macro


Sheet Stories / Video Notes + Clear 24 Hour Old Videos


Add A Timestamp to Task Lists (without Now Formula)


Make your Custom Functions Like Native Functions | Custom Function Autocomplete


Create a Changelog Between 2 Cells Custom Function | To learn Double For Loop


New Syntax for WhatChanged Formula in Google Script


How to Record Macros


2 Ways to Delete Lines Quickly (CAREFUL, it's a script!)


Deep Inside Dark Habits Google Script


How to Trigger Macros Daily


5 Ways to Create Coupon Codes | Create UUIDs

Create a Radio Button From Checkboxes Using Google Apps Script