Hey there stranger!

Sign up to get access.

Spreadsheet Automation 101 Lesson 1: GetValue - Introduction to SpreadsheetApp

About this Tutorial

Learn to write codes in Apps Script. Understand what SpreadsheetApp is used for. Get to know getActiveSpreadsheet and use it correctly.

Video Transcript

0:00 Hello, welcome to the first actual lesson of spreadsheet automation 1 0 1. You've gone through the pre-course material, maybe you've even gone through the Learn to Code course that I offer.
0:10 That's about 38 minutes. That's six videos. It shows you very quickly all the way through how to code in Google's script Google App Script here.
0:20 But in this course we're gonna go a little bit slower. We're gonna go a little bit more specific. Today is a pretty cool thing.
0:29 We're gonna write code. So you can do this a couple of different ways. One, you can follow along. We're just gonna go to up to Extensions app script.
0:36 You can pop this over into another window and open sheet.new and app script is available to you right from the GetGo.
0:45 So you create a new sheet and you have this function. My function we're gonna write code right now.  We're gonna create a code that takes the A one whatever value is in a one and brings it anywhere else in the sheet.
1:00 And what we're going to use to do this is something called spreadsheet app. So spreadsheet app with two capital capital S and a capital A with no parenthesis is a set of built-in functions inside of App Script.
1:14 Now, when we hit the dot, the period and we have this auto fill, we can see all of the options that are available to us.
1:22 And this is pretty cool. It gives us auto fill it, it makes it faster to code later on. The one thing we're gonna do right at this moment is get active spreadsheet.
1:32 I'll go deeper into this in another video in the next video. And what this does is it gets the, the spreadsheet file that we have right now open.
1:43 So all of this entire file sheet one, all of the tabs involved everything we're gonna get, get active spreadsheet with parentheses we're gonna do dot.
1:57 So a silly mistake. We gotta get, get active spreadsheet. And now when we do another dot, we have this, if you had what I had, which was set active spreadsheet, you'll notice the auto fill doesn't show up because there's nothing left to do.
2:11 We can set active spreadsheet, but we actually have to get the active spreadsheet. And now when we hit the dot, more things show up.
2:18 The next thing we're gonna do is we're gonna get sheet by name. We can start typing it, get sheet, but then we can also select it here and get sheet by name.
2:28 Again, you can type this all out if you want, or you can follow along and try to do as I do with the auto fill, add the parenthesis, make sure you add the parenthesis.
2:37 And we're gonna get the sheet by name. We gotta actually put a, something in here in quotes. If we hit the first quote, the second quote, we'll show up.
2:44 We're gonna do sheet one that is literally, we'll flip over Here is the name of the sheet. Now capital letters do matter.
2:51 If you watch the pre-course material you'll know that. So sheet one with a capital S in quotes, get sheet by name.
2:59 And then we're gonna get range. The range we want is a one. Now we can, now I remember we gotta rename our function.
3:07 We can use my function, but we can also get a one from sheet one. We'll name it all of that.
3:15 Okay, we're gonna hit save. We should hit save early on, cuz the first time you hit save, it's gonna take a little bit of time, a little bit of moments, but now we get range.
3:26 So what this allows us to do is get that sell a one, but we still need one more step. We need the final step is get value.
3:35 That's <laugh>. The name of this whole section is Get Value. And that's why it's because we actually have to get the value from the cell.
3:42 We'll hit save. Now what this is doing is in this function, it is literally just getting the value, but we actually wanna see it.
3:50 We wanna return. So again, from the pre-course material, you'll know we need to actually do something return. And what do we need to return?
3:58 Well, we'll set a variable, the value, we'll call it. We can literally call it anything we want. Almost anything. There are some saved words like name, var, val.
4:09 We'll turn the value. So again, we've, we can type in any text here, but we just have to make sure it's the same text here.
4:16 Return. So now every time we do get a one from sheet one in here, we'll do the same thing as we do every function or formula in Google sheets.
4:27 We'll type a equal sign and then parenthesis. Once we have the name, get a one from sheet one, we'll hit enter.
4:34 We'll see what happens. And that literally loads whatever is an A one. So we can change that, the stuff in a one.
4:40 And we have to hit this again, we'll get that again in another cell. See, this is a little bit cashed, so sometimes you might have to copy it, cut it, create a, a blank cell, and then paste it again and it'll go again.
4:58 The stuff in a one. So anything in a one, we can get anywhere else in the sheet with this exact code.
5:05 So I, I thought, I thought getting the value was the best part of this is get value. We're gonna go a little deeper.
5:12 I'm gonna go in next video more about strategy and and concept of what Spreadsheet app does and what this entire row of code does.
5:20 Join me in the next video.

Courses

Spreadsheet Automation 101: Introduction to Pre-course Videos

Think Like a Programmer: Develop The Mindset of an Apps Script Coder

Spreadsheet Automation 101: Functions

Spreadsheet Automation 101: Variables

Spreadsheet Automation 101: Dot Notation

Spreadsheet Automation 101: Camel Case

Spreadsheet Automation 101: Parentheses

Spreadsheet Automation 101 Lesson 1: GetValue - Introduction to SpreadsheetApp

Spreadsheet Automation 101 Lesson 1: Spreadsheet Taxonomy

Spreadsheet Automation 101 Lesson 1: A1 Notation vs Row,Column Syntax

Spreadsheet Automation 101 Lesson 1: getActiveSpreadsheet() vs getActiveSheet()

Spreadsheet Automation 101 Lesson 1: onOpen() Trigger - Custom Menu

This Seems Like Automation

Spreadsheet Automation 101 Lesson 2: Get Values - Introduction

Spreadsheet Automation 101 Lesson 2: Arrays

Spreadsheet Automation 101 Lesson 2: For Loop

Spreadsheet Automation 101 Lesson 2: Bracket Notation

Spreadsheet Automation 101 Lesson 2: Logger.log()

Spreadsheet Automation 101 Lesson 2: If ( ){ } and Checkboxes

Spreadsheet Automation 101 Lesson 2: onEdit() Trigger

Introduction to Spreadsheet Automation 101 Lesson 3

Spreadsheet Automation 101 Lesson 3: MailApp

Spreadsheet Automation 101 Lesson 3: Email Yourself For Loop

Spreadsheet Automation 101 Lesson 3: Send Email Every Week Trigger

Spreadsheet Automation 101 Lesson 3: Email Other People For Loop

Spreadsheet Automation 101 Lesson 4: Access APIs Introduction

Spreadsheet Automation 101 Lesson 4: UrlFetchApp

Spreadsheet Automation 101 Lesson 4: OmdbAPI get ApiKey, get Data in URL

Spreadsheet Automation 101 Lesson 4: OmdbAPI get data in Apps Script

Spreadsheet Automation 101 Lesson 4: JSON (beautifier) and OmdbAPI parameters

Spreadsheet Automation 101 Lesson 4: OmdbAPI Parameter Picker

Automatically Clear Content | Refresh Reuse Recycle Templates

Automate Google Sheets With Zero Experience

Automatically Uncheck A Daily Checklist

Activate A Certain Sheet When Opening a Spreadsheet