[00:00:00] Hey hackers, would it be crazy if in five minutes you learned a new programming language? Let's get crazy.

[00:00:07] Let's learn to code in Google Sheets. I did it live a few weeks ago in front of a group of about a dozen indie hackers. And it took me nine minutes. 

[00:00:15] You can access this exact document here at BetterSheets.co/hackers. You can directly get this document, get all the code

[00:00:23] but watch this video. This is a tutorial video, specifically for those who already know how to code. If you're a programmer, you know Python, you know JavaScript, you know some other programming language, this is for you. We're gonna get you started in Google Sheets, cuz you know programming and I know Google Sheets.

[00:00:39] This is where we meet. By the end of this video, I hope that you'll know how cool Google Sheets can be. So let's go, let's get started. It's been a minute already. 

[00:00:47] in Google Sheets. Where do we code? Let's go to sheet.new, I add a two cuz I have a couple of accounts here. Once this sheet starts to load up, we go to extensions app script.

[00:00:57] Every Google sheet has access to this. Now every function looks like this function. Literally the word function, then the name of the function. Parenthesis and then curly brackets. You can add in parenthesis any sort of variable names that you want to add in, and then you can use those variables and execute code inside this curly bracket.

[00:01:14] Look, if you know Java Script, you know Google Script. Cool. All right. Following along here. Variables use a ver far and then variable name equals. Now the semicolon you can use just like you use in JavaScript. If you want to keep that style, you don't necessarily have to use it. Look, let's just create a function right now.

[00:01:34] Let's call it cusper, Melay or cpm. This is a very common math problem that we use in marketing. We wanna know our cpm, so we're gonna write this function right in Google Script. So we're gonna call it cpm. We're gonna have our. variables. Here we're gonna have two variables. We're gonna have cost and count, and then inside the function we have our variable melay or meal.

[00:01:58] I don't know how, how you pronounce that. It's gonna be count divided by 1000. Again, you can use a semicolon if you wish. We're gonna do var CPM equals cost divided by melay again with a semi call in. Now how do we get that number out? We do return. Cpm. Now, if you see an orange dot over here on the left, that means we haven't saved it.

[00:02:21] So we're gonna hit command S and we're gonna save. And now that orange.is gonna go away. Eventually, please go away. We're saving the project. Saving the project. I might have to pause the video. There we go. We got it. So now we can use this function inside of Google Sheets. So if we have two numbers, we have our cost, we have.

[00:02:41] Impressions

[00:02:42] we have here, like 450 and we have 3000. If we want to do equal CPM, 453,000 loading and there's our number co, our cpm, we can also reference these cells so we don't have to type in this number so we can. C2 here, and for 3000 we can reference c3 and here we go. We have the same number. So now as we change this 400, we can do 10,000 impressions and it will give us a cpm.

[00:03:16] but let's keep going. Let's tap into the infinite power of Google Sheets. It's like a database we can access directly, backend, front end, all one and the same. Isn't that crazy? We have to learn no css. We have to learn no html. We're just learning JavaScript. We're just coding so we can access a value in a cell inside of a.

[00:03:36] Now it's very important that we know that sort of a hierarchy that a cell is in a sheet. So let's put something in a one and let's get that data from a one through a script anywhere we want.

[00:03:49] Okay? We're gonna put an A one, like maybe the amount of sales we have, right? And we're on sheet one. We gotta remember this, right? If we want to add more code, we just hit enter a few times and now we can start writing a new code and we can write. Get data from a one on sheet one, and this is just the name of it doesn't do anything.

[00:04:08] We can change that name anywhere we want. We need parentheses and then curly brackets. And inside the curly brackets, we're gonna do variable cell data. Then we can name that anything we want that variable. Now here's the magic that happens. We are using spreadsheet app and it's gonna be pink here. We're gonna do dot.

[00:04:28] Active and as we start typing it, auto says, What do we want? We want get active spreadsheet. Here's one trick you have to add, parenthesis to this one. Now we had a dot get sheet by name and as we type, it's gonna try to know what we mean, sheet by name. Again, we need parentheses and we need to enter here in quotes sheet.

[00:04:52] We then do dot get range. Now we're going from the entire spreadsheet to the active spreadsheet to the sheet that is the name we're going down in hierarchy. Then we're gonna get the range of that sheet, that sheet or the cell. We're gonna do a one. And then once we have the range, A one, we do get value Again, we can end in semicolon if we want.

