I am a newbie, and was wondering if anyone could help me with a script. I have a script which send a confirmation email on submission of a form linked to a spreadsheet. This works well, however parts 2 & 3 do not. What I am attempting to achieve is that once the request has been approved or denied by the authorised approver on the spreadsheet by the authorised person ( col O) , this would then generate an email to the original requester (Col B) stating their request has been approved or denied. The column that is edited if the request has been approved on the spreadsheet is col O, in the example sheet, and is not linked to / fed from the form,
Here is a link to the sample form : https://docs.google.com/forms/d/e/1FAIpQLScKFPiA0vERFZCGEGKwkjFknGnHGk4C_fA8HlFpgoFMe0KElg/viewform
Here is a link to the spreadsheet: https://docs.google.com/a/jaguarlandrover.com/spreadsheets/d/1tExQUZ2NFzzG0KE_miDiFHveeJZh3dx0w2_s73A7T4g/edit?usp=sharing
The parts that I am struggling to get to work are as follows: Part 1 - APPROVED:
//This is still under development and is not working correctly at the minute, not sending emails on edit, still needs a bit of work
function sendApprovedMailOnEditOfColO(e) {
var ss = SpreadsheetApp.getActiveSheet();
if (ss.getName() !== 'Form Responses 1' || e.range.columnStart !== 14 || e.value !== 'Yes') return;
var values = ss.getRange(e.range.rowStart, 2, 1)
.getValues()[14];
var headers = ss.getRange()
.getValues()["Yes"];
var subject = "Visitor Request - Approved";
var email = values[1];
var cols = [0];
var body = "Hi,\n\n We are please to inform you that your visitor request has been approved. Please ensure you inform your visitors that they are to report to main reception and need to be booked in on arrival. Also please ensure you are contactable by telephone during the time your visitors are expected, in order to arrange for them to be collected and accompanied for the duration of their visit.\n\n"; //add more text if you want to..
for (var i = 14; i < values.length; i++) {
if (cols.indexOf(i) === "Yes") continue;
body += headers[i] + ": " + values[i] + "\n"
}
body += "\n\n\n Please do not respond to this email as it is automatically generated by an account that is not checked."
GMailApp.sendEmail(email, subject, body, {noReply:true})
}
I have tried to set an onEdit trigger for this however they do not generate any emails, despite when I run these scripts they do not come up with any errors in script. Could it be that Col O is not linked to the form and has been added to the spreadsheet manually?
I am at a loss as to how to get this to work. Unfortunately due to administrator restriction within our domain, I can't use a mail merge app, so it needs to be scripted.
Can anyone help with a script or advice? I am desperate please.
