Gemini in Apps Script

0:00 gonna go through Gemini in Apps Script today. So just was announced that you can use Gemini in the side panel in Apps Script . This is really cool, and there's a ton of issues with it. One of the biggest issues is not it itself, not Gemini itself, but rather you as a user or me as a user. And so I'm gonna show you a few things that it can do, which doesn't seem like a lot. It says here that you can generate, modify, and debug code, which is something that Apps Script has been able to do for a long time on its own. Like, you can just write Apps Script or you can modify Apps Script. And there is a whole debug, version of Apps Script that you can use. But, oh my God, I've been using Apps Script for 10 years, and I, I think I've used it twice. Two Things to Know When Starting to Learn Google Sheets Everything Aggravating about Apps Script Breaking Through Errors In Apps Script What is a Spreadsheet? Anatomy of a Spreadsheet

0:45 It is pretty clunky, and also it's really good for developers. People who are, like, actively a developer, they know JavaScript, Apps Script is really good. But for people who are not professional developers, who are like, "Well, I need my Google sheet to do something really cool, like an automation," th- this Apps Script is not set up for them. It's for a professional developer. So I'm gonna show you some really cool stuff you can do with this, and you can just access it in Extensions Apps Script and click the icon in the row. So you can just go to Extensions, Apps Script . And up here, if you- it's closed already, there's a, a, a Gemini icon in the top right called Ask Gemini , and it opens up. And so what this is really doing is collapsing having another tab open, which Two Things to Know When Starting to Learn Google Sheets What's Better Than Google Sheets? Advanced Coding in Google Sheets for Programmers What is a Spreadsheet? Anatomy of a Spreadsheet

1:32 is usually what I've done in the past, have another tab of Gemini or ChatGPT, and then I'll have this code here. And what the funny thing is, is this looks like Cursor. It looks like an ID or a VS code. Like, we have a little bit of chat window. We have our code. We have our ability to look at, triggers, at executions over at project settings on the left. Like, this is pretty much VS code, and so it acts like that. I'm gonna test some things. Like, I wanna write some script right from the get-go. So we're just gonna be testing Gemini . I'm just gonna copy and paste them into here, just like I would with like either ChatGPT or Gemini , and let's see if it performs. I haven't actually run this yet. This is actually really interesting to see in real time. Is this going to work with a very short prompt? ChatGPT Clone in Google Sheets Part 1 Create a ChatGPT Cheat Sheet Spreadsheet ChatGPT Clone in Google Sheets Part 2 CLUTTERED CODE

2:19 And I'm gonna explain while this is doing it what limitations this has. And the limitation is not necessarily itself, but us as users. We really have to be in the driver's seat. We really have to know where we're going, what direction we're heading, and that we want to do this. Would you like me to… I have updated the file code.gs. Would you like me to add a filter so the script only fetches specific file types? What I wanted is all the drive files get all Drive files. Is it doing it? And now I have to, check this code, right? It's telling me, that it might exceed the Apps Script limit if I wanna read all of this, right? So let's see. Let's accept it all. And now you have to know how to run this, right? Tips to Navigating Thousands of Lines of Code In Apps Script Getting Started Coding in Apps Script How to Get Every Editor on Every Google File I Own

3:06 And it's funny, this is written the code, but it hasn't saved it. So I actually have to go up here and click Save Project to Drive. Again, I have to be in the driver's seat, and I really have to know what I'm doing in order to get the most out of this. And I'm gonna click Run, and already… Of course, I have to authorize. I executed the wrong code, which is a very easy thing to do if you're not familiar with it. It's not executing the function we've just written, so I'm just gonna delete this function here. I'm gonna save it again. Let's run this one, get all Drive files it's… Oh, it's gonna insert a sheet called Drive Files. Great. Let's go over there and see it work. Is it doing it. It probably takes a while just to get all the files, 'cause it has 5 Ways To Use Google Sheets for Advanced Project Management Getting Started Coding in Apps Script How to Save Google Sheets

