00:00 In this video we want to create an email that is sent when a Google Form is filled out. But we want to send an email to different departments based on what is in the form, which department someone has selected.
00:16 They may want to contact, maybe we're using this as an internal messaging tool. Anyways, we want to build a, database of emails that is for each department.
00:27 We're going to create a Google Form. We're going to create the Apps Script to email departments. This is not as hard as you think, and you can probably just copy my script that I do here.
00:35 And then we're going to create the On Form Submit trigger, which is the key to tie the Apps Script to the form.
00:43 And that is literally just a few clicks that I want to show you. So, let's get into this. Let's create the database first.
00:51 So, we're going to create an Emails tab. We need, ah, let's say a name here like Audrey. And this is Audrey at gmail.com.
01:06 We'll have email and department. So, we can have the department here. Let's say She's IT We have Bob, Bob at gmail.com.
01:17 And he's in HR. And there's Charlie, Charlie at gmail.com. And he's in Finance. And we have Dana, Dana at gmail.com.
01:31 And they're in Support. Okay, so, we have the department here. Email here, name here. Let's create a form that has these departments for contact.
01:44 Tools, create a new form. And this form, we could allow the public to fill out or we can have it only internally.
01:53 Doesn't really matter. So, we'll say contact department. We need what are you asking about? And we'll make that a paragraph, yep.
02:06 And then we need department. And here we're going to use a multiple choice. We have to make sure these are the same.
02:17 IT, ITHR. What was the other ones? Finance and support. And this is going to be key. This is the line or string that ties to these departments here to get these emails.
02:34 So these need to be exact. Uh, there is sort of a way you can make inexact matches, but it's going to be much easier with exact matches.
02:44 So, there we go. We have those things. We can publish this. Let's publish this to everyone. So we can actually fill it out as an example, publish, and we want to make, get a copy here open so that we can fill this out as needed.
03:04 But when we fill this out, the form responses are going to come here, but we want to email when that form is responded to.
03:12 It's weird. We need to go to extensions app script, by the way, we are here on this third section already.
03:26 So we're going to call this function, send email, and we're going to have an event here we can just use the E.
03:39 This E or event, it has data, whenever we're going to use the on form submit trigger, it won't happen right away, like we can't use it from here.
03:49 We're going to get, gather some data from that form. So we can get, let's see here, we use department as the name, variable department equals, let's say, variables E.named.
04:09 We're going to need to address this with a square bracket. We're going to use department. So this department name, this text here, is the name here.
04:24 So We could. Logger.log this department, just in case we want to see that in the logs. And we want to say, we're going to need to go to this emails tab, get these departments, and then find out what's in the B column for the email.
04:45 So we're going to say for, we actually need, let's say, variable, departments equals, spreadsheetapp.getactive, spreadsheet, get sheet by name, emails, dot get range, and that range is what is going to be c, colon c, get values.
05:19 So that's a list of values. We also have variable emails equals this exact same code, except it's a different range.
05:28 It's the b column. Okay, so we find our department here. And we'll get our emails, email there.
05:40 So for i equals 0, we'll start at 0, i is less than departments.length, i++, we're going to iterate for it, and we're going to say if departments i department, what we got here in our named values, and if that's true, we're going to get variable email equals emails, this same i, but plus 1, sorry, actually
06:17 just the i, because 0 is the first item in the departments, and 0 would be the first item in the emails list.
06:25 So, our variable is there, and else, if we can't find it, we'll just break, uh, the function and enter, actually, we'll return, let's do break, okay, so, we have figured out our email We'll see you will now need to send an email, so we can use gmailapp.sendemail, we need three things, we need the email
06:55 that we're getting up here, we need the subject, you have a new request, and we need a body, so, in this body, what I want to do is check this sheet, so, we're We're not going to actually send them any special message.
07:12 We just want to say, hey, you have a new message over here, we'll get the sheet URL, variable sheet, URL, equals spreadsheetapp.getactivespreadsheet, getURL, yeah, getURL, and we'll put it here, sheetURL, so, in that email, we will get it, just, hey, check the sheet for new messages, for a new message
07:42 for your department. So there we go. We have all of the parts of this. Now, we need to make sure that this E is actually coming from the form.
07:56 How we do that is we need to connect this Apps Script to the on submit. So again, we've just created the Apps Script to email departments.
08:05 Now, let's create the form submit trigger. Over in an Apps Script, go over to triggers, add trigger, choose which function to run.
08:17 If you have multiple functions, you'll have to choose this out of the drop down menu, but right now we have one function, send email.
08:23 Event source is from spreadsheet. And event type is on form. Submit, save. That is the whole trigger. it is in our triggers.
08:51 If you need to edit this for any reason, you can come back to triggers, edit it with this edit trigger button, a little pencil icon, or even delete it by clicking on the three icons over here and delete trigger.
09:04 But let's test this out. So to test this out, I'm going to go to my emails. I'm gonna actually use my own email address.
09:13 Andrew plus Audrey. So we're going to use this sort of trick with the plus to see if it's actually sending to the correct, uh, email address.
09:28 So we'll put this, we'll actually have real email addresses here, but have this thing that says who it's to. Let's go check it out.
09:38 Let's go contact department, something, IT, click submit. It failed because I think I missed one thing here, which is we need to put another curly bracket zero because we're a, this is an array of one single entity.
09:56 So we have to add that zero to get the actual value. I think that's what we need to do. Let's try that again.
10:02 Let's submit another response. One more time. Let's contact HR, submit. I realize what happened now is this else is actually breaking every time we don't have a combination if we don't actually have a match here.
10:20 So let's contact again. One more time. Click IT. Let's message IT and see if that worked. Just checking my mail and I got, you have a new request and it's to Audrey here.
10:35 So let's double check that that is correct. It's Audrey at IT. Yes, we have it. So it's sending the email now.
10:41 Let's go and submit another response. This one for finance. Let's select finance, click submit. And we have an email to Charlie, it says.
11:00 Let's look at our, I think, finance and Charlie. Yes. So we are working. It's totally working now. Awesome. This is the final code, you need to do that.
11:10 We don't need to break here else, just break. Looking for those matches and nothing else and sending an email once we get that match.
11:19 Cool. Thanks for watching this video. Thanks for going through all these departments. And we have created a department email database.
11:30 We created the Google form that has that specific text department. We created the app script to match those and created the on form submit trigger to actually Send the app script email when the form is submitted.
11:43 Cool. That's everything. Hope you enjoyed this.