I'm relatively new to coding, especially in Javascript. I'm working on a google form that links to a google sheet and I want a conditional notification that when they answer a certain response it will notify that departments manager via email.
Essentially, I need my script to search for the value of a cell that contains the email to send.
I'm using a VLOOKUP chart to automatically input the email of that department based on the the department question they answer.
Additionally I need a way to trigger this function to run when a new response is entered. Making sure that only an email is sent for the new response and not any of the older ones.
My email will have the message line and subject using this code below:
var recipient = emailAddress
var message = ''
var subject = 'Department Alert';
MailApp.sendEmail(emailAddress, subject, message);
Any thoughts on how to achieve this?
This code will do the work for you.
Install the trigger onFOrmSubmit. Then it should be good to go.
function onFormSubmit(e) {
const form = FormApp.getActiveForm();
const items = form.getItems();
const formResponses = e.response;
const arr = [];
let receiptant_name;
for (i in items) {
let each_response = formResponses.getResponseForItem(items[i]).getResponse();
// this column has the supervisor name, you can change column title into yours.
if (items[i].getTitle() == 'Supervisor Name') {
// return a list of name
receiptant_name = each_response;
}
if (typeof(each_response) == "object") {
each_response ='<div>' + each_response.map(r => { return '<div>'+r+'</div>';}).join('') + '</div>';
}
const title = items[i].getTitle();
arr.push([title, each_response]);
}
const emailbody = return_emailbody(arr);
const receiptant_email = return_supv_email(receiptant_name);
sendEmail(receiptant_email, emailbody);
}
//this function to map form responses into html table.
function return_emailbody(responsearray) {
let mail_array = [];
responsearray.forEach(r => {
if (r[1] === null || r[1] === '') {
r[1] = 'N/A';
}
mail_array.push('<tr><td>' + r[1] + '</td></tr>')
});
const competed_template = htmltemplate(mail_array.join(''));
return competed_template;
}
//sheet id contains the supervisor email.Default Sheet1, you can change to your sheet name.
function return_supv_email(sheetid,supv_name) {
//receive name list
const values = sheet_data_return(sheetid, 'Sheet1');
let email_list = [];
for(i in supv_name){
values.filter(record => {
if (record[1].replace(/\s/g, '').includes(supv_name[i].replace(/\s/g, ''))) {
email_list.push (record[2]);
}
});
}
return email_list.toString();
}
function sheet_data_return(id, sheet_Name) {
return SpreadsheetApp.openById(id).getSheetByName(sheet_Name).getDataRange().getDisplayValues();
}
function sendEmail(recipient, emailbody) {
GmailApp.sendEmail(
recipient,
'FORM GOT A NEW RESPONSE',
emailbody,
{
htmlBody: emailbody,
name: 'GOOGLE FORM RESPONSE',
});
}
//you can edit your this HTML template to make your email look better.
function htmltemplate(emailbody) {
let template = '<!DOCTYPE html> <html> <head> <base target="_top"> </head><body><table>{body}</table> </body> </html>';
template = template.replace('{body}', emailbody);
return template;
}