3:51 to go through each and every file. That is, one of those weird things about, App Script and the Drive API, is it's going to go through each and every file, which is actually what I want. I want to get all the Drive files with the URL into the sheet. And now what I would have done if I knew App Script , which I do, is I wouldn't necessarily just write it all in a single batch operation. I think, this, what I'm about to say is unoptimized. This is probably optimized for code, and the code way to do it is probably just, yeah, get all the files, put them into this array, and then, write it all at once. But as a user of Google Sheets, I would be like, let… Oh, there, there, there is everything. So, so that, it worked, but again, I'm not a professional developer here, even Get Every Editor on Every Google File I Own Getting Started Coding in Apps Script Get Every Editor on Every Google File I Own List Every File In Your Google Drive

4:42 though I can help you with your code. But I would not write it like this. I would write it so that it would go each one individually and just write it so that I could see it executing. I wouldn't have to wait minutes. All right. But that's a taste, right? Let's do this. Get all spreadsheet names in my Google Drive into a sheet. Add a link. So I want all the spreadsheet names. So I'm gonna go and make a new conversation, paste this in, hit enter. So it's just a very short description of what I want, and I want it to execute this. I want to create some code to do this. And again, I wanna point out one more problem. Like, I earlier said that you need to be in the driver's seat. You really need to execute all of these things. But I need to know that I want to do this, right, in code. It's writing a lot of text very, very quickly. From Sheet to Script And Back Again - Learn to Code In Google Sheets Part 3 Learn to Code in Google Sheets, For Programmers | For Advanced Google Sheet Users Advanced Coding in Google Sheets for Programmers VIBE CODE GOOGLE SHEETS

5:29 So I don't think it's very important, but, like… would you like me to add custom menu ? Now, this is something that I really like to do, is I like to add a custom menu up here that I can execute this code from the sheet itself instead of coming through here. I'm gonna accept all. And, and you know what? That is a weird situation here, is that it seemed like this question was that Accept All button, but it wasn't. All right, I'm gonna save this, and now I'm gonna switch it to… Where is my code? Oh, it updated the code. New conversation. Just start from scratch And I didn't r- say a sheet, I didn't say which Advanced Coding in Google Sheets for Programmers Getting Started Coding in Apps Script Advanced Coding in Google Sheets for Programmers How To Insert Row Under Header Automatically

6:15 sheet to go to, I just said, "Get all spreadsheet names in my Google Drive into a sheet, add a link, column two directly to that sheet." And specifically, I want spreadsheets, not every Drive file. That's the key here. And again, I'm gonna harp on this, but it is not super smart. It is trying to think through stuff. It's trying to give me suggestions, trying to decide things. But it really is leveraged by our knowledge of is it possible, is it doing something? All right, let's just accept it all without knowing… Not without reading all of this, and let's just run it. Get all spreadsheets, run. See what happens. It's gonna go into a spreadsheet called All Spreadsheets. Get Every Editor on Every Google File I Own When did you learn the secrets of Google Sheets? | Sheet Talking Episode 8 Esa Get Every Editor on Every Google File I Own Track Anything! In a Google Sheet

7:03 It's running something. Again, this is like one of those issues with Apps Script is I want it to sort of do each thing in order as it's going. But it is really gonna just take a while to get through everything. Search through it. There it is. Oh, that didn't take that long, but it has every single one of my spr- oh my God, so many spreadsheets I've made. Wow. Oh my gosh. This is automate. This is something I did for Apps Script , basically trying to do what Gemini is doing, but a little bit differently for users who might not be very knowledgeable about spreadsheets. All right. I'm gonna try one more prompt. Every Friday, email me a well-designed, awesome, inspiring quote, an HTML email of a random row of inspiration on the tab Quotes. Spreadsheet Automation for Beginners Can ChatGPT Automate Spreadsheets? Spreadsheet Automation for Beginners Bigger Brain

7:55 Actually, from the tab Quotes. So what it is is I have a list of quotes here. I've already done this. I don't have to go web scrape or anything. But in column A, I have all of these. I'm gonna go here, delete everything, start anew. Let's save this as nothing and paste this. So every Friday, email me a well-designed, awesome, inspiring quote, an HTML email of a random row of inspiration from the tab Quotes. So I told it already, go to Quotes. I didn't tell it column A. I didn't tell it… So I, I wonder if it's that smart to really know, if it really understands it. But it is final. There it is. Woof. And again, "I have updated the file. Would you like me to customize the HTML design with specific colors?" Send a Daily Email with Inspiring Quote - A Little Pick Me Up Roasting Google Sheets Send a Daily Email with Inspiring Quote - A Little Pick Me Up AI Emails Me Every Day

