Automate Emails by Google Form Responses Using Google Apps Script Email Certain Department Based on
Learn how to automate email notifications to different departments based on Google Form responses using Google Apps Script. This tutorial walks you through creating a database of emails, setting up a Google Form, and linking it with an Apps Script to send emails upon form submission.
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, they may want to contact, maybe we're using this as an internal messaging tool, anyways, we want to build a database of emails, we want It's for each department. 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. And then we're going to create the on form submit trigger , which is the key to tie the Apps Script to the form. 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. So we're going to create an emails Tab, we need, uh, let's say a name here like Audrey and this is Audrey at gmail. com. 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. 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 and they're in support. Okay, so we have department here, email here, name here. Let's create a form that has these departments for contact tools, Email Certain Department Based on Google Form Entries 2 Forms 1 Sheet
create a new form and this form we could allow the public to fill out or we can have it only internally. It 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. And then we need department. And here we're going to use a multiple choice. We have to make sure these are the same 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. So these need to be exact. Uh, there is sort of a way you can make inexact matches, but it's going Email Certain Department Based on Google Form Entries 2 Forms 1 Sheet
to be much easier with exact matches. 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. When we fill this out, the form responses are going to come here, but we want to email when that form is responded to. So we need to go to extensions, Apps Script . By the way, we are here on this third section already. So we are going to call this function send email. And we're going to have an event here. We can just use the E. 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.
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 E dot named. 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. 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. So we're going to say for, we actually need, let's say, variable departments equals spreadsheetapp . Email Certain Department Based on Google Form Entries Quick Follow Up Email
getactive, spreadsheet , get sheet by name, emails. Dot get range and that range is going to be C colon C get values . So that's a list of values . We also have variable emails equals this exact same code, except it's a different range . It's the B column. So we find our department here and we'll get our emails emailed there. So for. I equals zero. We'll start at zero. I is less than departments dot length. I plus plus. We're going to iterate for it. And we're going to say if departments I is equal to department what we got here Email Certain Department Based on Google Form Entries Email Yourself a Cell from a Google Sheet, Every Day Get Values from Sheet for Custom Function in Apps Script
in our named values and if that's true, we're going to get variable email equals emails this same I but plus one here. Sorry, actually just the I, because zero is the first item in the departments, and zero would be the first item in the emails list. So our variable is there, and else, if we can't find it, we'll just break, uh, the function and enter. So we'll return. I just do break. Okay, so we have figured out our email. We now need to send an email so we can use gmail app dot send email. We need three things. We need the email that we're getting up here. We need the subject, you have a new request, and we need a body. Email Certain Department Based on Google Form Entries Email Yourself a Cell from a Google Sheet, Every Day Email When Cell Changes
So, in this body, what I want to do is check this sheet. So, we're not going to actually send them any special message, we just want to say, hey, you have a new message over here, we'll get the sheet URL, variable sheet, URL equals spreadsheet app dot get active spreadsheet , get URL, Yeah, get URL, and we'll put it here. Sheet URL. So in that email, we will get just, Hey, check this sheet for new messages. For a new message for your department. So there we go. We have all the parts of this. Now, we need to make sure that this E is actually coming from the form. How we do that is we need to connect this Apps Script to the on form, on form submit . Email Certain Department Based on Google Form Entries Simply Email Yourself From a Google Sheet Automated Project Management in Google Sheets Email When Cell Changes
So again, we've just created the Apps Script to email departments. Now, let's create the form submit trigger . Over in an Apps Script , go over to triggers. Add trigger , choose which function to run. If you have multiple functions , you'll have to choose this out of the dropdown menu, but right now we have one function. Send email event sources from spreadsheet and event type is on form. Submit, save. That is the whole trigger once we've authorized it and everything it is in our triggers. If you need to edit this for any reason, you can come back to triggers, edit it with this. edit trigger button, the little pencil icon, or even delete it by clicking on the three icons over here and delete trigger. But let's test this out. So to test this out, I'm going to go to my emails. I'm going to actually use my own email address, Andrew plus Audrey. Email Certain Department Based on Google Form Entries How to Send Google Forms Entries Automatically with Apps Script Automatic Calculations Google Form Automation Basics with Google Sheets and Apps Script
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. So we'll put this, we'll actually have real, email address is here, but have this thing that says who it's to. Let's go check it out, 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, this is an array. Of one single entity. 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. 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 Email Certain Department Based on Google Form Entries Quick Follow Up Email
we don't have a combination if we don't actually have a match here. So let's contact again one more time. Click IT. Let's message IT and see if that works. Just checking my mail and I got, you have a new request. And it's to Audrey here, 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. 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. Let's look at our thing finance and Charlie. Yes, so we are working. It's totally working now. Awesome This is the final code you need to do that. We don't need to break here else just Looking for those matches Email Certain Department Based on Google Form Entries Quick Follow Up Email
and nothing else and sending an email once we get that match . Cool. Thanks for watching this video. Thanks for going through all these departments. And we have created a department email database. We created the Google form that has that specific text department. We created the Apps Script to match those. And created the on form submit trigger to actually send the Apps Script email when the form is submitted. Cool. That's everything. Hope you enjoyed this. Email Certain Department Based on Google Form Entries How to Send Google Forms Entries Automatically with Apps Script Automate Form Response to Confirmation Email Google Form Automation Basics with Google Sheets and Apps Script