00:00 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.
00:07 I have a recorded video about this, like the first 23 minutes of spreadsheet automation 1 0 1, which is the course that goes three hours.
00:16 It goes into all kinds of stuff about app script that you'll wanna know. But I wanna get you started real quick here today so we don't have to go through the entire course.
00:25 But if you do the pre-course material, which is section one on the Udemy course spreadsheet automation 1 0 1, it is available, all 23 minutes are available.
00:34 But let's see we're just gonna go to sheet.new. We're gonna slash two it so that we get in the right account.
00:41 So if you're using sheet.new and you have multiple accounts you can use slash we're just gonna call this getting started in app Script Script.
00:52 And we're probably gonna say getting started coding in App Script. All right, before we do anything in the sheet itself actually we're gonna go up to extensions app script.
01:04 Now we're starting, this is it. This is the ide. This is where we code. This is everything to do. This is what we do where we do everything with app script.
01:14 We are here the, and we're started. The first thing that shows up whenever you start a new app script in a sheet is function, my function with some couple parenthesis, open parenthesis and parenthesis.
01:26 And then it has open curly brackets and closed curly brackets right inside here, we can hit enter. And all of this stuff inside of these curly brackets are gonna be inside of our function.
01:37 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.
01:46 We're gonna say, this is data. 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.
01:57 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 grab some data that's on a sheet somewhere.
02:11 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.
02:21 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.
02:29 So the I and the S are capitalized. We're gonna say the same thing in this. We're gonna change the name, my function to get data from sheet.
02:37 And again, we're gonna capitalize, let me scroll in here scroll in, zoom in here. We're gonna capitalize the D, the F and the S.
02:46 And now we have a, some kind of function we're writing here. And we're gonna do V var, which means variable.
02:53 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 variable, all right?
03:00 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.
03:09 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.
03:15 If you do not have a capitalized, it will not work. All right. Spreadsheet app dot get active spreadsheet. We're gonna get the entire file here.
03:24 This is get active spreadsheet actually. And then we're gonna add parenthesis. We have to add parenthesis for this to work.
03:31 We're getting the entire file, not just one sheet, not just one tab, not just one thing. We're getting the entire file with get active spreadsheet.
03:38 Then we're gonna do get sheet by name. We're gonna use auto complete to just hit, hit a few letters and hit enter.
03:47 But when we do this, we have to add the parenthesis. We're gonna add inside, get sheet by name. Two quotes.
03:53 We can on. We only need to type in one quote and it'll finish the second quote. We want to get this sheet one.
04:00 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 one.
04:07 Now get range. We're gonna do dot get range in parentheses, we're gonna do quotes. B one I think is the one we want to get.
04:16 We're gonna go back here and say, yeah, we want this status B one. Now we got the range, we got the cell, we got the sheet that it's on.
04:25 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.
04:37 Now for our function to work, it needs to do one of two things. First it can change something about the sheet or it could return some data.
04:48 And that's what we're doing right now. We're return, we literally type in the word return what our variable is, data in sheet.
04:56 Cause we don't actually know what that value is, but we do know the variable that it is called by it.
05:02 So we just do return data and sheet. We're gonna hit command s that's gonna save our project. Over here on the left, we could have seen like orange button over here, orange dot.
05:12 That means we're not saved. But anytime we change something, you'll see this orange.here. We must save when we see that, that dot, we can hit command s or we can click this little disk icon up here called save project.
05:26 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.
05:39 We can type in equals get data in sheet. I think that's what it's called from sheet, sorry. Get data from sheet with a capital F and a capital S.
05:50 And then we do parenthesis. Now it's going to have a 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.
06:03 And here we go. We got our live status right here and we can reference that anywhere. Of course, this is a very simple example for you to go through coding that this is all the coding.
06:14 We're gonna go through these fundamentals again and again and again. If we wanna do a little bit of math, oh, what, what I mean is like you can actually use this like B one, right?
06:25 That's the same thing as what we did. But using code here allows us to create a lot of different variables and add stuff together.
06:33 So maybe we want to say return status and with a, a colon and a space in quotes. And then whatever that status is, which is data in sheet.
06:45 And maybe we wanna change this to status the variable so we can change this to status. We hit save up here and now when we get data from sheet, it's going to change, get data from sheet.
07:04 Didn't say say status. So now that is a little bit of difference from that number. But hey, we have a number here.
07:10 Let's do some something interesting from there. And actually we can even change this to get status from sheet. We can name it whatever you want, that we'll remember it, right?
07:19 We might not remember if we're adding more and more tabs, we might not remember. B one is the status of status here.
07:28 We might not remember that, but we could remember maybe even something simpler than get status from sheet. What about just status?
07:35 We can do that. Do this and equals now status and we can grab the status anywhere and it'll say status live.
07:44 We have changed that, that word fundamentally by adding those together. Okay, let's do a little bit of math function. Triple.
07:54 We're gonna triple some number. That number is going to be right here. We're gonna add that inside of these parentheses, this number.
08:02 And what that is, is a variable that says anytime we have something there that's going to, we can use that in our, in our function.
08:10 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.
08:19 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.
08:30 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.
08:44 And we're gonna use B2 in there and see what happens. We get a triple number. Now this might be very rudimentary for you, but let's do something more interesting.
08:56 Let's try cpm. So CPM is cost per mil and we can even have two things. We can have a cost and then maybe views.
09:05 We want to maybe try figure out what's the cost per view or sorry, cost per thousand views. So we're going to variable cpm.
09:14 We want to return our cpm. And our CPM is going to be equals cost divided by views, divided by 1000.
09:23 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.
09:30 Cost perme 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 be, views are gonna be 1 million.
09:51 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.
10:01 We do CPM cost, which is B3 and the views B four. See we get 55 now. I think that's 55 cents per V thousand views here.
10:14 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.
10:22 We can also take our function and do some value manipulation with it, add some different words, add words to take away words.
10:30 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 now we <laugh>, we can do it in app script, which is also JavaScript.
10:44 Thanks for watching getting started in app script.