Send Email when value changes in Google Spreadshee

问题:

I am trying to figure out, how do the following in Google Spreadsheet.

1. Send email when a value changes in a cell. (Value = Completed).
2. Compile that rows data into the email. See format in code below.
3. Prompt user for confirmation of info.
4. If YES, send email to active user as well as the preset users in the code below.
5. 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/>&nbsp;<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/>&nbsp;<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.

回答1:

I can get you started:

1. Add a trigger in Resources>Current project's triggers that triggers sendEmail() "on edit".
2. ...

回答2:

I just wrote a script that does that kind of thing but I wanted it to keep an eye on all the changes in the sheet but send a message only once every hour to avoid spamming my mailBox.

The script has 2 functions, one that collects the changes and stores them in text format and a second that sends email if any change occurred in the last hour.

The first function is called grabData and must be triggered by an onEdit installable trigger and goes like this :

function grabData(e){
  Logger.log(JSON.stringify(e));
  var cell = e.range.getA1Notation();
  var user = e.user.email;
  var time = Utilities.formatDate(new Date(),Session.getScriptTimeZone(),'dd-MM-yyyy')+' à '+Utilities.formatDate(new Date(),Session.getScriptTimeZone(),'hh:mm');;
  if(user!='[e-mail]'&&cell!='A1'){ 
  var dataUser1 = PropertiesService.getScriptProperties().getProperty('contentUser1');
  if(dataUser1==null){dataUser1=''};
  dataUser1+='\nCellule '+cell+' modifiée le '+time+' par '+user+' (nouvelle valeur = '+e.range.getValue()+')';
  PropertiesService.getScriptProperties().setProperty('contentUser1',dataUser1);
  }
  if(user!='[e-mail]'&&cell!='A1'){
  var dataUser2 = PropertiesService.getScriptProperties().getProperty('contentUser2');
  if(dataUser2==null){dataUser2=''};
  dataUser2+='\nCellule '+cell+' modifiée le '+time+' par '+user+' (nouvelle valeur = '+e.range.getValue()+')';
  PropertiesService.getScriptProperties().setProperty('contentUser2',dataUser2);
}
}

The other function has a timer trigger, I set it to fire every hour but you can change it to your best fit.

function sendReport(){
  var dataUser1 = PropertiesService.getScriptProperties().getProperty('contentUser1');
  var dataUser2 = PropertiesService.getScriptProperties().getProperty('contentUser2');
  if(dataUser1.length>1){
    MailApp.sendEmail('[e-mail]', 'Modification dans le planning FFE', dataUser1);
    PropertiesService.getScriptProperties().setProperty('contentUser1','');
  }
  if(dataUser2.length>1){
    MailApp.sendEmail('[e-mail]', 'Modification dans le planning FFE', dataUser2);
    PropertiesService.getScriptProperties().setProperty('contentUser2','');
  }
}

after a mail has been sent, the stored data is deleted. No mail is sent if no change was recorded.

You can also notice that I have 2 different users and 2 different storage places so that each of them can see what the other does without being notified for his own modifications.

Since both function use installable triggers, this will run on your account so beware not to explode your quotas if you set the timer to a very short period.