so I currently have a support app for my team that kicks out their support requests to a linked google sheet with one tab for each support form, and I've added an "In Progress" and "Complete" tab and linked all sheets to move the entire request between tabs based on data validation (see script and sheet below).
I'm trying to script out auto-email updates to the email address in the row being moved once the request (row) is moved to the In Progress tab and Complete tab that say something like "your ____ request is In Progress" or "...Complete" respectively.
I've tried out a few different scripts I've found online, but have either been having trouble sending updates with the MailApp
and onEditTrigger
functions, since the part of my script that moves requests between tabs moves and deletes rows.
Here's what I'm using to move request rows to and from.
function onEdit(e) {
const src = e.source.getActiveSheet();
const r = e.range;
if (r.columnStart != 1 || r.rowStart == 1 || e.value == src.getName()) return;
const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e.value);
src.getRange(r.rowStart,1,1,20).moveTo(dest.getRange(dest.getLastRow()+1,1,1,20));
src.deleteRow(r.rowStart);
}
I've been following along one forum that's been suggesting scripting the auto-emails with the below code, but on moving and "approving" (as the forum suggests), I can only get the Admin email update sent, but not the "candidate email" to send. When I try to run the triggerOnEdit
for the email send once verified, I get
TypeError: Cannot read property 'range' of undefined,
and errors referring to checkStatusIsApproved
(line 37), sendEmailOnApproval
(line 55) and triggerOnEdit
(line 16)
I've also attached the Google Sheets test file I've been using to match format.
Any help is well appreciated!!
var admin_email='matt@velourmd.com';
function triggerOnEdit(e)
{
sendEmailOnApproval(e);
}
function showMessageOnApproval(e)
{
var edited_row = checkStatusIsApproved(e);
if(edited_row > 0)
{
SpreadsheetApp.getUi().alert("Row # "+edited_row+" approved!");
}
}
function showMessageOnUpdate(e)
{
var range = e.range;
SpreadsheetApp.getUi().alert("range updated " + range.getA1Notation());
}
function checkStatusIsApproved(e)
{
var range = e.range;
if(range.getColumn() <= 1 &&
range.getLastColumn() >=1 )
{
var edited_row = range.getRow();
var status = SpreadsheetApp.getActiveSheet().getRange(edited_row,1).getValue();
if(status == 'approved')
{
return edited_row;
}
}
return 0;
}
function sendEmailOnApproval(e)
{
var approved_row = checkStatusIsApproved(e);
if(approved_row <= 0)
{
return;
}
sendEmailByRow(approved_row);
}
function sendEmailByRow(row)
{
var values = SpreadsheetApp.getActiveSheet().getRange(row, 1, row, 1).getValues();
var row_values = values[0];
var mail = composeApprovedEmail(row_values);
MailApp.sendEmail(admin_email,mail.subject,mail.message);
var candidate_email = composeCandidateEmail(row_values);
MailApp.sendEmail(candidate_email.email,candidate_email.subject,candidate_email.message);
}
function composeApprovedEmail(row_values)
{
var first_name = row_values[3];
var request_type = row_values[7];
var email = row_values[4];
var message = "The following applicant is in progress(1): "+request_type+
" email "+email;
var subject = +first_name+ "Your support" +request_type+ "request is currently in progress "
return({message:message,subject:subject});
}
function composeCandidateEmail(row_values)
{
var first_name = row_values[3];
var request_type = row_values[7];
var email = row_values[4];
var message = "The following applicant is in progress(2): "+request_type+
" email "+email;
var subject = +first_name+ "Your support" +request_type+ "request is currently in progress "
var message = "Hello "+first_name+"\n"+
"Your support ticket is underway and in progress.\n Please follow the following instructions to proceed with the process.\n";
return({message:message,subject:subject, email:email });
}