00:00 Hey, so you are managing your guest list in a Google sheet. That's great. Let's actually do some really cool stuff with it and teach you a few formulas that'll help you manage this list.
00:10 And this, these tips could also be very useful for any event whatsoever. When you try to come get a list of people's names, you're trying to deal with those names and then you're trying to get the RSVPs.
00:22 So let's do this. First off, one of the easiest formulas you absolutely already know or may be familiar with is let's just get a total number of people that are coming.
00:33 We can do that with count all Count A, and all we have to do is do count A and then the range of a column here.
00:40 We've written all of our guest lists. And there we go. We got 200 people. It's double check. Yeah, it's 200 rows of 200 people.
00:46 First name, last name. And there we go. We got 200 people coming to our wedding. You know that. But let's see, do we actually have 200 people if we wrote these names?
00:56 If we win through our contacts, our catalogs, our memory, and we wrote out a bunch of names, especially if it's two people writing names, especially if you have like a large group of people organizing an event, like maybe it's a church event or some other event, and you have a bunch of people that
01:11 you're like, we gotta invite this person, this person, this person, and the list keeps going. You might have some duplicates.
01:16 So let's see, how do we figure out duplicates? We can use the match formula for this. Ab absolutely there is the duping data cleanup here.
01:25 Remove duplicates. But what that does is it re it just deletes it. It doesn't necessarily know that. It doesn't let you know that there are duplicates.
01:32 I wanna know if there's duplicates because maybe I want to talk to someone about this, right? I want to, I wanna say why did we invite this person twice?
01:41 Do we need to put them in the party or something? Do we need to invite them more, right? Is it someone we have to have special arrangements for?
01:47 So I just wanna see what this is. I wanna see these people, these people's names. So we're gonna use match.
01:53 We're gonna search a one here, and we're gonna use this range, the entire A column and the search type, we're gonna use zero.
02:00 Now what happens is, right when we do this, we should get 1, 2, 3, 4, 5. We're getting the row that it's on. So all we have to do to make sure that this is the only one here is we do match minus row, and now we get zero.
02:17 We do that for each one and we should have zero for all of them. But what happens if we get down here?
02:24 Oh, did you notice that we have negative four? We can also take this list when I believe this cut on, we can also sort it sort.
02:35 There we go. Oh, we got those people right there. There's negative 98. Let's sort it again. Try to get those people.
02:42 Where is it? Ah, actually <laugh>, this is so funny. So we can scroll through here and we can see there they are.
02:50 Here's four people. Journey Pool. Yep, there's two of them. Cherish Cook. Yep. Mateo, we got 'em twice. Sydnee, yes. We got four people twice.
02:58 Now this has saved us four seats, right? Because we, we now maybe have four people that we can invite as well, and then we know exactly who they are, right?
03:05 Maybe we put next to it who put them on the list so that we can search through this a column and say, okay, both you and me, we invited the same person.
03:13 Or who <laugh> or I put this person on twice. You know, we will play the blame game here. So now we have, we can delete them and look up here and say, equals count all again A, A and C.
03:27 We have 1 96, right? We've deleted four people. So that gets us D duplications. Now what we can also do is go to extensions.
03:36 Nope, not extensions tools. Create a new form because we wanna people to rsvp. Maybe we have their email address this year.
03:43 And we, we are gonna just do a little quick this this and@gmail.com. I know this is not a true Gmail, gmail address, but maybe we have their email addresses and we want to email them and then we say, Hey, we're gonna email you this form.
04:00 And we're like, rsvp, can you come? Yes, no. And then maybe we add here dinner. Dinner. Are you gonna choose chicken, beef, or veggie, right?
04:18 And then we might wanna also ask any allergies. And this is gonna be a short answer there, okay? We're gonna send that.
04:27 I think we need to first actually go up to settings and make sure you have the responses. If you're using a workspace account, turn that off that you're only selecting the re restricting.
04:37 Turn that off. Now we have this url. We can send this URL to people and say, Hey, RSVP for our wedding, we probably also need their full name <laugh>.
04:49 Now that I realize this, let's go back to our questions. Add one more name. What we could do here as well is in that name we can prefill get a prefilled link.
05:04 This is cool right name. I'm going to do all caps name here. And then I'm gonna go click get link, copy that link.
05:15 Now what's the link We wanna send people, we wanna send them this link, but we want to put their name here.
05:22 So we concatenate this equals all of this and this name. Oops. No, no, no. Whoops. We did that properly. We just need to delete these rows.
05:37 Cool. So now we have a link. Let's look at this. Here's Derek, if we type in this your url, we're gonna send to Derek.
05:44 Derek's name is already filled out so they don't need to fill in their name. Isn't that cool? Now they just have to say, yes, I'm coming.
05:51 Yes, I'm taking the chicken. I have no allergies whatsoever. Right? And now they're on our form responses. We got a yes from Derek over here in the E column.
06:02 And so what we can do as well is say you're up at the top. Maybe we have their email address.
06:09 Let's add it. Ooh, we don't want 101 row above. We have their name, we have their email and we have their rsvp.
06:17 So we can go here, we can go equals filter all of this by this name. Wait by this is equal to name, right?
06:40 We haven't found anyone. Okay, so we go if error, we're just gonna get nobody. We're just gonna get a blank there.
06:47 Perfect. Who is it? Derek Wagner, RSVP'd. Perfect. So now we know exactly if they RSVP'd, we can also get all of their information, right?
06:57 Instead of saying just their name, let's grab not the E column, but the B to D column. I think. I think we need to add one more to the right.
07:08 Yeah. So rsvp, yes, we got their dinner and then their allergies. All right here. Now all we have to do is send them the link here, have 'em fill it out with their name already attached to their email address.
07:21 This is rock and roll in this wedding. Okay? Now as we get more RSVPs coming in, we really want to make sure we're capturing all of the the catering.
07:30 So we have gonna have a catering here. We have chicken, we have beef, we have veggie, and we wanna get a count of how many are each one.
07:39 So we're gonna use count. If we're gonna go back to our start one, we're going to go over to that dinner column and we're gonna count if it is a one here.
07:48 So this chicken is a one and we have three of them. Now all we have to do is put a dollar sign in front of the two Ds here and we can copy this over.
07:57 And now we have zero for beef and veggie. And we can also just, let's double check that this is gonna keep adding up totally automatically, right?
08:07 We're gonna get ahamed some al I mean he has an allergy, right? To cats, let's say not eating cats, I mean like just he has an allergy to cats.
08:14 Sorry, <laugh>. Let's, so now we have a veggie and on our catering we have one. So now we have a little summary in our sheet of all of the catering things.
08:27 We, we can just send this to the caterer and be like, done. We, we counted everyone's rsvp. And then if you want people's specific allergies, right?
08:36 We have someone with cat allergy, we can have here, let's do this equals we're gonna sort, actually sort the form responses of allergies, but just D two to D and we're gonna sort that.
08:53 So now all of our allergies are gonna come, come to the top and we're gonna get a nice little list, but we're gonna want to say allergy here.
09:00 And then we're gonna want to have the guest, so we can use here equals V lookup, search key cats look in this column to E and we wanna return the two.
09:21 We're gonna say false right here. So there we go. We have a V look up of the allergies. We now get a list of everybody's allergies.
09:27 Let's add another allergy on here and you'll see that this is gonna happen automatically. Let's do it. We got a yes re is coming, we got veggies and she's got, she's allergic to pollen and bees Submit.
09:44 Now we go back to our catering page. There we go. Now I'll do is do V look up down the row.
09:50 We're gonna get some na so we won't do that. But now we got name, her name right there, her allergy right there.
09:55 Very specific allergy. Okay. Now here's probably the toughest part of this watch we do as we go through this, we wanna email everybody a reminder, a follow up.
10:06 If they are not RSVP'd yet, we're gonna probably send this maybe a month before two weeks before, a week before our deadline, right?
10:13 Not even before our wedding. We, we need to be prepared a month before our wedding. So we need to email these people a few times and be like, Hey rsv, we just need to know a yes or a no.
10:23 So we're gonna go up to here extensions app script. I'm gonna write a little app script. Super simple. Just watch I cover this also in spreadsheet automation 1 0 1.
10:33 If you haven't taken that course I do a lot with ma. Not a lot. We don't do a lot with mail app.
10:38 We just go through the specifics in our function. We're gonna write mail, app dot send email, and what are we gonna send?
10:44 We're gonna send to a recipient, a subject and some body of the email. We're going to have a variable recipient is going to be equal to some Gmail account, which we'll get in a second.
10:59 The subject will be please rsvp. Something simple like that. We can always say have, have much different subject. You can always have much different variable body will equal.
11:15 Hey, just checking in if you're coming to my wedding. R S V P before May 1st here. And then we wanna add here the Google form Link.
11:39 Okay? How do we get this stuff? How do we get the email? We're gonna say the recipient is the email.
11:46 So a few ways we can do this. Okay, we could do this actually a pretty, we can do this almost <laugh>, unbelievably simple, but by just putting the variables, we have the variable here in the B column and the F column on sheet one.
12:00 Right now I'm gonna move this F column over here to B and F. We need to know just B, C, and D, which ones don't have anything in D, right?
12:09 So what we're gonna do is gonna do variable, variable array of guess equals spreadsheet, app.gi, active spreadsheet.gi sheet by name.
12:23 I think it's sheet one. I don't, yeah, in quote sheet one dot git range. We're just gonna get all of A no B I think it's B to D.
12:36 We want B, C, and D. Yeah, get values. Now we are going to need the email from each of those rows that don't have an RSVP that are either yes or no in the not yes or no in the D column.
12:55 How do we get that? We're gonna do, first off, we're gonna do a four loop I equals zero I is less than array of guess that length And then I plus plus.
13:12 So we iterate through it and for this we are going to say second. Okay? So we can do get each row with just this array of guess.
13:24 I what I wanna show actually is I think we need to do B two here. Let's actually log this row and I'll show you what's going on here.
13:37 We don't need any of this yet, so I'm actually going to comment out all of these things just in case we actually do send it.
13:47 So, okay, we just wanna log the rows, let's run it and see we need to get the review. These permissions go through these authorization.
13:57 It's only happened like once when we use that mail app or yeah, send an email a**. You, there we go.
14:04 See all of these are LA Rose. Each one of these is row. The first one is going to be variable.
14:11 What was that again? Row, no email is equal to row. It's one, or sorry, zero actually we can log that email.
14:33 We can do variable RSVP equal equals row one, right? That's this co. No, that's actually the Google form link. There we go.
14:49 Variable RSVP is equal to row two. Again, I go through these things in spreadsheet automation 1 0 1, I go through all of what this means, BRCA notation, dot notation, all of this stuff.
15:04 We, the first lesson, section one, actually the first section is all about getting this spreadsheet app, this one line of code done.
15:10 And we go deep into this. So if you're interested in reading this and understanding much more, go take spreadsheet automation 1 0 1.
15:17 All right. So let's see. Actually, let's log all of these. Google Form link, just to show you, these are all different things.
15:43 See we got the email, we got the what else we got? We got the Google form link and we have the RSV V, which is gonna be blank, right?
15:53 If we are if we want to send the email so we can take these logs off. Now we don't need these anymore.
16:08 And then we also wanna move all of these here, but we wanna put it inside an if, if RSVP does not equal Yes.
16:21 And then, or is the two pipes they're called RSVP does not equal. No. So cuz if they say no, we don't want to send them million again we wanna send 'em only a link if they have not said yes or no.
16:38 So we put all of these emails here. Boo boo, boo boo, boo boo. Just Moving these over and now let's see dog logger dot log.
16:58 I'm just gonna log the email addresses of everybody and it's gonna log it right here. We're gonna run this. I'm gonna see all these email addresses, right?
17:07 These are people who have not RSVP'd yet. Journey Pool. They're right. Mateo is probably not on. Oh, Mateo is there.
17:16 Oops. Okay, I think I just had to switch the or the pipes here to Anne. So it's, it needs to be there neither yes nor no not or cuz then it'll run.
17:29 Okay. So yeah mate, Mateo's not here anymore. We have Brooke Foster, let's just double check she's not there. Yep, she's not on this list.
17:36 So only the people who are not anything right have not RSVP'd. And we get their email address and we can send them an email and say, Hey, just checking.
17:45 Here's a Google form link and it's their, again, it's their form link that has their pre-filled name in. So they just have to fill out yes or no, click a couple buttons and it'll come here and have their name and they will RSVP right here.
17:57 We will see all of this stuff automatically once we send them the email and they actually answer this form. This is pretty cool, right?
18:03 I hope you're excited about this. You can un do these comments. If you want to see that the mail app is actually gonna work.
18:13 I'm not going to, cuz these email addresses are completely fake. And so I'm gonna get, I'm just gonna get a bunch of errors and it's gonna go and try to send these emails and I'm gonna get a bunch of weird bounces.
18:23 All right, thanks for watching this. I hope this has helped you manage your wedding guest list. Or again, if you are using Google Sheets to manage some RSV Pable event and you're managing it with other people, adding guest lists like manually you're trying to come up with a guest 
18:41 list and then send them, Hey, are you coming or not? Reminders, these should have helped you. Thanks so much for watching.
18:48 Hopefully this was helpful.