I have written this google app script.
Purpose: Every time I edit "Location Update" script should send an email to "Broker Email".
Here is Link to spread sheet:
function onEdit(e) {
var activateSheet = SpreadsheetApp.getActiveSpreadsheet();
SpreadsheetApp.setActiveSheet(activateSheet.getSheetByName('Responses'));
var sheet = SpreadsheetApp.getActiveSheet();
var range = e.range;
var row = e.range.getRowIndex();//Gets the row the change was made to.
var timestamp = sheet.getRange(row, 1).getValues(); //Gets the email
var load = sheet.getRange(row, 2).getValues(); //Gets the email
var email = sheet.getRange(row, 3).getValues(); //Gets the email
var location = sheet.getRange(row, 4).getValues(); //Gets the email
var template = HtmlService.createTemplateFromFile("template");
template.load = load;
template.location =location;
MailApp.sendEmail(email,"Hi","this is my email",{htmlBody: template.evaluate().getContent()});
}
<html>
<h1><b>This is a Check-In call for load number: <?= load ?></b></h1>
<p>
<?= location ?>
</p>
<p>
Please do not reply this email, we don't read these emails. If you have any questions/concers please <a href="google.com">Click Here</a>. We will get back to as soon as we can.
</p>
</html>
The error:
range not defined
Means that the getRange() method failed. If a variable is being used to assign the range to, then that variable would have an assigned value of undefined. If the range is not being assigned to a variable, but used in a chain, then it's also undefined.
If you are running the code from inside the script editor, you will get a range error. In the case of a simple onEdit() trigger, you can only test the code by actually editing a cell. The e event variable will not have anything assigned to it unless you actually edit a cell. That e variable will be assigned an event object when the event (a cell edit) happens.
The function name onEdit() is a reserved function name. In order for the onEdit() function to run when a cell is edited, all you need to do is create a function of that name, as opposed to going through a set-up process to assign an event to a function name. onEdit() is a simple trigger. And "installable" trigger needs to be set-up.
I ran your code, and don't get any range errors, if I trigger it by editing a cell.
I am getting an error stating that I don't have permission to send an email. Which is because the trigger is a "simple" trigger. You may need to create an installable trigger in order to have permission to send the email.
Try this script (also posted on google docs help forum)
function sendMail(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() !== 'Responses' || e.range.columnStart !== 6 || e.range.rowStart < 2) return;
var values = e.range.offset(0, -5, 1, 6)
.getValues()[0];
var htmlBody = '<body>' + 'Hi<br>'
+ '<h1><b>This is a Check-In call for load number:' + values[3] + '</b></h1>'
+ '<p>' + values[5] + '<\p>'
+ '<p>' + 'Please do not reply this email, we don\'t read these emails. If you have any questions/concers please <a href="google.com">Click Here</a>.'
+ 'We will get back to as soon as we can.</p>'
+ '<\body>'
MailApp.sendEmail(values[4], 'subject', "", {
htmlBody: htmlBody
});
}
Set an installable onEdit trigger and see if that works ?
