Hey there stranger!

Sign up to get access.

Spreadsheet Automation 101 Lesson 2: onEdit() Trigger

About this Tutorial

One of the things to learn: We can only have one onEdit() in our entire spreadsheet file. I’ll also walk you through on how you can use Event and Logger to get cool information.

Video Transcript

0:00 All right, this is really cool. We're gonna learn the unedited function. It is a trigger that is what's called a simple trigger.
0:08 Meaning when we go to our app script, we literally just have to type it in and we can only have one in our entire spreadsheet file.
0:16 So we type function on edit with the capital E, and we have some code that executes. Now what we're gonna do in this particular example is we don't want to actually run this function.
0:31 What we end up doing all the time, we want to use the if part of the function to say, is this in the correct row?
0:40 Is it on the right sheet for what we're doing right now? We're creating a task list and we wanna put in the D column next to each one of these tasks.
0:49 When it's done, we are checking them off with this checkbox. So we wanna know when does the checkbox get checked and then enter the timestamp in the de column here, next to the task one, the moment that it's done.
1:05 Okay? So we need to know that this checkbox is in the B column. So we need to know that the edit, whatever edit is happening is in the B column.
1:14 What happens with an on edit is we'll have an event and that event will be this this variable here. We don't have to pass this in ourselves.
1:25 This trigger on edit will happen all the time. So let's actually log this and let's just see if we can see something here.
1:33 We can't run this on edit. We can try. You're gonna get probably in. Yeah, no, nothing's happening. There's no event happening.
1:41 But if we go over here to our executions we'll look at all these other, this one execution, right? We had nothing, but let me just check off this box B2 in sheet one and see what happens.
1:57 Let's see. We get our simple trigger type, simple trigger on edit that is happening without us doing anything that's happening because the user of me is editing that checkbox.
2:07 And let's see what that event shows us. Let's move my face up into the center <laugh>. Look, we have so much cool information.
2:15 We have the value that is, it is executing. The value is turning to true that checkbox, it tells us where the source is.
2:21 It's a spreadsheet, tells us the old value, which is false, tells us who, which user. And it gives us a range.
2:28 This range, even though it just says capital R range, it actually has a lot of information into it. And that's what we're gonna be executing.
2:34 We're gonna get the source, the spreadsheet name, we're gonna get the range. We're gonna know what row, what column, everything about this.
2:40 We're gonna, we know the value of it. Really cool stuff with this on edit, with this event. It's triggering all this information.
2:47 All right, let's go do it, right? Let's go back to our editor. Let me move my face again. All right, it's gotta load up again.
2:58 All right, so here we are. We got our event. We need some variables, right? We need to know the range, we need to know what the value is.
3:04 We're going to use essentially the if here and we wanna run our variable timestamp equals new date. And we want to do spreadsheet, app dot, get active sheet, wherever.
3:24 I guess we can do active sheet dot, get sheet by name, sheet one dot get range here. We're gonna get the range, the row and the column dot set value.
3:40 And this value we want is timestamp. All right? Actually this column is going to be, we know it four, right?
3:46 It is always gonna be the D column. That row is gonna be whatever range we get in that row. So let's see.
3:53 So if our check, if our event, event variable event ring row equals event range dot get row, let's see. We could always dot logger dot log event row.
4:12 And if event row, we'd actually don't care about the row. We need the we need the row to know when to set this down here, but we need the event column to check.
4:22 Is it the second column on sheet one, event column equals event dot range, dot get column. And let's actually event column log that.
4:38 All right? If event column we want this two equal sign is two, then we wanna run this whole thing, right?
4:51 We wanna put the timestamp in next to the row. So we got event row, the right row. There we go.
4:59 So let's see if this actually works. Boom, we got it. Let's look at our log. We can go back to executions and we will see this is the simple trigger.
5:12 Our log two right there. Boom. Perfect. So what we did, let me review this, is we have this event that's running here.
5:20 And every time that we edit something that event's running, when we find out we gotta get the row, we gotta get the column of that event.
5:28 We can log that event column. And if that event column is two is equal, is equal to two, then we're gonna create a timestamp.
5:36 If it's not, it will skip it completely, it will just skip all of this if cool. One thing we also probably want to do is make sure we have an and on that the variable sheet is equal to event.source.gi.
5:56 I think this is get name, so this is wrong. Actually, I just did variable sheet event dot source dot getName.
6:06 I think it's actually, this is the name of the entire spreadsheet. So it's actually the range dot get name get sheet, probably.
6:28 Nope, that's not it either. All right. I think it's get source dot, get active sheet there and then get name, let's see if that is correct.
6:46 Can look at executions while it's doing it. There we go. Perfect. Can look at our log here. Sheet one. Perfect.
6:57 So there we go. We got the sheet name. So now what we're doing is we're making sure that the event that's happening is on the right sheet.
7:08 It's on the right column. And that once we know that column on the same exact row we're going across and we're writing whatever the timestamp is.
7:15 So that's what this is doing down here in the if column. And if it's none of those, if it's not event column, the column is not two.
7:23 If it's not on sheet one, then we're skipping all of this and we're doing nothing at all. So this on edit won't happen if say we added some text here Or we wrote rewrote this task, right?
7:37 Task, nothing is happening until we click done on the B column and then we enter a timestamp. So that's pretty cool that in on edit we can create these triggers and with, if we can say, alright, if this happens, then we'll do this.
7:51 If this happens, we'll do this other thing. Really, really cool. And we can get so much information from this event object here in the on edit, which is very different than doing a regular function where in that parenthesis you have some type of variable.
8:06 Here Google Sheet is essentially inserting a variable, but all of that information we saw before the range, the sheet that it's on, the spreadsheet, that it's on, all of this cool stuff we can access based on getting the range, getting the column, getting the row, wherever it is, we can find it.
8:25 And then once that is, we can compare, okay, is it on the sheet one, is it on this sheet, is it on this column?
8:30 Is it in this row? And then with that information, we can use that information to do some task here, which is really, really cool.
8:37 Thanks for watching.

Courses

Spreadsheet Automation 101: Introduction to Pre-course Videos

Automate Google Sheets With Zero Experience

Automatically Clear Content | Refresh Reuse Recycle Templates

Spreadsheet Automation 101: Functions

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

Automatically Uncheck A Daily Checklist

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