00:02 We're going to create an onboarding scheduler. We're going to create a series of emails. We're actually not going to create the emails.
00:22 We're actually going to automatically send the emails when they need to be sent along with the access to the sheets and give them access automatically.
00:31 So this is going to be very interesting, I swear. But first I want to make the sheet pretty so it doesn't blind you while you watch this video and all.
00:40 So just stick with me because I think this is going to be one of the most interesting videos I ever make.
00:44 Alright, we use sheet styles by BetterSheets.co.free, Google Sheet Add-On to make this sheet look better. So what we have is we have a series of documents.
00:53 I'm just going to have a first document here. And we're going to document one, sheet one. Document two, sheet two.
01:04 We're going to have some email here. Basically, we need a schedule. What's going to happen exactly is we're going to put someone's name here.
01:11 Let's say I'm onboarding to this company, right, Andrew? My email is, you can give them, you can put actually, Andrew, add, let's say, finish.
01:21 Sheet's dot co. That's my email address. And I want to send them this document, Document one. I want to give them access and I want to send them an email telling them they have access.
01:32 Because what is going to happen, this is all going to happen automatically. We're going to literally do this automatically. But you'll see in a second.
01:39 Let's make some more. Let's Sheet dot new slash three. This is, I'm not sharing it right away. And this is Sheet one.
01:49 We just get that URL. All we need is the URL. All we need is the email address. And actually, I am in my workspace now.
01:57 So I'm going to do it for someone outside my workspace. Camfy at gmail.com. All right. All right. And we're going to send myself this document.
02:05 When you get access, read this. So basically, you can create SOPs, standard operating procedures. You can create checklists for people when they sign on to your organization.
02:17 And you can send them both an email. Say. And hey, check out this document on a schedule, say a week later or day on their first day, send them another document the second day and then give them access on that day in the exact same place.
02:32 So this is going to be really cool. So right now, you can see that this first document is shared with nobody.
02:39 It is private only to me. So we're going to go to. App Script, we're going to work in here and write a little bit of script.
02:45 If you are a better Sheets member and you're watching this, then down below at better sheets.co. Tutorial. If you're watching this there, you can get this sheet absolutely free included with your membership.
02:55 If you're not watching, you should become a better Sheets member. Alright, we're going to create a few functions here. We, need to set up an email to this email address.
03:05 This is b3, so let's say variable email equals, gotta get that get active spreadsheet. We gotta get that range, which is in sheet, get sheet.
03:22 By name I think it's for still sheet one dot get range. I think it was b2, b3, as well get values.
03:35 Now, that, this line right here is just gonna get the email that we're gonna send to. Send, send an email.
03:40 So, We wanna send an email. I'm gonna say what do we wanna say? Well, actually, first off, let's just do mail app.
03:48 Send email. That's the first thing. The recipient is gonna be email. The subject. We'll do that later. And then the body here.
04:00 Okay, let's, Let's say variable subject equals, what do we wanna do here? We wanna say let's call this send email.
04:15 Let's say, let's just call this next onboarding step. And we're also gonna add a timestamp new date. The reason is because if we name this next onboarding step for each of the emails that we send, then they're going to be threaded together.
04:33 So we just add this timestamp. We can add other things like what is the step but for this, we'll just do a timestamp right here.
04:39 Plus new date, get a timestamp. What is. Is the body variable body equals we're going to need we want to do this.
04:51 We want to know what are we sending B for. Hmm. So we're gonna say something like hey, you can have their name.
05:02 It's gonna be B2. So we can take all of this and put a variable on board name. And instead of B3, we get B2 and, And we add plus on board name.
05:26 Plus we'll do let's take out the, there. Let's do a new line. We want to, let's say here's your what actually we need to do with new line this way.
05:49 Here's your next document to review. Here's your document to review today. That's what we'll say. Then we'll pat a colon.
06:01 Let's make sure that can, civilizations correct and then put the document and for document I don't know if we want to put it right in here.
06:09 We might want to do doc here. So doc. We'll have a doc URL. Okay, but before we send an email we need to make sure that someone is actually a viewer of this so add, viewer on board viewer and what do we need?
06:31 We need the email address. We definitely need that. So we'll copy that again. So for a test right now we will put doc in here but I want to do a variable doc.
06:44 Equals, we're just going to get the URL here, document one. Put that in quotes. And we can just use the regular quotes and we want to say doc.
06:56 And we're going to use this ad viewer. We just need an email address. And you can never remember the syntax of this.
07:05 So it's so much easier just to go to the Google Docs help here and see it here. So we have ad viewer email address and we need ads given user to list of viewers for the file.
07:18 If the user already was available as editors method has no effect. So if there are no. No have no relation to the document.
07:25 They'll add as a viewer. If they are an editor, it won't do anything. If they're already a viewer, won't do anything.
07:32 So we need a class file, which is going to be. This is a document. So we need. For able document.
07:45 Equals document app dot get. Think ahead, the drive here. Now we have document. This is probably better. And what we need is to get the doc and then add it to add the viewer.
08:00 That's it. Should be it. But we'll see if we get. Any document dot add viewer, the perfect email. Now what this ad on board viewers doing, it's getting our email from B3.
08:14 It's getting the document that we have hard coded here, but we will get that again in another way soon enough.
08:19 But just to test it out, I'm going to run this right now. I'm going to choose add on board viewer, run, see if we have any issues.
08:29 I'm going to authorize. I would recommend doing it this way as well to just test it and authorize it right away.
08:36 Let's see if we've been shared. It has now been shared. You saw no cuts, no anything. I've now shared. I'm going to remove access so we get to see that again and we get to do it again.
08:50 But basically, now we can take this document, so we don't even need to delete it. We can just comment it out, put doc there.
09:01 Now we have these two functions. So we've already written send an email. We can send an email based on the document.
09:08 We can send an we can add a viewer based on a document and their email address. But how do we schedule these things to go?
09:16 How do we combine them? That is the question now. So one thing I did is I will just add documents here.
09:24 The. Just makes it a little simpler, but let's say we have a key here sent. Or not sent. Maybe we want to say, maybe what we want to do is every single day, we're going to send an email.
09:38 But only if there is something to send, meaning if there is nothing in this sense. Column. Each one of these document, it may be this is document for day one.
09:49 This is day two, day three, day four, right? And in the send column, we're just gonna, gonna write sent if it's been sent.
09:57 Okay, so we need to function. Check. Check send. And we're gonna probably do use this to be the trigger. So every day we're gonna create a trigger, we're gonna have it time driven.
10:15 You'll see that in a second after this. But we're gonna need something to trigger. We need to check send. What are we gonna check send?
10:21 We're going to go to our. Or spreadsheet. And get a an array. Call it sense. We get sheet by name dot get range.
10:40 We're going to get whatever range is here. So we want like be four to see seven, let's say. There we go.
10:57 And then we're going to get values. Why we get values. Instead of get value is that it is an array, a two dimensional array and we can actually log this to look at it first.
11:13 Let's look at the sense. And let's save and then we'll check send, run this and we'll see this array there is a document and then if it's sent or not is there as well.
11:25 So we can see if these are all there and we have all of that. Let's look at that in the log as well.
11:38 And it should be a two dimensional array. We have four arrays in an array here. We have the first item is going to be this.
11:45 A document and then the second item in each array is going to be the sent if it's sent or not.
11:49 Okay, so how do we then send it if? Not if we wanted to do four. We want to do a four loop here equals i equals zero.
12:00 I is less than since that length. We're gonna actually get, let's double check this length. Since dot length i plus plus.
12:14 Then we're gonna put a little function here in a hot second, but let's just log this since dot length and make sure that it's four here.
12:24 Four. Exactly. So. That's how many there are. So there's four items and for each one we want to check if since.
12:34 I. Zero. Is equal to blank. Then we want to break out once we do first one, we just want the first one.
12:53 Since. So then we're gonna do add on, we want to add the onboarding first. With since. Same thing. This is the first item.
13:32 This is the second item in the first array. It's going to look at is it sent or not? We are going to delete that.
13:42 We have all the same document. Then what we need to do is actually write a sent in here. So we are going to do a spreadsheet app.
13:54 Take all of this. This eye is going to be here. Get range is going to be which row. Let's put eye in there first.
14:06 For a hot second, we want column three, number of rows. Actually, I think this is going to be one. Nope, this is going to be I.
14:15 One, one, we'd only want one to do. We want to set value sent. Actually, think this should be up here, not equal to sent.
14:33 No matter what it is, maybe there's some date in it or whatever. We're only going to send one at a time because we're going to break out of this, I do believe.
14:40 But the first time this runs, this i is going to be zero, and it's going to be the fourth row we want to do.
14:46 So this is going to be zero. This is i plus four, I believe. And let's see if this runs. So first off, let's go back to our documents, see it.
14:53 We have nobody shared on it now. It's probably only to me. And I can also check my email and let's just run checksend and see if we get an error.
15:08 Mm hmm. We have written sent here. We have in our document, it is shared with one person. And we are in our, let's look at our sent folder in our email.
15:20 Let's look at our sent. And there it is. So next onboarding step and we need. We need to add a little space here.
15:28 But hey, Andrew, here's your document to review today. And if I open it in an incognito window, I don't think I'll be able to open it.
15:35 I'll have to sign in. But I'm already, I already know I am already shared on this with one person. That person is here.
15:41 So that's pretty cool that we can then send these documents one at a time. But let me. Add a trigger here, right?
15:48 We need a trigger because we don't want to come in here and click this send every single day. So we go over to the left side here where this clock is.
15:56 Go to triggers. Round my face down here. Add trigger. We're going to choose which function to run. We're going to check send.
16:06 We're going to time driven, select event source time driven. We want to change this to a day timer. We want to send it you know, in the morning ish, so eight to nine AM.
16:16 You know, before nine AM, you'll get the this day is, you know, onboarding documents. Click save. And now we'll see a trigger here, which will.
16:26 Send every single day. But at some random time between eight and nine AM and then every day thereafter, it'll be every 24 hours.
16:34 If you want to delete it, just go here, delete trigger. Because what's going to happen is, let's say. These are all sent.
16:41 I want to double check that this is not sending continuing to send every day after the first four. Days. So let's, we have the time and trip driven trigger already done, but I can see it right now.
16:52 It is all sent. These are all say sent. So does this trigger trigger? Does it actually send an email? And we can check our inbox or our sent and double check.
17:07 There is nothing here. So you can check that as well with this code if you want to double check that it won't send.
17:15 If everything is sent, it won't send again. But you do probably want to go back and delete this trigger just in case.
17:22 And you can always use this as a template where put in the number. Of, you know, documents they need to have all the all of the documents here have sent in this column, put the email address there and save this as a template and copy it every time you have someone onboarding.
17:39 Should be pretty fun and pretty easy to do and simple for you to, like, onboard people one day at a time with the specific SOPs.
17:47 That they have also double check. This is probably all within one week, you know, weekends it might send as well.
17:56 So watch out for that. Bye.