[00:05:15] Now that we have. All of this, we have the hierarchy down to the exact cell. We want a one and we will return it. Return cell data, hit command s to save, make sure that orange dot goes away. And now anywhere we have a one here we can do equals it can actually copy this entire thing, get data from a one

[00:05:36] And we just do the parentheses there. Now, in E six, we have number 50. We're just getting that data from a one. Isn't that cool? We're now accessing basically a data spread. Any data on this spreadsheet we can access by its range, by its sheet name, by its hierarchy, right? A sheet, an active spreadsheet, the name of the sheet, and the range that it's in.

[00:05:58] So now if we have that kind of data, maybe it's like on a sales page and we want to copy that to a log. Now this is gonna get a little complicated. If you wish to copy this specific script again, go to BetterSheets.co/hackers right up here. Go here, BetterSheets.co/hackers. And you can get this Google Doc exactly to for yourself.

[00:06:21] Copy it, use it. Copy this code, try it for yourself. . But here we're gonna do, we're gonna copy all of this code and what does it do? It actually will do nothing. Now, why will this do nothing? It's because we've commented it out. So in Google Script, we can do two slashes in front of any text we want, and that is commenting out.

[00:06:43] So it won't actually run just like in any other programming language. You might want to add some comments. We do that with two. So here we've written out in words what we want to do. We want to get data from the sheet sales summary. So we need a sheet called sales summary,

[00:06:58] and we wanna copy that to a sheet called Sheet log. Now what we're doing is basically we have. A sales amount here, and we can update this every day, but we don't want to, We want to actively edit this every day, but we want to copy it to this log so that it's timestamped and it has a reverse chronological log of all of the sales of every day.

[00:07:19] And this is how we're gonna do, We're gonna get data from the sheet sales summary, what we want to copy. We're gonna get the sheet and range of the sales log where we wanna copy it to. We're gonna insert a row above the top row just in case there's no more rows. Then we're gonna get the range and we're gonna write a timestamp.

[00:07:34] The timestamp we're gonna use is new date. That's how we're gonna do it. Then we're gonna copy the data. That's it. All right. I've gone ahead and written this code out and we're gonna walk through it a little bit right now. So we have function copy, sales data two sales log, our variable data, just as we did before.

[00:07:49] We get the range of the name of the page and a one. Then we get the range that we want to go to, and then we insert a row, literally just the destination sheet dot insert row before we write the number one. And then we have the range of the timestamp, and then the timestamp is literally new date. Then here the.

[00:08:11] we're gonna set that date, the val, sorry. We're gonna set that date in the, as a value of a time stamp. Then we're gonna copy it to where the destination sheet, by default, the values in formatting are copied, but we can override them here with contents only. True. So we're only copying the contents. That is key because of sometimes we don't wanna copy that formatting, so we're gonna hit command s and.

[00:08:36] Now, in order to run this, we need to actually authorize, how do we know that is if we choose here, get a copy, sales data to sales log, and we hit run from inside here, we're going to get authorization required. Now, we only have to do this once when we run the code for the first time when we have new scopes.

[00:08:56] And so you'll always, sometimes, sometimes you'll always have to test it. So now we have get range of no. because I have the wrong name. So I have sales log here. And this tells you this type error has an error and it shows you GS 24. It's because it's in line 24. So what it's doing is actually, it got the, I put in the wrong name of the log, so we go change the name there.

[00:09:19] Now it should run again. We're gonna run our coffee sales data, it completed, and there we go. We have a timestamp and a sales amount. Now we can do this. . Now if something doesn't work as you saw there was an error, but sometimes there will, the execution will complete, but we don't actually see our answer.

[00:09:36] So what we can do is log so we can add up here or down here. Actually we'll add at the end logger dot log. So this is great for debugging. So if we add here, logger dot log data dot get value, and we hit run inside here, we see the 50 right there. So we can do this again. If we change this to 55, we can go back here, run this again, and we can see this information here.

[00:10:02] 55. So this allows us to log little points to debug along the way. Really, really cool inside this ide, right? So we're not really programmers in Google Sheets yet. We're, we're sort of writing code that executes when we wanna execute it. But what happens if we wanna run a CR job, right? We're not programmers unless we're lazy programmers, right?

