I am trying to figure out, how do the following in Google Spreadsheet.
- Send email when a value changes in a cell. (Value = Completed).
- Compile that rows data into the email. See format in code below.
- Prompt user for confirmation of info.
- If YES, send email to active user as well as the preset users in the code below.
- This is optional: Update sheet in row on column (P) 16 with Email Sent + timestamp.
Hi Serge,
Try to implement the code you provided, but I could not make heads or tails on what to modify to fit what I needed done.
Let me explain it again with below workflow.
Send an email when the value changes for column K.
Partial Sample code to watch column K
var sheetNameToWatch = "Active Discs";
var columnNumberToWatch = 14; // column A = 1, B = 2, etc.
var valueToWatch1 = "Completed";
var valueToWatch2 = "in progress";
try{
var ss = e.source;
var sheet = ss.getActiveSheet();
var range = e.range;
if (sheet.getName() == sheetNameToWatch && range.columnStart ==
columnNumberToWatch && e.value == valueToWatch)
var confirm = Browser.msgBox
('Email will be sent Team X. Do you want to sent this email?', Browser.Buttons.YES_NO);
if(confirm!='yes'){return};
// if user click NO then exit the function, else move data
The email will contain the specified values of that specific row. Ex. Values in columns A, B, C, D, E, F, G, H, I, J.
//Email to be sent if **Inprogess** value is a match:
Var sendEmailTeamA(){
var ProjectName = e.values[0];
var ProjectId = e.values[1];
var ProjectManager = e.values[3];
var Sales = e.values[4];
var Client = e.values[5];
var DiscType = e.values[6];
var DVDFlash = e.values[7];
var Phase = e.values[8];
var Encryption = e.values[9];
var Qty = e.values[11];
var DueDate = e.values[12];
var SpecialInstructions = e.values[13];
var emailAddress = '';
var subject = "DVD Request - " + ProjectName + " " + ProjectId;
var emailBody = "Hi Venue Colombo Team,"
"\n\nThe following data room(s) will need a disc creation. Please begin bulk save data room and create ISO to upload to the FTP site: " +
"\nProject Name: " + ProjectName +
"\nProject ID: " + ProjectId +
"\nProject Manager: " + ProjectManager +
"\nPhase: " + Phase +
"\nDisc Type: " + DiscType +
"\nEncryption: " + Encryption +
"\nQuantity: " + Qty +
"\nClient Due Date: " + DueDate +
"\nSpecialInstructions: " + SpecialInstructions;
var htmlBody = "Thank you for your <b>Club Ambassador Program</b> report submitted on <i>" + timestamp +
"</i><br/> <br/>Person Show Submitted this email: " +
"<br/><font color=\"red\">Your Name:</font> " + activeSessionuser +
"<br/>Your Email: " + toAddress;
var optAdvancedArgs = {name: "Club Ambassador Program", htmlBody: htmlBody};
MailApp.sendEmail(emailAddress, subject, emailBody, optAdvancedArgs);
}
//Email to be sent if **“Completed”** value is a match:
Var sendEmailTeamB() {
var ProjectName = e.values[0];
var ProjectId = e.values[1];
var ProjectManager = e.values[3];
var Sales = e.values[4];
var Client = e.values[5];
var DiscType = e.values[6];
var DVDFlash = e.values[7];
var Phase = e.values[8];
var Encryption = e.values[9];
var Qty = e.values[11];
var DueDate = e.values[12];
var SpecialInstructions = e.values[13];
var emailAddress = '';
var subject = "DVD Request - " + ProjectName + " " + ProjectId;
var emailBody = "Hi Venue Colombo Team,"
"\n\nThe following data room(s) will need a disc creation. Please begin bulk save data room and create ISO to upload to the FTP site: " +
"\nProject Name: " + ProjectName +
"\nProject ID: " + ProjectId +
"\nProject Manager: " + ProjectManager +
"\nPhase: " + Phase +
"\nDisc Type: " + DiscType +
"\nEncryption: " + Encryption +
"\nQuantity: " + Qty +
"\nClient Due Date: " + DueDate +
"\nSpecialInstructions: " + SpecialInstructions;
var htmlBody = "Thank you for your <b>Club Ambassador Program</b> report submitted on <i>" + timestamp +
"</i><br/> <br/>Person Show Submitted this email: " +
"<br/><font color=\"red\">Your Name:</font> " + activeSessionuser +
"<br/>Your Email: " + toAddress;
var optAdvancedArgs = {name: "Club Ambassador Program", htmlBody: htmlBody};
MailApp.sendEmail(emailAddress, subject, emailBody, optAdvancedArgs);
}
This workflow will apply to columns K, L, M, N, O. Email will be sent to the preset email addresses in the code. I hope this explains it a little bit better. I thank you again for your time and help.
