This is a sendEmail function in apps script for an on form submit trigger. This sends an email to whoever is selected in the form. I’m new to this so it’s probably clunky — but it works. I just can’t figure out how to include a specific cell’s name ${cell} from the new submission in the email body. I know it’s something simple. Help!
Below is what I have.
I’m expecting the email body to read “…Please check the box in cell D5” D5 being the correct cell within in the new row from the form submission that triggered the email.
It’s returning the error “Exception: Cannot convert ‘A5’ to int.” in reference to the body of the email. I know this isn’t referring to the “+ row” piece at the end because this all works without the “${cell}” piece.
function sendEmail(e) {
//info from Form Submission
Logger.log("%s", JSON.stringify(e));
var named_values = e.namedValues
var values = e.values
var supervisor_name = named_values["Supervisor Name"].toString();
var url = "URL";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
let range = e.range;
var row = sheet.getActiveCell().getA1Notation();
var cell = sheet.getRange(row,4)
var supervisor_email, body;
console.log(`supervisor_name: ${supervisor_name}`)
switch (supervisor_name){
case "NAME":
supervisor_email = "EMAIL";
body = `${supervisor_name},n nA request has been submitted that needs your approval. Please check the box in cell ${cell}.`+ url + '&range=' + row
break;
console.log(`supervisor_email outside of switch: ${supervisor_email}`)
MailApp.sendEmail(supervisor_email, "Approval Requested", body)
}
user24713415 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.