电子邮件通知如果单元格更改 [英] email notification if cell is changed
问题描述
需要执行以下操作的脚本:
如果列G中的任何单元格已更改,则将电子邮件通知
发送到具有此行信息的自定义地址:来自
单元格D的信息和单元格G的新值。
UPD
我找到了有用的信息:
函数emailNotification(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = ss.getActiveCell()。getA1Notation();
var cellvalue = ss.getActiveCell()。getValue()。toString();
var recipient =me@gmail.com;
var subject ='更新为'+ sheet.getName();
var body = sheet.getName()+'已更新。访问'+ ss.getUrl()+'查看单元格上的更改:«'+ cell +'»新单元格值:«'+ cellvalue +'»';
MailApp.sendEmail(收件人,主题,正文);
};
该脚本跟踪整个表中的更改。我只想跟踪G列中的更改,并从D列中获取值。
问题:
如何获得列D中单元格的值,当值为
时,已将列G中的单元格更改
blockquote>
最后一个脚本 - 回答我的问题
功能sendNotification(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = ss.getActiveCell()。getA1Notation();
var row = sheet.getActiveRange()。getRow();
var cellvalue = ss.getActiveCell()。getValue()。toString();
var recipients =me@gmail.com;
var message ='';
if(cell.indexOf('G')!= - 1){
message = sheet.getRange('D'+ sheet.getActiveCell()。getRowIndex())。getValue()
}
var subject ='更新为'+ sheet.getName();
var body = sheet.getName()+'已更新。访问'+ ss.getUrl()+'查看行上的更改:«'+ row +'»。新评论:«'+ cellvalue +'»。对于消息:«'+消息+'»';
MailApp.sendEmail(收件人,主题,正文);
};
在onEdit上设置触发器并且脚本可以正常工作
解决方案在发布问题前,您应该搜索此论坛;我在电子邮件+单元格上进行了搜索,结果如下: / questions / 13768532 / how-to-attach-onchange-cell-value-event-script-to-google-sheet / 13770980#13770980>这篇文章的答案几乎完全符合你的要求。 p>
在您的编辑之后进行编辑:使用
IF 条件的
。
类似这样的:
var cellG =''
if(cell.indexOf('D')!= - 1){// =如果您编辑col中的数据D
cellG = sheet.getRange('G'+ sheet.getActiveCell()。getRowIndex ())。getValue()
//根据需要添加更多条件和/或发送邮件(cellG包含活动行的列G中的值
}
Logger.log(cellG )
need help with google script. I have multiple row spreadsheet.
Need a script that does the following:
If any cell in column G has been changed, then send email notification to custom address with information from this row: information from cell D and new value of cell G.
UPD
I found useful information:
function emailNotification() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var cell = ss.getActiveCell().getA1Notation(); var cellvalue = ss.getActiveCell().getValue().toString(); var recipient = "me@gmail.com"; var subject = 'Update to '+sheet.getName(); var body = sheet.getName() + ' has been updated. Visit ' + ss.getUrl() + ' to view the changes on cell: «' + cell + '» New cell value: «' + cellvalue + '»'; MailApp.sendEmail(recipient, subject, body); };
This script tracks the changes in the entire table. And I would like track changes only in column G, and get values from column D.
Question:
How to get the value of the cell in column D when the value has changed cell in column G
Finally script — answer to my question
function sendNotification() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var cell = ss.getActiveCell().getA1Notation(); var row = sheet.getActiveRange().getRow(); var cellvalue = ss.getActiveCell().getValue().toString(); var recipients = "me@gmail.com"; var message = ''; if(cell.indexOf('G')!=-1){ message = sheet.getRange('D'+ sheet.getActiveCell().getRowIndex()).getValue() } var subject = 'Update to '+sheet.getName(); var body = sheet.getName() + ' has been updated. Visit ' + ss.getUrl() + ' to view the changes on row: «' + row + '». New comment: «' + cellvalue + '». For message: «' + message + '»'; MailApp.sendEmail(recipients, subject, body); };
Set trigger on onEdit and script will work fine
解决方案You should search this forum before posting questions; I did search on email+cell and got a few results :
For example, this post answer does almost exactly what you want to do.
EDIT following your edit :
use an
IF
condition.Something like this :
var cellG = '' if(cell.indexOf('D')!=-1){ // = if you edit data in col D cellG = sheet.getRange('G'+ sheet.getActiveCell().getRowIndex()).getValue() // add more condition if necessary and/or send your mail (cellG contains the value in column G of the active row } Logger.log(cellG)
这篇关于电子邮件通知如果单元格更改的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!