8:41 Can it… It asks this… This sort of leads me… I'm like, if I didn't know anything, I would just keep looking at that and trying it. I, I want to just be definitive. All right. Where is the HTML? Oh, it didn't create an HTML file. It just wrote the HTML body. All right, cool. So there's a set up weekly trigger . That's good. There is send weekly quote, so let's try that one. Send weekly quote, run. Oh, it did send it. Let's go check it So here it is. It has one weird issue, which is emojis in it will look like this. But yeah, looks cool. It's a generally HTML. Weekly Email Sheet as Lead Magnet 150 VCs Actively Investing by Lemon.io AI Emails Me Every Day

9:27 Yes. Email. The problem with the rat race is that even if you win, you're still a rat. Oh, well, that's quite the inspiration. That is probably one of them. All right. Cool. It did it, but a lot of iteration we need, right? We need to probably edit that HTML to a bit of what we want, or we might not care too much. But I like it. I think this is fun. I think Apps Script with Gemini is basically, like, exactly the same as what I've been doing previously with AI and Apps Script , which is I would just talk with ChatGPT or Gemini in another place, and then it would generate some code. But what I've done is I made Automate, and this is bettersheets.co/automate. This allows you to connect to Google directly, grab or create a new… ChatGPT Clone in Google Sheets Part 2 Can ChatGPT Automate Spreadsheets? ChatGPT Clone in Google Sheets Part 2 Bigger Brain

10:18 a sheet, and then in that sheet, it's gonna write better.js, not code.js, so you can use this with existing code. And it will write you code, but it will also figure out what you're talking about. So let's actually connect this Gemini and Apps Script . So I wanna choose a spreadsheet I'm gonna select Gemini in Apps Script And so now Gemini and Apps Script is connected. And the weird thing is that I do need to get the Apps Script URL. So that's one weird thing about this is that i- Apps Script-- Sorry, Gemini in Apps Script means you don't have to do this step, but doing this step Two Things to Know When Starting to Learn Google Sheets Getting Started Coding in Apps Script Advanced Coding in Google Sheets for Programmers 43 Minutes of Google Sites and Google Sheets working together

11:05 means that the sheet and the script we know, and we can write to the script. And so I don't know if you can see down here, but it says the f- tab names. Oh, here it is here. It's Sheet on all app- spreadsheets, Drive files, quotes. And now I can go back to my prompt. Here, let's try this one. Let's try the same one Every Friday, email me a well design. Once I do that, I can also add @quotes. So I know that it's exactly the tab I want. I know it's selecting Gmail 'cause it's highlighted here, and I have a few different modes. I notice that Gemini doesn't have modes. It has a wide and a side, okay? From Sheet to Script And Back Again - Learn to Code In Google Sheets Part 3 No More Google Sheet Tricks Introducing SheetFools Make a Tour of the Sheet

11:52 It's wider or it's on the side. It has good response, bad response, which I don't know if I would know that, copy, cool, but it doesn't have modes. And so what I did is I generate or d- created modes for you. So if you just wanna write some script, we're in write mode. If we want to append only, meaning we don't wanna edit anything, we don't wanna overwrite anything, we just wanna add more code. Or we draft, which is a safe way to say we're just gonna write it in the browser here. But also there's plan if I just wanna write some notes . There's also map if I just wanna understand what are the functions doing now, and generate a little bit of comments to say, "Hey, this is how the functions work with each other." And also if I just wanna ask, I can ask questions , or if I wanna fix. Fix means there's some problem and I wanna change it. Write Your First Script Writer Better Prompts CLUTTERED CODE

