There are times that automated email can be very helpful. The concept I'm going with here is aligning to a form response. When we ask folks to sign up for something we sometimes need them to have materials or may want to customize the response. Yes, in Forms you can customize a response to the form but what if you want to get fancy? Say people are filling out a form for an audition (I was in the entertainment industry so I'm using an example personal to me) they are going to need to audition materials and information for the particular role they are auditioning for. Here's where a script can help you to select who receives what.
Let's go back to our form we were collecting emails on. We'll keep this simple to begin then I'll give you a case where it can be expanded.
Open the Scripts Tools>Script editor. You should have:
function formResponse(e){
Logger.log(e.values[0]);
}
Try changing it to:
function formResponse(e){
Logger.log(e.values);
}
Submit a form response again.
The 0 index of the form response is the timestamp. If we modify our log to get the value of e, Logger.log(e); we will get all the information returned. Logger.log(e.values); will return all the values of the form response.
We want to identity the index of the email address. If you are collecting emails automatically if will likely be index 1 or e.values[1]. If you are collecting emails in your form it will be listed in the form at an index relative to all other information you may be collecting. If you have turned on the automatic collecting of email and turned it back off you may have also created an index with no information or null.
Your incoming data may look like this [9/16/2018 11:03:52, clay@claycodes.org, Clay Smith, Danny] or [9/16/2018 11:05:21, , Clay, Danny, clay@claycodes.org] or something else.
Each index is separated by a comma. If we want to get the values of a particular index we can use the index number. There are other methods but this is a good concept to start with.
function formResponse(e){
Logger.log(e.values);
}
Our function returns all the values to the log. It's now time to start doing something with the values. We want to send an email when some one submits the form. Let's set a variable to be the email address. We can do this with:
var email = e.values[4];
The 4 index is from the image sample above. Your index may be different.
Next let's identify a file URL we want to include in our email. To make it easy here's a link to Bloom's Taxonomy verbs. https://drive.google.com/file/d/0BxUJf-bQ7_PzNnFKMXdOUjJGZGM/view?usp=sharing
We can assign this link to a variable by typing: var fileurl = 'https://drive.google.com/file/d/0BxUJf-bQ7_PzNnFKMXdOUjJGZGM/view?usp=sharing';
I've made up the variable name fileurl just like I did email. I'm using the apostrophe, ', mark to indicate the data is a string or typed letters and not something to be calculated. The apostrophe encloses the information on each end.
We'll make a variable for the body and include some characters which will be escaped. Meaning they are normally reserved for programming but we will tell the program not to read them as part of the programing.
var body = 'Thanks for filling out the form here\'s a document to read before we begin. \n ' + fileurl;
The \ is used to escape characters. The apostrophe typically represents the end of a string of characters, in this case we're using it grammatically. So we escape it with \'. Looking at the other escape character we see \n representing a new line. The + symbol is used to put things together. just like we would add 2 and 2 to get 4 a string gets added to make a longer string. The fileurl is a variable so it doesn't go in apostrophes or the system will think it's the word fileurl.
Now let's put these elements together as an email.
var mail = GmailApp.sendEmail(email, 'Subject goes here' , body);
The GmailApp loads a library of possible functions. We are using the pre-made sendMail which takes in three inputs, an email address, the subject of the email, and the body of the email. We could have used a variable for the subject but here I've entered it as a string to build on the idea that we don't have to always create variables. we could also replace the variable email with e.values[4] and it will still function.
Putting it all together looks like:
function formResponse(e){
Logger.log(e.values);
var email = e.values[4];
var fileurl = 'https://drive.google.com/file/d/0BxUJf-bQ7_PzNnFKMXdOUjJGZGM/view?usp=sharing';
var body = 'Thanks for filling out the form here\'s a document to read before we begin. \n ' + fileurl;
var mail = GmailApp.sendEmail(email, 'Subject goes here' , body);
}
Google handles a lot of security for you. To be sure all the security is engaged for our function let's try running it. It will give us an error but before it does it will go through the permission checks and allow the mail feature to work. Click Run>Run function>formResponse
Accept the permissions and we'll be ready to submit through the Form.
Go ahead and submit a new form with your email address. Check your email. You should get the response from the script you wrote. Congratulations!!!
So here's a use case which may help. Let's use the audition example. We'll add a dropdown of role with two selections. Danny or Sandy. We'll create an if statement to change the fileurl based on the role. We could also change the text of the body and subject by using the conditional statement but we'll keep it simple.
function formResponse(e){
Logger.log(e.values);
var email = e.values[4];
var role = e.values[3];
if(role == 'Danny'){
var fileurl = 'https://drive.google.com/file/d/1-hGi-F7EAi4pBXoDoZHzLQ2qasJ4ie2F/view?usp=sharing';
}else if(role=='Sandy'){
var fileurl = 'https://drive.google.com/file/d/1t7bVocmj9QaHApIIlc91l3RARg4gXoKa/view?usp=sharing
}
var body = 'Thanks for filling out the form here\'s a document to read before we begin. \n ' + fileurl;
var mail = GmailApp.sendEmail(email, 'Subject goes here' , body);
}
