当Google电子表格中的值发生变化时发送电子邮件 [英] Send Email when value changes in Google Spreadsheet

问题描述

我试图弄清楚，Google Spreadsheet中的以下内容如何。

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

1. 单元格中的值发生变化时发送电子邮件。 （值=已完成）。


2. 将行数据编译到电子邮件中。请参阅下面的代码格式。


3. 提示用户确认信息。


4. 如果是，发送电子邮件给活动用户以及预设用户下面的代码。


5. 这是可选的：在列（P）16上使用Email Sent + timestamp更新行中的表。

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.

您好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.

发送当K列值发生变化时发送一封电子邮件。

Send an email when the value changes for column K.

部分示例代码观看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

电子邮件将会包含该特定行的指定值。防爆。列A，B，C，D，E，F，G，H，I，J中的值

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);
}

该工作流程适用于K，L，M，N，O列。电子邮件将被发送到代码中的预设电子邮件地址。我希望这解释得更好一点。

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. 在资源>当前项目的触发器中添加触发sendEmail（）编辑时的触发器。
   
2. ...

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

这篇关于当Google电子表格中的值发生变化时发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！