00:00 Hi, in this video, we're going to talk about what you can automate in Google sheets. Uh, and specifically we're going to talk about triggers because there is a bit of a difference between semi-automation and automation.
00:17 So semi-automation happens a lot in Google sheets and actually that's my preferred way to do automations what you might consider automations in Google sheets or write scripts, um, or have, uh, some kind of formulas that automatically do something based on somebody's input.
00:34 So, uh semi-automation and I would even consider formulas if we say equals D eight plus, uh, <inaudible> right. We have this total here, uh, and we say, you know, 78 and 99 here, and we have this, uh, result, right?
00:51 I call this semi-automation meaning it's not necessarily triggered by the user and create something else, generate some other thing it's already here and generates it for you.
01:03 Or semi-automation also includes scripts that we might build. Uh, so I want to skip over that for now and talk about automation because, uh, it's a fairly broad and wide amount of things you can automate in Google sheets.
01:19 But if you have not yet ever, uh, either written a script or done app script, this is a little bit of a next step after just learning how to create a script.
01:31 And it might actually, you might want to watch it because it might actually help you understand why do people write scripts?
01:40 It might be because people will then be able to use that script over and over and over again, based on these triggers, which we'll talk about.
01:48 And so if you've never written a script before, go ahead and take a look at my course. I'll learn to code in Google sheets or intro to scripting.
01:57 They're the same course for videos, 30 minutes, we'll get you started. Um, and it's basically going to this extensions tab and going to app script charity have open and it'll look like something like this, an editor.
02:11 I haven't had a script written yet, but we will in a moment. And right now, uh, this is the basic thing that hap, uh, shows up in Google script.
02:20 But we're not going to change it yet because I want to show you what will happen if we try to automate something and we have nothing, um, let me delete this.
02:27 If we automate things where we want it to happen on a regular basis, and that's where triggers come in. So if we did have a script here and we click over here into triggers, which is the next window down, we can create a trigger and we just have to go over here, click add, trigger on the bottom, right?
02:47 And we have a bunch of options, but as you can see at the bottom, it tells you, you cannot create a trigger without a target function.
02:54 So we have to add functions. We must have something written in here, which write something right now, pretty simple. Um, and I want to just name this high and we're going to do toast.
03:10 So this is it. This is the entire thing we want to create. And it's just a message that will show up in our sheet.
03:17 And I just want to say hi or hello and say hello. And we're going to command essence and save that the orange button goes away when it's saved.
03:26 So now this function, if we run it, it's just going to show up a little message on the bottom, right?
03:33 That says hi, or hello. Sorry. And we can usually see it. Executing might need to review permissions, authorize this. Uh, if you don't see my window right now, I'm just going into the Google account and saying, yes, authorize and allow.
03:52 Um, this is a necessary thing with Google script. We would want to make sure, uh, we authorize it to do something.
03:59 And so here on the bottom, right, it says, hello, and that's it. So we can run it right here. And it says, hello, there's a few other ways to run a script, right?
04:11 I think not sure if it'll happen, but let's say, don't know if this will work, we get an error or nothing will happen because this script isn't in a sheet, we can do another script, like a function Add, and we can take two inputs, a and B, and we can do return.
04:41 A plus B can save that. And if we do this, add one and two or three, we can see four, right?
04:53 This is a function that's based inside of a cell. And this is a function that goes to the spreadsheet and says, Hey, do something in the spreadsheet, a little bit different.
05:02 Um, but w we're not, they're all called functions, but let's see. We want to create a trigger here. We don't want to create a trigger of adding two numbers to, we don't have two numbers.
05:13 We want to create a trigger of this messages says, hello. Maybe it's like a reminder every hour that we've, um, that the hour has gone by.
05:22 Um, maybe it, it helps us remind us that we need to take a break during Google sheets, Google sheets, spreadsheet making.
05:30 So we want to create chooses function. Remember we had this error at the bottom and said, we needed a function.
05:35 And now we don't have that error. We have now options of which function to run. And we must select the one we want to run, choose which deployment should run.
05:45 It'll be head. I don't think I've ever changed that select event source. And this is where the bulk of this video is going to be spent.
05:53 We're just going to talk about these events sources. It's going to be time-driven right. When we say time-driven, we have this type of time-based triggers, there is from spreadsheet and we can have these different, um, options.
06:08 And this is really, really powerful. So we have on open, which means the moment that someone opens the sheets, something happened.
06:17 So we can absolutely do that. We can say on open, give us the message. Hi, and we can save that.
06:23 Let's let's look at that. When that happens, we have an unopened, it says, hi, so I'm going to close this.
06:29 We'll come back to it, but I want to just refresh it. If we refresh, I think it's going to trigger that on open.
06:37 Hopefully it'll trigger that on open. If it doesn't, there it goes. Hello. And we have a little message, a little toast message that says, hello, great.
06:46 Let's go back to our, our, uh, app script and our triggers. We're going to do this a few times. So triggers.
06:55 We still have that unopened, but we want to add another one. Let's go one, choose. Hi, the hello message from spreadsheet.
07:04 And we want on edit. Let's save that now every single time that somebody edits a cell. So if we do hi, hello, and see this message pops up and it pops up again, every time we edit now, that could be very, very annoying.
07:23 If we have a message, right? That shows up every single time we edit, but it could also, you could also set up a function that runs, let's say you want it.
07:37 If you're on a particular sheet, right? If there's like an input sheet, and you want someone to know that their input has been recorded, right?
07:45 Maybe you want to set up an on edit, not a great use case, but there are use cases for it.
07:52 Let's go back to our triggers and see what else, what else we have. Oh, and by the way, there's an edit trigger.
07:58 If you want to just create a function that happens on edit, you can literally do function on edit and any time an edit occurs, this function will run inside of here.
08:09 That's another way to do on it. But there's usually always a few ways to do same thing, unfortunately. Right? We run into that problem all the time, right?
08:18 So I'm going to delete this actually. Now I'm not going to delete it. We will keep it. Let's keep going.
08:24 We also have on change. Now, this is fairly, uh, this is hard to explain between an edit and change. So what the main difference is between on edit is a user is inputting data into a cell or deleting that cell or editing any particular cell on change is changing the entire spreadsheet.
08:49 Meaning we're going to delete a column or add a column or add a tab. So let's look at that unchanged.
08:58 We're going to do the same thing. Just that hello message. And now on any edit, we, I think we would have to refresh this.
09:07 So we just type, hi, we'll get a message. Hi, hello. Now it might not work. If we just do it right now, delete column up.
09:17 It does work. Okay. Usually we might have to refresh the sheet. Um, but you see, we can delete all these columns and that should trigger the hello.
09:28 So in case you want to have a little message that may be whenever we add it or duplicate a sheet or add a sheet, it's like, Hey, you've duplicated this sheet.
09:38 What are you doing next? What's your next thing you want to do? All right, let's keep going. So we have, we're still in the spreadsheet and we want to do on form submit.
09:48 This is really cool. This is fairly awesome trigger. We can trigger things like, uh, emails, automatic emails that say, Hey, somebody edited, uh, submitted this form.
10:01 You can also add in, uh, this is going to sound weird, but a Google form responses page will always insert a row.
10:13 And so one of the common things that I've been asked, and there's a few videos on this, is that you want some formula to be in a, in a row or in a column on the page that has the form responses you want, maybe a running total.
10:28 You want to calculate something and you usually do it on that page, or you want to do it on that page.
10:34 But the result, the answer in the video I talk about this is you actually do it on a separate page or use a rate formula, um, to do it.
10:44 You can't do individual formulas because they'll, they just won't appear. Cause you'll insert a row in, uh, in that sheet.
10:53 Well, this unformed submit trigger allows us then to use script to say, Hey, whatever that, if you submitted a form, go and add to this column, this particular, uh, formula or function.
11:12 And so that gives us another option in that fight. Okay. Uh, the, and also you might also have your own use cases for on, uh, forms of it.
11:21 There are so many things you need to do there. All right, let's go to time-driven. Cause I think time-driven ends up being what people think of as automation, things that occur hourly, daily, weekly, and it gets a little confusing here.
11:36 So I want to explain a little bit, so first off, when we select time-driven, we get an extra option here, select type of time based trigger.
11:45 We have our, but we also have a specific time date and time minutes. So we can run something every five minutes.
11:53 Uh, see we can five minutes, 10 minutes, 15 minutes, every 30 minutes. If you want to do every hour, you do our timer every hour.
12:00 We also have every two hours, every four hours, every six hours, every eight hours, every 12 hours. Amazing. We also have day-timer, uh, which this is every single day daily, but you pick which hour.
12:12 So you have 24 options here between which hours do you want it to run the issue with this is that it will run at some point within that hour.
12:25 And that's it, right? That's not a great like automation to know, okay. Sometime between eight and 9:00 AM great. What I've done in the past is I've used this timer.
12:35 This day-timer a lot. If I want to compile some information, if I want to create a new day template or a new week template, um, and say like execute this thing between, I like usually it's sometime after midnight and before like 5:00 AM, I try to do it before everyone starts working, but you never know when people will stop working.
13:01 Uh, you know, sometimes people will have a late night little session of entering data, especially this gets really confusing. If you have people across many different time zones or across the world, if you have people within a few times zones, not a big deal, right?
13:16 It gives you the GMT and you can usually find a three to five hour window in which nobody has started working on a sheet that you can compile sheets and fix them up.
13:25 Uh, I use I've used this time-driven day-timer before also to redo formatting. So conditional formatting gets really messy, uh, especially if you're cutting rows over and over again, cutting and moving rows really can bog down a sheet and almost get, make it impossible to use.
13:48 So what I would remedy that with was to say, okay, every single day before everyone starts working, I'm going to reset the I'm going to actually set the conditional formatting for everything.
14:00 And so if you ever changed anything or moved it around this conditional formatting, we'll fix it in that day, every single day.
14:09 So I wrote that script once set this time, driven out day timer for the hour before everyone started working or a few hours before everyone started working and we never had conditional formatting problems again, that's it just fixed it with the script.
14:23 Uh, we also have week timer. So if you want a specific day again, great for weekly, uh, reports or weekly fixes, if you want to say, okay, on Monday every single Monday, we need to have this template duplicated and, and named for the day of the week, right?
14:43 The, the date of that week, the start of that week. Great. Write a script, copy, copy sheet, uh, renamed date, time format, the date time, and that's it done.
14:55 And you can write that script and run it every single Monday for the next years, uh, without having to actually do that saves, you sounds like it saves you a little bit of time every single week, like a tiny amount of time, right?
15:08 You're clicking a button and you're typing in the date, but if we automate that and we maybe make a new template for every single person in the company or every salesperson, or we also, in addition to that on Friday, we generate a report and we compile things, uh, every single Friday that would be really that's cool, right?
15:28 Or every Saturday during Saturday and Sunday, there's something that this is a really silly idea, but it does happen, right.
15:36 Uh, if you are working in spreadsheets all the time, uh, you might spend some amount of time on Saturday or Sunday, either cleaning up the past week or setting up for the next week.
15:47 And that's, I personally think is a bit of a bother for anyone who works at a company or even owns a company to have to do something on a Saturday or a Sunday, like have to do it right.
15:58 You have to do it because it's the end of the day on Friday, you're done and you've got to do it before Monday morning, right?
16:05 Your only option is to do it on Saturday or Sunday. But if you write a script, say compile this report and have a PDF waiting for you on your Google drive or in your inbox, or, um, copy and duplicate some sheets and set the dates and times you can set oh day of the week to be Saturday and have this sheet, do it, and then just check it on Monday morning.
16:29 Right? Really simple stuff. Uh, really simple triggers here from this time driven. Uh, so, and then we also have month.
16:40 So last is if you have the first of the month or the last of the month or 15th, maybe you have to do payroll on the first and the 15th every day.
16:47 And you do every day, every month. And you have to just send a message. Hey, go to this sheet. This is actually okay.
16:55 I've done triggers where I will literally email myself the link to the sheet. That is like a quick reminder. Um, and then the last event sources from a calendar.
17:09 Now, this is interesting because this connects our Google calendar to our sheet. So you just type in someone's email address, which is the owner of the calendar, usually your own.
17:25 And then you can only do calendar updated. And this happens whenever the calendar is CRE a calendar event is created, updated or deleted.
17:38 Uh, and this is really cool. If you ever want to connect your Google sheets to your calendar. Now we've gone through just very quickly all of the triggers from spreadsheet time-driven, uh, and from calendar that we can do.
17:53 And I have spent most of my time in time-driven, uh, to be honest, I've spent most of my time in these trekkers.
17:59 Time-driven picked all of these, but hopefully just walking through some of these have given you some ideas on how to use these triggers with your own, uh, functions.
18:07 If you're writing your own functions, I do want to give you one more resource, uh, because these are culled triggers.
18:14 Um, but they're called actually in Google apps script, uh, Google world called installable triggers. Uh, there are two types of triggers and you've just seen the installable triggers.
18:27 Uh, let me see if I can find the other name for, uh, here it is. The other one is called simple triggers, and I use this a lot in if you've watched other videos that I use on edit and these unopened, uh, unopened can give you, uh, you can create a menu, a custom menu for your scripts inside of Google sheets, and you use the on open trigger, simple trigger on edit.
18:52 I use a lot cause it's really fun. And that's really just go literally going into the editor and typing function on edit function on edit that if you do this, you are using the simple trigger, the installable triggers.
19:07 This is what we just went through and walked through are all the time driven event-driven triggers. And I really do recommend reading this and going through this installable triggers a reference on Google app script.
19:21 It might give you some more ideas. It also will share with you some restrictions. Um, because whenever we say we want to automate something in Google sheets, sometimes what we want to automate is literally impossible.
19:36 That's because certain triggers might not go outside of the balance of a cell. They might just not just, uh, they might not have permission.
19:45 Also some triggers might not be able to go outside of a sheet. Um, if you ever used my product called only sheets, it involves executing a function on another sheet, and that is pretty hard to do or pretty hard to, uh, actually execute.
20:06 Um, but we want to do this all the time, right? We want this sheet to do something else in another worksheet.
20:11 Um, we want to create worksheets and, um, there are some restrictions with this. So it's very helpful to read through these restrictions on a simple triggers and installable triggers.
20:22 There's also one other thing, and I found this out is simple. Triggers have some restrictions that are not a restriction on installing triggers.
20:31 So this on edit function has some restrictions and I can write on edit and make it super fast and super quick.
20:38 But if I want to do some things, I need to write the function here and then you use an installer double trigger.
20:47 I know that sounds confusing to do the exact same thing we wanted on edit, do something. Um, and that's it.
20:54 I would, I would just highly recommend if you enjoyed this video, walk through and have some ideas go through Google app scripts are insolvable triggers and get a little bit more information and it, uh, maybe we'll give you a little bit more nuance than this 22 minute video.
21:13 Hopefully this has been helpful. Thank you so much. And this is now what you can automate in Google sheets.