So, I'm trying to use the script to identify changes in Row 60
and if it identifies certain changes, it will automatically email the client to update him/her on the status of their order.
However, line 30 of the Code is throwing up an error that I can't figure out any help would be appreciated:
function onEdit(e) {
// Your sheet params
var sheetName = "Form responses 1";
var dateModifiedColumnIndex = 64;
var dateModifiedColumnLetter = 'BL';
var range = e.range; // range just edited
var sheet = range.getSheet();
if (sheet.getName() !== sheetName) {
return;
}
// If the column isn't our modified date column
if (range.getColumn() != dateModifiedColumnIndex) {
var row = range.getRow();
var time = new Date();
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy, hh:mm:ss");
var dateModifiedRange = sheet.getRange(dateModifiedColumnLetter + row.toString());
dateModifiedRange.setValue(time);
};
}
function triggerOnEdit(e)
{
sendEmail(e);
}
function checkStatus(e)
{
var range = e.range; //This is the line that throws up the error. //
if (range.getColumn() <=60 && range.getColumn() >=60)
{
var edited_row = range.getRow();
var status = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form responses 1").getRange(edited_row,60).getValue();
if (status == "PENDING" || status == "APPROVED" || status == "REJECTED")
{
return edited_row;
}
}
return 0;
}
function sendEmail(e)
{
var approved_row = checkStatus(e);
if (approved_row <=0)
{
return;
}
sendEmailByRow(approved_row);
}
function sendEmailByRow(row)
{
var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form responses 1").getRange(row, 1, row, 63).getValues();
var row_values = values[0];
var candidate_email = composeCandidateEmail(row_values);
MailApp.sendEmail(candidate_email.email,candidate_email.subject,candidate_email.message);
}
function composeCandidateEmail(row_values)
{
var name = row_values [4];
var email = row_values [5];
var comment = row_values [63];
var status = row_values [60];
var link = row_values [61];
var subject = "Your Purchase Order is " +status;
var message = "Hello " +name+ ",\n \n Your Purchase Order is " +status+
". \n \n The following comments were made: /n" +comment+ "\n \n You can find your original Purchase Order here: /n /n" +link;
return({message:message, subject:subject, email:email});
}
Image of spreadsheet, pay attention to the Column headers as some columns are hidden:
Thanks for any help on this B
Try this:
You need to create and installable trigger for it. Of course, you can not test it by running it from the code editor like any other function because most other functions don't require event objects. If you wish to provide an image of the spreadsheet and a csv of the data (which can be posted into a code section of your question), then I'd be willing to try to get it running for you. Note: I don't wish to have a link to a spreadsheet because I no longer following links to spreadsheets. If you must know why then read my profile.