Apps Script you need to know while using ChatGPT

0:00 Here's some things I think you should know about Apps Script , even if you're using chat GPT to code in Apps Script . So Apps Script allows us to automate Google Sheets in really interesting ways, but there are some things that ChatGPT may not get you across the line for, essentially. First thing is Extensions, Apps Script . This is where you're going to put all of your functions . Let's write a function with ChatGPT that does something cool. For instance, let's create a function that will insert a row just before the header, just under the header. So let's go to ChatGPT, create an Apps Script that will insert a row just below the header of the active sheet. So this is very interesting thing to say active sheet.

0:48 I'll show you something about that later. See if this does it correctly. Let's copy this and see if it works. So it's going to take function get active sheet . This is actually doubling. I don't think we need this. The header row is just going to be the first row insert after that should do it. So we can take this insert row run. Let's see it run. We're going to have to authorize. I think if we're running Apps Script for the first time. Yeah. Review permissions. You're going to have to do that. ChatGPD won't be able to do that for you. Let's click allow down here and there. See, we've inserted a row, but it's got some kind of formatting . So let's go back to ChatGPD and say, without any formatting , do this without any formatting . Yep, so it's just going to clear the format of this newly inserted row.

1:35 Actually, it adds a little bit that we don't need, but let's go back and insert that. So I'll show you this is a little too verbose. I mean, it still works. That's sort of the beauty of ChattyBD and coding here is once you know you can insert functions here they're gonna run fine. And we can always test them. There you go. So now it's inserting a row without any formatting . Actually, it's clearing the format . One better way to do this, though, and ChetCPT may know how to do this, is, let's actually clear this format , and instead of insertRowsAfter, if we go to sheet. insert row and there's this other one called insert row before and we can just do two there and that way the formatting of the first or second row is going to be preserved also we can just do get active sheet we just need parentheses at the end of that and saving this and running it should execute perfectly fine and there we go we also have you This drop down menu. So Chachapiti knew how to do exactly what I said, but we wanted to do it a little differently.

2:20 One other thing Chachapiti won't be able to help you with, or maybe it will, if it's chatting with you is how to do this automatically. Like literally we have to come into Apps Script and click run. And there's like a way to. Set this up that this is going to do happen every single day. Let's say every day daily. How do we do that? We're gonna go over to our left side here. Click on triggers. Click add trigger on the bottom, right? We're gonna choose a function if you have multiple functions , you have to choose it from this drop down menu. We're gonna insert row below header from spreadsheet . We're gonna change this event source to time driven. We're going to Go to day timer And select the time of day, and now we can do like, maybe every morning, before we come to work, 4am to 5am, click save. Now this trigger is going to literally do what we did, which is just go in and run it, but it's going to do it at a time before we need this done. So that we can come here and there'll be a new row for us to, to fill in or whatever. Chetchpd may guide you through that, but I think it's super helpful to show you this in person.

3:06 Also to delete that trigger , go over to triggers. Again, we're in just extensions Apps Script, go over to the triple button here, to options, and you can click delete trigger. Again, ChatGPT may walk you through that, but won't show that to you. Something that ChatGPT may do for you is when I edit the third column, I want to bold the entire row . Create an app script here we're doing an edit and this is an on edit trigger , which is going to be a little bit different than our function up here. Our function is going to have the name on edit. Let's copy this code that does correctly. We can add functions . We don't need only one function . We can have multiple functions in app script. This function on edit is a. actually built in function. We can only have one. And that's something that chat GPT may not tell you. It may not allow you to know that you just have to know it that on edit is a built

4:42 in function , but there can only be one. This E is going to be like an event that happens. And we're going to get the range of when that editing happens. We're going to get the column three, get active sheet , get the row, and then we're going to change it to bolt. Let's check out if it works. We're changing this to March and it bolded it. Perfect. It's working as we accept one thing here We didn't ask Chachapiti to do but you should know is that this should also be Rho is greater than one and Rho we can get actually variable Rho equals Range dot get Rho. So that's something that we can do Thing that chatgpd didn't add here, but we want to add this because we don't want it say we rename this to

5:29 year or months instead of months. We don't want it editing this row. So that's perfectly fine there. But how do you actually do multiple things in and on edit? So we have this event. We're going to get the range . We can get the value even and log the value for example. So variable value equals E dot value and we can log it logger dot log value. Save, make sure you save all of these functions and we're going to type in 33, we, actually we're going to change this to April, change this to June. And if we go to our executions, here's our past executions and each one is going to have that log. So log is going to show up if we run it from our editor , but if

6:17 we run it from somewhere else. Go over to this executions and this is where you can find any logs that you have. Here's June, the one before that was April. That was the value in the cell that we edited. ChatGPT won't be able to show you that. Hopefully that shows you there. But now, this if can be used multiple times. So we can say if value is equal to, we need two equal signs to say, hey are these things the same? If the value is June, Do something and in curly brackets, we're going to say we don't want to bold the entire sheet. We want to bold, let's say we want to highlight something. So what we can do is say take this. I'm going to add a comment and go highlight or change background

7:04 of whole row to bright neon blue. And now I'm going to take this and copy paste it. Finish this script and let's see what it does. Get sheet and set background. Perfect. So let's copy this part and add it right into here. One thing I want to share with you about ChatGPT is if you put your entire code in, copy the code, say edit this part, and then copy the code from ChatGPT. Sometimes, not all the time, but sometimes, ChatGPT will delete part of your code. That is really a poor thing about ChatGPT, that it, it doesn't really know it's doing that. It's just trying to help you and you're gonna, it's gonna add whatever you have asked it to add. But it will delete. So a lot of times I will take parts of my code and delete it.

7:52 I'm going to paste it into chat GPT. So let's see if this works. I'm going to change this to June. And there it is. So all the June's are going to be highlighted blue. And now if I want to copy this and do something else, say with May, maybe May is a different color, black or gray four, four. So let's change that, save it, change this to may they're super gray, almost black. And that's how you can do multiple things with one function on edit. It is use this if, if needs the logical expression here, it's going to be either true or false inside of the parentheses. And then inside of curly brackets, do whatever it needs to do. If it is true, if it's false, it'll just keep going. It'll just skip all of that. Personally, I've really enjoyed using Chachapiti along with Apps Script .

8:40 It makes it very simple to do these sort of one line, couple line of. Apps Script also, it helps extremely well with for loops. If you're looking for more deeper automations and how I use Apps Script , subscribe here on YouTube to BetterSheets, but also go over to bettersheets. co and take the course Master Spreadsheet Automation. It's available on Udemy as well. Find it there. Master Spreadsheet Automation will get you up to date on all the Apps Script you need to know to automate your Google Sheets.