79% OFF online library of tutorials for mastering Google Sheets

One time purchase $149

This tutorial is not available for free users. Please purchase a subscription to view this tutorial.

Hey there stranger!

Please sign-up or sign-in to watch this tutorial.

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

About this Tutorial

Let’s talk about the IF formula and the differences. Learn how to insert a checkbox and all the other things it can do for you.

Video Transcript

 All right, in this section we're gonna talk about the if function inside of Google App Script. Not the IF formula, but actually we'll talk about the if formula and differences. And we're gonna talk about check boxes. So let's get started. I wanna share with you what you probably already know now you probably already know insert checkbox.

And what that checkbox is, is a visual representation of true or false. Right now it says false and if we click on it, it says, , you can absolutely, you are able to change these values, but right now we're just gonna use them as true or false. Check boxes are really, really fantastic. Watch. I have@bettersheets.com.

I have a ton of other videos about check boxes. I love check boxes. You can do all sorts of things. You can make 'em huge.  big and then they don't show up as check boxes. They can be buttons inside of sheets. All right, enough of that. Let's talk about if the IF formula you know of and probably love. I love the IF formula.

I have so many uses for it, but if we have a logical expression and we can use a, we can reference a cell B2 and say, is that true or false? And if it's true, we say this is true. This is true. If it's false, we do. This is false.  Now this is probably already what you know, right? And we can change that by doing this checkbox.

This is true, this is false. But what we're gonna do in app script is we're gonna use an if inside of our function we're gonna end up doing a function of unedited. But for right now, actually, let's just do it. We'll go much deeper into un edit soon enough. Oned We'll, we'll keep that to another video, but, Inside of your functions, you can write if now, the syntax of the, if here is different than the IF formula here.

If formula has, if open parenthesis, then three elements Your expression that has to come out either true or false, and then whatever is true and whatever is false in app script, however you have if parenthesis, and inside of the parenthesis is only one thing is the expression that is true or false.

And then you have curly brackets. So the if in, in app, , if it is true, it will run whatever is in the curly brackets inside that. If it's false, it will skip completely. You can also do else if you wish that sort of, you know, if it's false, it'll just do whatever's in else. But you can also just skip it completely.

What kind of expression can we do here? Well, we can combine what we've done before, which is use spreadsheet app and grab that value. , right? The first thing we did is we get value from a particular cell. Well, we have a checkbox, which is a representation of true or false. This is b2. So what we're gonna do is we're gonna create a variable called checkbox.

We'll assign it to spreadsheet app and you should remember all of this spreadsheet, app dot get active spreadsheet dot GI sheet by name. And let's just double check the name. We're on sheet one dot get range. Now we're in the sheet one B two, so we need to do two two. We al also could do obviously B two in quotes dot get value, right?

We've done before. Now this checkbox is going to be either true or false, and we can run if inside of.  Function, which is now on edit, which we'll get to later. We can say it's just gonna be true of false based on that checkbox. So let's see, what do we wanna do? Base if it actually is true, if this checkbox is true, if it is checked, what do we wanna do?

Well, we can probably put like a timestamp in D two. All right, so we'll take all of this and we'll say spreadsheet app dot, get value instead of get value. We'll, so set value, let's do D two. So, Two, four. And what do we wanna set? We'll set the variable, which we'll set here. Oops. We'll do another variable date now equals new date.

And we'll put that here. Or times stamp we couldn't even do. Let's see. Timestamp. That's better. And we'll print a timestamp into the second row, fourth column.  on an edit. So actually we can do a different function here. Type now. All right, let's try this type now. So now if it's false, it'll do nothing, right?

So right now, if, if we run, this should do nothing. See if we get any errors, right.  exactly what we thought did nothing. So if it's true now, we should get a date time in a timestamp in D two. Let's check it. Let's run it. It says complete it. And there we go. We got the date and we got a whole timestamp all here.

It is correct , correct timestamp, correct date. And we know our if is working. . So again, the syntax of this is if in parenthesis an expression that ends up being true or false, a logical, some logical expression we can obviously use this in any different ways. Like we can use a double equal sign to a sign.

Do these two things equal each other? We can do true. Actually, we don't need quotes there. True. Now this is sort of a technology just asking, is this true? If it's true, it'll be true. If it's false, it be false. But I just want to mention this to you because this double equal sign sometimes is confusing.

We're using an equal sign to sign a value to a variable. When we use two equal sign, we're essentially asking, are these two things equal? Is this equal to this?  How I, how I remember this and might be a simple heuristic for you to remember might help you is anytime that I see one equal sign, I use the word is so checkbox is all of this spreadsheet, app dot, get all this to get value.

When I see two equal signs, I think of the phrase in my head is equal two. So we think, you know, the equal sign is. , but in coding, how I reference this in my hand, again, one equal sign is, is and two equal sign is equal. Two. There also is three equal sign, which we won't get into right now. Similar and almost the same as two equal signs, but two equal signs.

Let's you find out, are these two things equal? We will get into this by asking for oned. By the way, when we get to the oned. Portion of this, we will ask if a range that we're getting in the event is a particular row. So we can say something like variable, let's do

range is two two. Or we can even do goals B two here. And is this range. , we wanna know that the checkbox is true and we use two ampersands to you and we can also do two pipes for or, but for now we're gonna do two ampersands for and range is equal to B two. So obviously I assign the, I'm assigning the range b2, so this should be correct.

It actually shows me an error here. It has a little Curies underneath a little red underline telling me that this equal sign is not correct. So we need two equal signs there to say, are these two things true? Is range, whatever that variable is. Is it equal to B two, is it? Well, let's change this to five.

So it's a one column over to the right. And if it's true, it will write here and it's. So why aren't these things true? Well, we can use logger to log dot log, let's say range. Let's see what it does. So we're getting range. Okay? That's why is because this is a range. It's not the actual value, the value B two here.

We have to get. We have to do a little bit extra here to see that these two things are the same. This range is literally the range, not b2. We can do range dot, I think can do variable range, row equals range dot get row. So this is gonna be cool. We can rain, we can do range rope, which sounds like I'm Scooby Doo range rope.

So range row is equal. The, the row of the range. We're logging the range row. And we're gonna ask, is the range row two? Is it equal two? Right. Let's see. Now if we run it and we get execution started, two. Perfect. We should get a timestamp. Perfect. So these, all of these are working together now. Hopefully we're pulling in more things that we've been covering recently and we're gonna cover more stuff soon.

Really excited. Thanks for watching.



Spreadsheet Automation 101: Introduction to Pre-course Videos

Spreadsheet Automation 101: Functions

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

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

Spreadsheet Automation 101 Lesson 3: Email Yourself Introduction

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