[00:10:25] We don't wanna run this, this ex, We don't wanna execute this exact code by ourselves every single day. We gotta set our timer and hit run. No, we can do it with a crime. What the really Google sheet says. Qu jobs. Yes they do. Right? Code ain't shit if we don't got qu jobs, right? We're hackers cuz we're lazy.

[00:10:47] Don't wanna click one button every day. So let's set this up. We have our function here. Copy sales data to sales log. What we're gonna do over on the left side, we have this timer button that says triggers we're gonna hit trigger. right under my face. over here on the bottom right, we're gonna add trigger.

[00:11:04] We're gonna change it to copy sales data. Which deployment should run head yes. Can't change event source, we're gonna change that to time driven. Once we do that, we have a time based trigger, so we can do this every day. Day timer, we can select the amount, the day time period we want to run it. Now, this is one specific weird thing in Google Sheets, is that you cannot set a specific exact time, but you can set a hour of the day.

[00:11:35] So if all of this code runs on Google servers, we can do like 9:00 AM to 10:00 AM Great. Now save. Now we have a trigger that is gonna run every single day between 9:00 AM and 10:00 AM and it's going to do exactly what we did before, which was take this number, insert a row here, and add it to the top, and then have our time stamp.

[00:11:55] again, if I'm going too fast in this video, you can go to BetterSheets.co/hackers. And all of this is in this Google Doc, so if you don't like watching this video, you can go and read the documentation. But wait, there's more. So someone asked me about an api, how to access an api. So this for Tebo?

[00:12:12] Yes. The same Tebo who made Tweet under, he asked, How do I access an API through Google Script? Well, let's, for our example, use omd api.com. First we need an API key. So if you're following along, you want to. Do this yourself on your own sheet. Go to omd b api.com and get your own api. I'm gonna use mine here.

[00:12:34] We're gonna create a sheet here called API Key. We're gonna put it in a one, and then we're gonna get the jsun. We're getting at J on here and we can walk through this if you wish, or sorry, you can walk through this if you wish. But the main piece of information, the biggest piece of information you need to know is URL fetch app dot fetch.

[00:12:54] And we're gonna grab the A, the URL with the API key there. So this is what you need to know your L fetch app dot fetch. So we're gonna go back to our code, gonna go to our. . Now, if we don't want to continue writing new scripts here, we can always add a new script right here, script, and maybe we call this api.

[00:13:16] and we can copy that or delete that and we're gonna paste it. So this says, get our API key exactly the same code we had before, which is spreadsheet app. Get active spreadsheet, get sheet by name, get the range, get the value. Exactly same. We're accessing that information from the spreadsheet. Now we have the URL of OM db.

[00:13:35] You can go check this out on om db api dot. , Was that No? Yeah. Omd api.com. You can add in the url, the API key and the title you want to fetch. So we want to get Jason title. Now what that does is actually goes and grabs the API key from here. And let's go to movies. Let's do equals get jsun.

[00:14:00] and we can actually put this, get Jason in a one, or sorry, put this in D one, but we can do hackers. There's an error because we don't have a title. There we go. Title now. 1990 fives movie Heckers. But that's not all. We can actually get the, the poster. So let's get the poster just from the. Hackers or from the any movie title.

[00:14:23] So here's the function. Get poster. You. Again, you can read this inside of Better Sheet. Do Code slash Hackers. You can grab this url sorry. Code. So we're gonna use this, We're gonna do json stringy, we're gonna grab the poster url, and then we're gonna return the we're gonna return the URL of the movie poster.

[00:14:44] So instead of this data we're gonna do get poster A one. And inside this, we wanna put this around image function. This is a Google Sheets formula image, and takes in a URL and it shows an image. So, let's see. There it is. So hackers, now we've changed any title here. We can do Jumanji, any title we want, and we can get the movie poster.

[00:15:08] Jungle Cruz. There we go. We can get any poster with, with this API in Google. , and now we know Google Scripts. Isn't that cool? I'm so glad you watched this entire video. Thank you for watching. If you wanna follow along the code and get the code again, go to BetterSheets.co/hackers. Go get it, go use it, learn how to program in Google Sheets.

[00:15:32] Thanks for watching. Bye.