12:40 So there is the debug mode in Apps Script , but I've never liked it because it makes it f- feel like it is a code problem only, when in reality, most of the problems that occur with code is not the code itself, but how we interact with it. Oh, maybe the sheet doesn't exist, or the sheet… Or it's putting out data and the code is correct, but the data it's putting out is wrong, so we need to change the code. Yes, the c- code ends up being wrong, but it's wrong for, not because there's an actual bug. It's because our output is different than what we intended. And so this fix mode should help you a little bit more around Sheets than just the code itself. All right, let's actually send this, and see if this is working. Breaking Through Errors In Apps Script Getting Started Coding in Apps Script Breaking Through Errors In Apps Script CLUTTERED CODE

13:26 So we're trying to essentially bring together all of the Google Workspace into here without actually having to access Google Workspace. It's connected already. I already connected it by connecting Google to here, and that was all in the setup. It took just a couple minutes to set up, and now it's writing the code for me But the cool thing is that underneath this is a harness that is essentially going to try to write the code. It will debug it itself. And it is using a lot of the information and experience that I've personally had writing Apps Script for over 10 years. And so it's gonna add, gonna definitely try to add a custom menu . Two Things to Know When Starting to Learn Google Sheets Everything Aggravating about Apps Script Advanced Coding in Google Sheets for Programmers How to Create a Custom Automation Menu in Google Sheets

14:14 That's one of the best things I love to do, and it's gonna en- empower you to enable or disable that. So let's look at our sheet. Let's see our Apps Script All right, let's refresh our sheet again And when we do, we have this weekly quote email. And so we can actually send that e- quote email. It's cool. Let's see what we created Let's refresh. Let's write every Friday, email me. Let's write it. And what it's also doing is explaining a bit of how automations work. It's really a good educational experience. Sometimes I've noticed this, that when I write code for other people, half the Automate Emails What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users Spreadsheet Automation for Beginners Gmail Declutter Engine

15:03 time I'm explaining a few of the bits and pieces of how you interact with the code And what that means is you end up learning how triggers work, what triggers can do, what are the limits of HTML, what are the limits of the script itself, and we become better. All right, let's add it. Let's refresh Now we have a tab called, a menu called Automate. Let's send our Friday email. I think we need to authorize again just because we deleted everything and redid it. And this is the one that Better Sheets wrote. Wow. Or bettersheets.co/automate wrote. Wow. That looks a little cooler, right? Automate Emails What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users Different Kinds of Automation Better Letters

15:48 That's a bit better. I mean, it is essentially the same thing, right? But let's look at the code. Let's go at Extensions, Apps Script , and see It is going to be in better.gs. It show, shows you where it begins. It has some auth script, OAuth scripts that it, scopes that it added. And it writes it so that you c- I think is a little easier to read, where you see the sheet name . It has all of my tendencies of how I write for clients so that they can easily w- write or edit their own code as well, so they're not cut off from the code Here's a weekly trigger . Yeah, we can select the Friday hour Advanced Coding in Google Sheets for Programmers Getting Started Coding in Apps Script Advanced Coding in Google Sheets for Programmers Sync Calendar to Sheets

16:33 Here's the code for the HTML And then it even notifies you of a failure. That's pretty cool So I showed you Gemini inside of Apps Script here. And a few of the failings are not necessarily it's failing, 'cause it will write code, it'll always optimize towards JavaScript, which is well-documented. However, if you wanna use script writing that is for humans and, and based on the script that I wrote myself that is not publicly available, then use bettersheets.co/automate. And I think it helps a little knowing what the AI knows. Seeing the tabs here, having access to just clicking Gmail or Google Docs if you wanna access those. Build Your Own AI Writer in Google Sheets Everything Aggravating about Apps Script Advanced Coding in Google Sheets for Programmers Bigger Brain

17:18 Also we have a little bit of a sidebar here, which you can see where the headers , what the headers are in each of the sheets. That helps you know that it knows what it's doing. Can also connect more than one spreadsheet . If you're saying, "I wanna move data between these two spreadsheets," there you go. It can do that. And it's gonna be a cool learning experience for you, I hope. Like, education and execution should happen at the same time so that we become better at Google Sheets, right? Google Sheets Interface Changes Make It Easier For Others To Use Your Google Sheets Advanced Coding in Google Sheets for Programmers Track Anything! In a Google Sheet