From Sheet to Script And Back Again - Learn to Code In Google Sheets Part 3

Beginner

About this Tutorial

In this video, I'm going to share with you how to go basically from sheet to script and then back to the sheet again.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>0:00 So in the last video, we took some information from cells mushed up into some math and returned it.

And we are programming, we're coding inside of Google Sheets. 0:12 In this video, I'm going to share with you how to go basically from sheet to script and then back to the sheet again. 0:19 Essentially we are accessing from the script the data that's inside of a sheet without having to put it in as we did before. 0:29 In this cost per melay function , we put C3 into the function On the sheet side, sometimes we wanna access a cell programmatically and we don't want to have to enter it on the sheet side. 0:42 We want to go based on the range in the script, and we'll show you that.

So basically we'll go from the sheet to script and then back again. 0:52 I'm gonna make a new sheet actually called sheet two. I'm gonna put some data here and I'm gonna just call it data is here, some text. 1:00 And we're gonna access that in this video. Also, we're gonna go through the hierarchy of how code looks at a spreadsheet . 1:08 So this will be very exciting. So let's go to our IDE or our extensions app script . Again, let's load this up and we're only gonna write one as well. 1:17 Two lines of script. We're gonna call this function get data from a one, Okay?

And we're gonna put nothing in the parenthesis here and in the curly brackets, we're gonna hit enter. 1:30 Once you put the curly brackets, what we wanna do is a variable called cell data , and we're gonna equals. Now, how do we access a spreadsheet ? 1:41 Let me go through what the hierarchy is first, then I'll show you in the code.

So first off, the entire thing is called like a worksheet or a spreadsheet . 1:50 So there is a little bit of confusion sometimes in Google Sheets in particular, this is sort of something that's not in work in Excel . 1:58 So in Excel you say worksheet , and then you say like sheet or tab. In Google sheets, we say spreadsheet sometimes I'll say spreadsheet file, and then I'll say tab for the individual sheets on a, in a sheet. 2:11 So what it is, is the hierarchy goes spreadsheet, which is the entire file.

Then a sheet a spreadsheet inside of it, which is also called a tab. 2:21 Then when we get into the tab, we can access any cell with the A one notation , which is the n which is the notation across the top and across the side here. 2:36 Okay, so that's the explanation, but let me show you because it's much easier to show also, because Google script has auto complete here. 2:46 So if I just start typing capital S spreadsheet , it'll say, Hey, do, do you want this? And yes, we do. 2:53 And what is it? Spreadsheet app. This is the start of the hierarchy.

This is everything in this entire file. The Script that we're working on right now is tied to this entire spreadsheet file. 3:06 And that's this word right here, spreadsheet app. We hit it the dot or period. Then we say get. And in this case, we want to do active sheet because we're gonna use this function inside of a sheet. 3:23 We wanna add on this one parentheses. That's one key thing you gotta know right here is that this get active sheet needs to add parentheses. 3:32 You'll get an error if you only do this without the parentheses.

Then now that we have the entire spreadsheet file and we're, we're on the sheet that we are active in that our formula is gonna be in, we do get range , and then we say a one in quotes, and then we do get value. 3:55 So the value, let me move my face here. The value here is the actual value inside of the cell, either text or a number . 4:03 And then we're gonna finish that with the semicolon. You don't necessarily need to do that. Now all we need to do is return sell data . 4:11 We're gonna hit command S to save. Now that orange button goes away.

Now we can use this, get data from a one anywhere we want. 4:18 So we can do equals get data from a one, do the parenthesis, hit enter, and there we go. In E four, we now have what's in a one. 4:30 Now if we want to change that range , this a one based on what we enter in the sheet, we can actually put a variable inside of this parenthesis here called range and use that instead of a one, we can say range Hit command S. 4:48 Now in, we get an error .

Now in our sheet, we're gonna write in here in quotes a one, and we're gonna get the exact same answer. 4:57 Data is here, and then we can also get a, we can change this to a two anywhere here. That'll get us, get us what's here. 5:04 Hello? Actually it already got it. So we need to go back and change a three. There we go. Height. And that's right there. 5:14 Sometimes we wanna programmatically or automatically access this kind of data that's in our script here.

And we're gonna make one change for the next video. 5:24 To set up the next video, we're going to add ta sorry, edit this. Get active spreadsheet . We're gonna change it to get active spreadsheet instead of get active sheet . 5:36 Why I do that is so that we can run this script from Google servers. We never, we don't have the sheet open. 5:46 So this sh script should still work here. Let's double check.

It does still work here, but now what's gonna happen in the next video is we're gonna automatically we're gonna use this automatically, programmatically. 5:59 So get ready, get started. See you.</div>