Getting Started Coding in Apps Script

What you need to know to access Apps Script inside Google Sheets. Code in Google Scripts. Automate business processes and more.

Transcript:
(00:00) We're going to try to replace all of this. Look at this big thick JavaScript book. Uh, you might be intimidated by something like this, uh, which I was, and I am, I mean, you look here, I'm not that very far into it. Uh, JavaScript is massive. It's a lot of stuff, but apps, script is so much fun.
(00:20) App script is the Java script, like a coding language inside of Google sheets. And I hope this video in just 10 minutes is going to do something for you. That I, that happened to me 10 years ago, which is getting started in Apps Script just the very, very basics. Of Apps Script are fundamentally what you got to get through, but it gets so much fun.
(00:44) I've been having so much fun lately. Coding and app script and combining that with AI. So you'll see a lot of that on this channel here soon, but I wanted to get you started right away. Get started. Even app script coding right now. Hey, so we're gonna get started with App Script today. This is gonna be a very interesting video cause we're gonna do this live.
(01:04) I have a recorded video, uh, about this, like the first 23 minutes of, uh, spreadsheet automation 1 0 1, which is the course that goes three hours. It goes into all kinds of stuff about app script that you'll want to know. But I wanna get you started real quick here today so we don't have to go through the entire course.
(01:22) Um, but if you do the pre-course material, which is section one on the Udemy course, Uh, spreadsheet Automation 1 0 1, it is available. All 23 minutes are available, but let's see, uh, we're just gonna go to sheet.new. We're gonna slash twoit so that we get in the right account. Uh, so if you're using sheet.
(01:41) new and you have multiple accounts you can use slash we're just gonna call this, uh, getting started in App Script Script and we're probably gonna say getting started coding in App Script. before we do anything in the sheet itself, uh, actually we're gonna go up to Extensions app script. Now we're starting, this is it. This is the ide.
(02:04) This is where we code. This is everything to do. This is what we do where we do everything with app script. Uh, we are here the, and we're started. Uh, the first thing that shows up whenever you start a new app script in a sheet is function. My function. Some couple parenthesis, open parenthesis and parenthesis, and then it has open curly brackets and closed curly brackets.
(02:28) Right inside here, we can hit enter. And all of this stuff inside of these curly brackets are gonna be inside of our function. And we can name this function anything we want with a couple little parameters to think about. But first, let's say we have some data inside of our sheet. We're gonna say, this is data.
(02:46) Uh, this is a number. This is a number. We're gonna add a number here, 55, and we're gonna add some data, like maybe a status or something, and we're gonna go say, yeah, we're live. All right. We wanna grab this information, right? Maybe in our app script, this is one of the fundamental things to do in app script is to grab some data that's on a sheet somewhere.
(03:08) One value in a sheet. All right? We're gonna do variable. data in sheet. We're gonna capitalize this in a very particular way. The first letter is lowercase, but then anytime we add words, we're gonna keep it as one word and we're gonna capitalize that first letter. So, the I and the SR capitalize, we're gonna say the same thing in this.
(03:30) We're gonna change the name, my function to get data from sheet. And again, we're gonna capitalize. Let me scroll in here. Uh, scroll in, zoom in here. We're gonna capitalize the D. The F and the. and now we have a, some kind of function we're writing here, and we're gonna do V var, which means variable.
(03:51) Variable is we're gonna grab some data or get something that we want to use somewhere else in the code. We're gonna use a variable. All right. Variable equals, what does this variable equal the most powerful thing or the, the simplest powerful thing we can do is spreadsheet app with a capital S. When we type in spreadsheet app with a capital S and a capital A, it turns to purple so you know you're doing it right.
(04:13) If you do not have, uh, a capitalized, it will not work. All right. Spreadsheet app. Get active spreadsheet. We're gonna get the entire file here. This is uh, get active spreadsheet actually. And then we're gonna add parenthesis. We have to add parenthesis for this tour. We're getting the entire file, not just one sheet, not just one tab, not just one thing.
(04:33) We're getting the entire file with get active spreadsheet. Then we're gonna do get sheet by name. We're gonna use auto complete to. Hit a few letters and hit enter, but when we do this, we have to add the parenthesis. We're gonna add inside, get sheet by name, two quotes. We can, we only need to type in one quote and it'll finish the second quote.
(04:55) We want to get this sheet one, so we're gonna type in sheet one, and that's the name of the sheet. So that's why, why we say get sheet by name sheet. now get range. We're gonna do dot get range. In parentheses, we're gonna do quotes. Uh, B one I think is the one we want to get. We're gonna go back here and say, yeah, we want this status B one.
(05:19) Now we got the range, we got the sell, we got the sheet that it's on. We need to get the actual thing inside of the. Inside of the cell. So we do get value and we don't have to do anything else here except add these parentheses. Now, for our function to work, it needs to do one of two things. Uh, first it can, uh, change something about the sheet, uh, or it could return some data.
(05:46) And that's what we're doing right now. We're return, we literally type in the word return, uh, what our variable is, data in sheet. Cause we don't actually know. Value is, but we do know the variable that it is called by it. So we just do return data and sheet. We're gonna hit command S uh, that's gonna save our project.
(06:05) Over here on the left, we could have seen like orange button over here, orange dot. That means we're not saved. But anytime we change something, you'll see this orange.here. We must save when we. that dot, we can hit command s or we can click this, uh, little disk, uh, icon up here called Save project.
(06:24) If you hover over it, it'll say Save project. We can save that project. Now, once we create a function, a custom function inside of our app script, get data from sheet, it's called, we can use that in our sheet. We can type in equals get data in. I think that's what it's called from sheet, sorry.
(06:43) I get data from sheet with a capital F and a capital S, and then we do parenthesis. Now it's going to have a, um, Red underline, it's okay because it doesn't know that we have that custom function, but when we hit enter, it'll say loading. And here we go. We got our live status right here, and we can reference that anywhere.
(07:05) Of course, this is a very simple, uh, example for you, uh, to go through coding that this is all the coding. We're gonna go through these fundamentals again and again and again. If we wanna do a little bit of math. Oh, what I mean is like you can actually use this like, one, right? That's the same thing as what we did.
(07:25) But using code here allows us to create a lot of different variables and add stuff together. So maybe we want to say, uh, return status and with a, a colon and a space in quotes, and then whatever that status is, which is data in sheet. And maybe we want to change this to status the variable so we can change this to status.
(07:50) Uh, save up here and now when we get data from sheet, it's going to change. Get data from sheet. It's didn't say say status. So now that is a little bit of difference, uh, from that number. But hey, we have a number here. Let's do some something interesting from there. And actually we can even change this to get status from sheet.
(08:14) We can name it whatever we want that we'll remember, right? We might not remember if we're adding more and more tabs. We might not remember. B one is the status of, uh, status here. We might not remember. But we could remember maybe even something simpler than get status from sheet. What about just status? We can do that.
(08:36) Do this and equals now status and we can grab the status anywhere and it'll say status live. We have changed that, that word fundamentally by adding those together. Okay. Let's do a little bit of math function. Triple. We're gonna triple some. , that number is going to be right here. We're gonna add that inside of these parentheses, this number.
(09:00) And what that is, is a variable. This is anytime we have something there that's going to, we can use that in our, in our function. So we're gonna add those curly brackets and we're gonna take variable. Tripled, we're gonna call it tripled equals whatever the number is. We're using the exact same word that we use here, and this is a variable that anything we enter here, we can triple, we're gonna do times three, and now we return tripled.
(09:28) That's it. That's all we have to do to do some cool math here. So now we're gonna take, let's make sure we've saved that project, that that orange button, orange.is gone. And we go equals triple. And we're gonna use B2 in there and see what happens. We get a triple number. .
(09:52) Now this might be very rudimentary for you, but let's do something more interesting. Let's try cpm. So CPM is cost per millet. And we can even have two things. We can have a cost and then maybe views. We want to maybe tr figure out what's the cost per view over, sorry, cost per thousand views. So we're going to variable cpm. We want to return our cpm, and our CPM is going to be equal.
(10:16) cost divided by views, divided by 1000. So what this is doing is taking the views, dividing it by 1000, taking the cost and dividing it by that to get our cost per thousand views. Cost perme, uh, CPM is cost perme layer cost per thousand. So we're going to get this and maybe we have get some error here, but maybe we have, our cost here is gonna be $550 and our views are gonna.
(10:45) Views are gonna be 1 million. Let's see how many, how much cost per thousand views that is. We're gonna say cpm, and again, we can do the math here if we really want, but we've already done the math. We do CPM cost, which is b3, and the views before, see we get 55 now. I think that's 55 cents, uh, per thousand views.
(11:12) This is pretty cool, right? We can create some math inside of a function and create a custom function that exists inside of our sheet. We can also take our function and do some value manipulation with it. Add some different words, add words to take away words. If we wanna do some JavaScript and or do some math, and we can do it without having to do this entire book, this JavaScript book we can throw away because.
(11:39) We are, we can do it in App Script, which is also JavaScript. Thanks for watching getting started in app Script. That was just the beginning. Watch here. 23 minutes of the first pre-course material of spreadsheet, automation one. You saw spreadsheet app in action here in this video, but there are so many more that are covered in this video.
(11:59) Uh, you'll see functions, you'll see variables much more explained, uh, but then when you get into spreadsheet automation 1 0 1, we're gonna talk about mail app. You're gonna be able to email something to yourself. You're gonna be able to automate your business practices.