如果特定单元格发生更改，则发送电子邮件通知，并在没有更改跟踪的情况下向消息添加其他单元格值 [英] Email notification if specific cell is changed and add another cells value to message without change tracking
问题描述
如果M列中的任何单元格已被更改，则发送电子邮件通知以在同一行中指定N列的地址。但我也需要从同一行的其他列指定正文文本。我做了一些工作，但也导致如果其他已声明的列（如项目，客户，任务，执行者）已被更改，emil已发送到。
我需要什么：
仅追踪一个M列中的变化，并将其他列的电子邮件附加数据的主体放在同一行中。 （这点）没有跟踪其他栏的变化，电子邮件应该只发送改变列M。
可能它会很容易，但我扭曲...
我建立这个脚本基于：
我的脚本：
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 sendto ='';
if（cell.indexOf（'M'）！= - 1）{
sendto = sheet.getRange（'N'+ sheet.getActiveCell（）。getRowIndex（））。getValue（）
}
var project ='';
project = sheet.getRange（'C'+ sheet.getActiveCell（）。getRowIndex（））。getValue（）
var customer ='';
customer = sheet.getRange（'D'+ sheet.getActiveCell（）。getRowIndex（））。getValue（）
var task ='';
task = sheet.getRange（'E'+ sheet.getActiveCell（）。getRowIndex（））。getValue（）
var executor ='';
executor = sheet.getRange（'F'+ sheet.getActiveCell（）。getRowIndex（））。getValue（）
var deadline ='';
截止日期= LanguageApp.translate（Utilities.formatDate（sheet.getRange（'I'+ sheet.getActiveCell（）。getRowIndex（））。getValue（），GMT，EEEE，dd MMMM YYYY）， 'en'，'pl'）
var status ='';
status = sheet.getRange（'M'+ sheet.getActiveCell（）。getRowIndex（））。getValue（）
var mysubject = status +'| '+ project +'：'+ task +' - '+ ss.getName（）+'update';
var mybody ='\\\
Status：'+ status +'\\\
\\\
project：'+ project +'\\\
customer：'+ customer +'\\\
task：'+ task +'\\\
executor ：'+ executor +'\\\
Deadline：'+ deadline +'\\\
\\\
'+ ss.getName（）+'：\\\
'+ ss.getUrl（）;
MailApp.sendEmail（{
to：sendto，
subject：mysubject，
body：mybody}）;
};
我已经制作了一个示例脚本，您应该可以访问 https://docs.google.com/spreadsheets/d / 11u0xkdtPlQsnVppCnPYM0CHuCTPLdmN8PcFlaW08lNw / edit＃gid = 0
您必须复制才能真正做到。
但 ...复制不会给您我设置的触发器。如果您编辑脚本，请转至菜单资源 - > 当前项目的触发器，并为函数checkForChanges（）创建基于时间的触发器。 EM>。我将它设置为每分钟用于测试目的。
在您的专栏中，我添加了四个新的： 因此...定期检查函数checkForChanges（）范围 ChangeDetector 寻找真实。如果没有找到它，立即退出。 每次找到更改时，它都会收集该行的数据并通过电子邮件发送。 （实际上，为了简单起见，我只是将它记录下来。） 关键技巧是两行：
lastEdits [row _] [0] = currentHashCodes [row _] [0];
ss_.getRangeByName（LastEdit）。setValues（lastEdits）;
请注意，如果您使用命名范围可能。
以下是代码，以防将来的示例在某一天丢失：
/ *从Current Hash列中的单元格调用* /
函数strHash（valCell）{
var hash = 0;
if（valCell.length == 0）返回散列;
for（i = 0; i
hash =（（hash <<< 5）-hash）+ char;
hash = hash&散列; //转换为32位整数
}
返回散列;
}
/ *定时从定时触发器中调用。 * /
函数checkForChanges（）{
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var changeDetector = ss.getRangeByName（ChangeDetector）。getValues（）;
for（row in changeDetector）{
if（row> 0）{
changed = changeDetector [row] [0];
if（changed）notify（ss，row）;
}
}
}
/ *由checkForChanges（）调用。 * /
函数notify（ss_，row_）{
initializeRangeArrays（ss_）;
status = statii [row _] [0];
project = projects [row _] [0];
task = tasks [row _] [0];
customer = customers [row _] [0];
executor = executors [row _] [0];
截止日期=截止日期[row _] [0];
sendto =收件人[row _] [0];
var mysubject = status +'| '+ project +'：'+ task +' - '+ ss_.getName（）+'update';
var mybody ='\\\
Status：'+ status
+'\\\
\\\
project：'+ project
+'\\\
customer：'+ customer
+' \\\
task：'+ task
+'\\\
executor：'+ executor
+'\\\
Deadline：'+ deadline
+'\\\
\\\
'+ ss_.getName （）
+'：\ n'+ ss_.getUrl（）;
Logger.log（to：+ sendto）;
Logger.log（subject：+ mysubject）;
Logger.log（body：+ mybody）;
Logger.log（）;
lastEdits [row _] [0] = currentHashCodes [row _] [0];
ss_.getRangeByName（LastEdit）。setValues（lastEdits）;
};
var recipients = null;
var projects = null;
var customers = null;
var tasks = null;
var deadlines = null;
var executors = null;
var statii = null;
var lastEdits = null;
var currentHashCodes = null;
var rangeArraysInitialized = false;
/ *由notify（）调用。 * /
函数initializeRangeArrays（ss_）{
if（！rangeArraysInitialized）{
recipients = ss_.getRangeByName（Recipient）。getValues（）;
projects = ss_.getRangeByName（Project）。getValues（）;
customers = ss_.getRangeByName（Customer）。getValues（）;
tasks = ss_.getRangeByName（Task）。getValues（）;
deadlines = ss_.getRangeByName（Date）。getValues（）;
statii = ss_.getRangeByName（Status）。getValues（）;
executors = ss_.getRangeByName（Executor）。getValues（）;
lastEdits = ss_.getRangeByName（LastEdit）。getValues（）;
currentHashCodes = ss_.getRangeByName（CurrentHash）。getValues（）;
rangeArraysInitialized = false;
}
}
更新2014/09/22：
我在演示电子表格中做了一些更改。请仔细阅读。
转至我添加的脚本。 。 。
function strArrayHash（range）{
var ret = new Array（）;
var str =;
for（item in range）{
str = range [item] .toString（）;
if（str.length> 0）{
ret [item] = strHash（str）;
Utilities.sleep（50）; //玩这个以减少内部执行错误s
} else {
ret [item] =;
}
};
return ret;
}
在列P中，我替换了。 。 。
= if（M2 =，，strHash（R2）））
= if（M3 = ），，strHash（R3）））
：
：
= if（M22 =，，strHash（R22）））
。 。 。与。 。 。
= ARRAYFORMULA（if（M2：M514 =，，strArrayHash（R2：R514）））
。 。 。在单元格P2仅中。我还删除了范围P3：P22中所有单元格的内容。
我将总行数增加到1026
一旦数值出现在P列中，我使用[Paste Special]»[仅粘贴数值]将单元格P2：P1026复制到M2：M1026中。
在我的i7笔记本电脑中，它花了30秒钟时间重新计算并检测到500行的更改。
如果尝试使用内部执行错误做所有1024行。
可能您需要将if子句复杂化，以便仅对真正需要的行进行散列计算。
更新:: 2017/02/24没有人对此感兴趣，因此我停止了运行该脚本的触发器。
What i did:
If any cell in column M has been changed, then email notification has been sended to specify adres from column N at the same row. But i also need some specify body text from other columns at the same row. I did something that is working but it also causes that if other declared columns (such as project, customer, task, executor) has been changed the emil has been send to.
What i need:
Just track change in only one "M" column and put at the body of email additional data from other columns but from the same row. And (thats the point) did not track change at other columns, email should be send only if changing column M.
Probably it would be easy, but i'm twisted...
I bulid this script based on:
- email notification if cell is changed
- how to attach onChange cell value event/script to google sheet
- Restrict notifications sent for changes referenced in columns
My script:
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 sendto = '';
if(cell.indexOf('M')!=-1){
sendto = sheet.getRange('N'+ sheet.getActiveCell().getRowIndex()).getValue()
}
var project = '';
project = sheet.getRange('C'+ sheet.getActiveCell().getRowIndex()).getValue()
var customer = '';
customer = sheet.getRange('D'+ sheet.getActiveCell().getRowIndex()).getValue()
var task = '';
task = sheet.getRange('E'+ sheet.getActiveCell().getRowIndex()).getValue()
var executor = '';
executor = sheet.getRange('F'+ sheet.getActiveCell().getRowIndex()).getValue()
var deadline = '';
deadline = LanguageApp.translate(Utilities.formatDate(sheet.getRange('I'+ sheet.getActiveCell().getRowIndex()).getValue() , "GMT" , "EEEE, dd MMMM YYYY" ),'en','pl')
var status = '';
status = sheet.getRange('M'+ sheet.getActiveCell().getRowIndex()).getValue()
var mysubject = status + ' | ' + project + ': ' + task + ' - ' + ss.getName() + ' update';
var mybody = '\nStatus: ' + status + '\n\nproject: ' + project + '\ncustomer: ' + customer + '\ntask: ' + task + '\nexecutor: ' + executor + '\nDeadline: ' + deadline + '\n\n' + ss.getName() + ': \n' + ss.getUrl();
MailApp.sendEmail({
to:sendto,
subject:mysubject,
body:mybody});
};
I have made an example script that you should be able to see at https://docs.google.com/spreadsheets/d/11u0xkdtPlQsnVppCnPYM0CHuCTPLdmN8PcFlaW08lNw/edit#gid=0
You'll have to make a copy to actually do something with it.
But ... copying won't give you the trigger I set up. If you edit the script, go to the menu Reources --> Current project's triggers and make yourself a time-based trigger on the function checkForChanges(). I set it for "every minute" for testing purposes.
To your columns I added four new ones:
- concat - is simply a concatenation of all the row values whose changes you need to monitor
- Current Hash - is generated from a simple function I added to your script
- Last Edit - after sending an email the script gives this cell the value of Current Hash
- Changed - Compares Last Edit and Current Hash and says **true* if they are different.
So ... periodically the function checkForChanges() runs down the range ChangeDetector looking for true. If it finds nothing it quits immediately.
Each time it does find a change, it collects the data of that row and emails it. (Actually, I just log it, for simplicity sake.)
The key trick is the pair of lines :
lastEdits[row_][0] = currentHashCodes[row_][0];
ss_.getRangeByName("LastEdit").setValues(lastEdits);
Note how clear your code can be if you use named ranges where ever possible.
Here's the code in case the example gets lost someday in the future :
/* Called from cells in column "Current Hash" */
function strHash(valCell) {
var hash = 0;
if (valCell.length == 0) return hash;
for (i = 0; i < valCell.length; i++) {
char = valCell.charCodeAt(i);
hash = ((hash<<5)-hash)+char;
hash = hash & hash; // Convert to 32bit integer
}
return hash;
}
/* Called periodically from a timed trigger. */
function checkForChanges() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var changeDetector = ss.getRangeByName("ChangeDetector").getValues();
for (row in changeDetector) {
if (row > 0) {
changed = changeDetector[row][0];
if (changed) notify(ss, row);
}
}
}
/* Called by checkForChanges(). */
function notify(ss_, row_) {
initializeRangeArrays(ss_);
status = statii[row_][0];
project = projects[row_][0];
task = tasks[row_][0];
customer = customers[row_][0];
executor = executors[row_][0];
deadline = deadlines[row_][0];
sendto = recipients[row_][0];
var mysubject = status + ' | ' + project + ': ' + task + ' - ' + ss_.getName() + ' update';
var mybody = '\nStatus: ' + status
+ '\n\nproject: ' + project
+ '\ncustomer: ' + customer
+ '\ntask: ' + task
+ '\nexecutor: ' + executor
+ '\nDeadline: ' + deadline
+ '\n\n' + ss_.getName()
+ ': \n' + ss_.getUrl();
Logger.log("to: " + sendto);
Logger.log("subject: " + mysubject);
Logger.log("body: " + mybody);
Logger.log("");
lastEdits[row_][0] = currentHashCodes[row_][0];
ss_.getRangeByName("LastEdit").setValues(lastEdits);
};
var recipients = null;
var projects = null;
var customers = null;
var tasks = null;
var deadlines = null;
var executors = null;
var statii = null;
var lastEdits = null;
var currentHashCodes = null;
var rangeArraysInitialized = false;
/* Called by notify(). */
function initializeRangeArrays(ss_) {
if ( ! rangeArraysInitialized ) {
recipients = ss_.getRangeByName("Recipient").getValues();
projects = ss_.getRangeByName("Project").getValues();
customers = ss_.getRangeByName("Customer").getValues();
tasks = ss_.getRangeByName("Task").getValues();
deadlines = ss_.getRangeByName("Date").getValues();
statii = ss_.getRangeByName("Status").getValues();
executors = ss_.getRangeByName("Executor").getValues();
lastEdits = ss_.getRangeByName("LastEdit").getValues();
currentHashCodes = ss_.getRangeByName("CurrentHash").getValues();
rangeArraysInitialized = false;
}
}
Update 2014/09/22 :
I have made a few changes in the demo spreadsheet. Please take a peek.
To the script I added . . .
function strArrayHash(range) {
var ret = new Array();
var str = "";
for (item in range) {
str = range[item].toString();
if (str.length > 0) {
ret[item] = strHash(str);
Utilities.sleep(50); // play with this to reduce "internal execution error"s
} else {
ret[item] = "";
}
};
return ret;
}
In column "P" I replaced . . .
=if( M2 = "", "", strHash(R2)))
=if( M3 = "", "", strHash(R3)))
:
:
=if( M22 = "", "", strHash(R22)))
. . . with . . .
=ARRAYFORMULA(if( M2:M514 = "", "", strArrayHash(R2:R514)))
. . . in cell "P2" ONLY. I also deleted the contents of all cells in the range "P3:P22"
I increased the total number of rows to 1026
Once values appeared in column P, I copied cells P2:P1026 into M2:M1026 using [Paste Special] » [Paste values only].
In my i7 laptop it took it 30 seconds to recalc and detect a change in line 500.
I get an internal execution error if I try to do all 1024 lines.
Probably you will need to complicate the "if" clause such that it causes hash calculations on only the lines that really need it.
Update :: 2017/02/24 No one is interested in this so I stopped the trigger that ran the script.
这篇关于如果特定单元格发生更改，则发送电子邮件通知，并在没有更改跟踪的情况下向消息添加其他单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
