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

问题描述

我想弄清楚，如何在 Google 电子表格中执行以下操作.

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

1. 当单元格中的值发生变化时发送电子邮件.(值 = 已完成).
2. 将该行数据编译到电子邮件中.请参阅下面代码中的格式.
3. 提示用户确认信息.
4. 如果是，则向活动用户以及下面代码中的预设用户发送电子邮件.
5. 这是可选的:使用已发送电子邮件 + 时间戳更新第 (P) 列 (P) 16 行中的工作表.

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.

嗨塞尔，

尝试实施您提供的代码，但我无法确定要修改哪些内容以适应我需要完成的工作.

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,"
  "

The following data room(s) will need a disc creation. Please begin bulk save data room and create ISO to upload to the FTP site: " +
  "
Project Name: " + ProjectName +
  "
Project ID: " + ProjectId +
  "
Project Manager: " + ProjectManager +
  "
Phase: " + Phase +
  "
Disc Type: " + DiscType +
  "
Encryption: " + Encryption +
  "
Quantity: " + Qty +
  "
Client Due Date: " + DueDate +
  "
SpecialInstructions: " + 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,"
  "

The following data room(s) will need a disc creation. Please begin bulk save data room and create ISO to upload to the FTP site: " +
  "
Project Name: " + ProjectName +
  "
Project ID: " + ProjectId +
  "
Project Manager: " + ProjectManager +
  "
Phase: " + Phase +
  "
Disc Type: " + DiscType +
  "
Encryption: " + Encryption +
  "
Quantity: " + Qty +
  "
Client Due Date: " + DueDate +
  "
SpecialInstructions: " + 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.

推荐答案

我刚刚写了一个脚本来做那种事情，但我想让它关注工作表中的所有变化，但每次只发送一次消息小时以避免向我的邮箱发送垃圾邮件.

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.

该脚本有 2 个函数，一个用于收集更改并以文本格式存储它们，另一个用于在过去一小时内发生任何更改时发送电子邮件.

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.

第一个函数称为grabData，必须由onEdit可安装触发器触发，如下所示:

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+='
Cellule '+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+='
Cellule '+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.

您还可以注意到，我有 2 个不同的用户和 2 个不同的存储位置，因此他们每个人都可以看到对方做了什么，而不会因为自己的修改而收到通知.

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.

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