当不同列中的值增加时发送电子邮件通知 [英] Send email notification when values in different columns increase

问题描述

我有一张从A1到BK288的数组。此数组是基于另一个电子表格的百分比计算，每5分钟更新一次。现在，我希望收到电子邮件通知，当不同列中同一行上的一个值增加并且变得大于固定值时。然后，电子邮件应该包含该主体上的主题和主题栏目的标题。
我写了这个脚本，但是对于一个单元格，我不知道是否有一种方法可以为每一列扩展它，除了为每列写一个函数外。另外，有没有办法触发自动通知？考虑表单每5分钟更新一次值。谢谢

  function getValue（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = ss.getSheetByName（Perc）; 
 var value = sheet.getRange（A288）。getValue（）; 
 var title = sheet.getRange（A1）。getValue（）; 
 if（value> =2）sendEmail（value，title）
}; 
 
 
函数sendEmail（值，标题）{
 var recipient =[e-mail]; 
 var subject = title +price; 
 var body = title +price has changed by+ value +％; 
 MailApp.sendEmail（收件人，主题，正文）; 
}; 
  

解决方案

这是一个解决方案。

数据一个接一个地放在数组和处理列中。我还在多个产品发生更改时添加了一个解决方案，因此只有一个邮件会发送所需的所有信息，以预先配置每天的配额。

编辑：只分析第288行

  function compareValue（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（） ; 
 var sheet = ss.getSheetByName（Perc）; 
 var data = sheet.getRange（1，1，sheet.getLastRow（），sheet.getLastColumn（））。getValues（）; 
 var list = []; //变量列表大于2 
 
（var i = 0; i< data [0] .length; i ++）{
 
 var title = data [0] [一世]; //获取元素的标题
 var value = parseFloat（data [data.length -1] [i]）; //获取最后一行的值（288th）
 
 if（value> = 2）{//如果值大于2，则将其添加到列表
 
 var element = [title，value]; 
 
 list.push（element）
 
} 
} 
 
 if（list.length == 1）{// if only 1元素更改，发送经典邮件
 sendEmail（list [0] [1]，list [0] [0]）; 
} 
 if（list.length> = 2）{//如果有2个或更多元素发生变化，发送所有更改的邮件
 sendEmail（list）; 
} 
} 
 
函数sendEmail（value，title）{
 var recipient =[e-mail]; 
 var subject = title +price; 
 var body = title +price has changed by+ value +％; 
 MailApp.sendEmail（收件人，主题，正文）; 
} 
 
函数sendEmail（list）{
 var recipient =[e-mail]; 
 var subject =倍数价格已经改变; 
 var body =; 
 for（var i = 0; i< list.length; i ++）{//获取列表以写入主体
 body = body + list [i] [0] +price has changed按+ list [i] [1] +\％\\\
; 
} 
 MailApp.sendEmail（recipient，subject，body）; 
} 
  

I have a sheet with an array of values that goes from A1 to BK288. This array is a percentage calculation based on another spreadsheet that updates every 5 minutes. Now I would like to receive an email notification when one value on the same row in different columns increase and become greater then a fixed value. Then the email should contain that volue on the body and the title of the column on the subject. I wrote this script but is for a single cell, I don't know if there is a way to extend it for every column, apart writing a function for every column. Also, is there a way to trigger an automatic notification? Consider the sheet updates the values every 5 minutes. Thanks

function getValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Perc");
  var value = sheet.getRange("A288").getValue();
  var title = sheet.getRange("A1").getValue();
  if(value >= "2") sendEmail(value, title)
};


function sendEmail(value, title){
  var recipient="[e-mail]";
  var subject=title + " price";
  var body=title + " price has changed by " + value + "%";
  MailApp.sendEmail(recipient, subject, body);
};

解决方案

Here is a solution.

The data are put on a array and process column one after one other. I also add a solution when multiple product changed, so only one mail is send with all the informations needed, to prevend the daily quota to be reached.

Edit: Only analyse row 288

function compareValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Perc");
  var data = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  var list = []; // List of change greater than 2

  for(var i=0; i<data[0].length; i++){

    var title = data[0][i]; // Get the title of the element
    var value = parseFloat(data[data.length -1][i]); // Get the value on last row (288th)

    if(value >= 2){ // If the value greater than 2, add it on a list

      var element = [title, value];

      list.push(element)

    }
  }

  if(list.length == 1){ // If only 1 element changing, send classic mail
    sendEmail(list[0][1], list[0][0]);
  }
  if(list.length >= 2){ // If 2 or more element changing, send mail with all the change
    sendEmail(list);
  }
}

function sendEmail(value, title){
  var recipient="[e-mail]";
  var subject=title + " price";
  var body=title + " price has changed by " + value + "%";
  MailApp.sendEmail(recipient, subject, body);
}

function sendEmail(list){
  var recipient="[e-mail]";
  var subject="Multiples prices have changed";
  var body = "";
  for(var i=0; i<list.length;i++){ // Get throught the list to write the body
    body=body + list[i][0] + " price has changed by " + list[i][1] + "\%\n";
  }
  MailApp.sendEmail(recipient, subject, body);
}

这篇关于当不同列中的值增加时发送电子邮件通知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！