如何根据Google电子表格上已更改的单元格发送通知 [英] How to send a notification based on changed cell on google spreadsheet

问题描述

我知道它类似于 如果单元格已更改，则通过电子邮件发送通知

但我想执行以下操作:

在A列中有提醒名称，B列中有电话，F列是状态

In column A I have the reminder Name, column B I have the Telephone, and column F the status

状态值为1,2,3,4,5

The status values are 1,2,3,4,5

所以基本上我想在F列更改为4,5时发送一封电子邮件，其中包含A列和B列的信息.

So Basically I want to send an email when Column F changes to 4,5 with the information of Column A and column B.

示例:提醒名称刚刚更改为状态4，请给他们打电话:电话号码

Example: Reminder Name has just been changed to status 4, please give them a call: Phone number

function checkReminder() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  var sheet = spreadsheet.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var startRow = 2;
  var range = sheet.getRange(2,5,lastRow-startRow+1,1 );
  var numRows = range.getNumRows();
  var sV = range.getValues();
  range = sheet.getRange(2, 1, lastRow-startRow+1, 1);
  var reminder_info_values = range.getValues();

  var warning_count = 0;
  var msg = "";


  for (var i = 0; i <= numRows - 1; i++) {
    var statusC = sV[i][0];
    if(statusC > 3) {
      var reminder_name = reminder_info_values[i][0];

      msg = msg + "The reminder: "+reminder_name+" changed status to "+statusC +" ";
      warning_count++;
    }
  }

  if(warning_count) {
    MailApp.sendEmail("[e-mail]",
        "CC Changed status of "+ reminder_name, msg);
  }

};

我不知道如何使它工作，请帮忙

I don't know how to make it work, please help

我也尝试了在Google Spreadsheet中的值更改时发送电子邮件

推荐答案

您的脚本缺少一个重要功能:您应该记住是否已发送邮件，然后再发送邮件，否则每次阅读单元格值时，邮件都会已发送.

your script was missing an important feature : you should remember if a mail has already been sent before sending it otherwise each time you read the cell value a mail will be sent.

有很多方法可以实现，添加带有已发送邮件"标志的列可能是最常见的方法，但绝对不是我喜欢的方法...

There are many ways to achieve that, adding a column with a "mail sent" flag is probably the most common but definitely not the one I prefer ...

这是一个在发送邮件时使单元格变色的版本，我选择红色，但是您当然可以更改为更离散的颜色. (我添加了一个记录器，以便您可以轻松查看十六进制颜色)

Here is a version that colorizes the cell when a mail is sent, I choose red but you can change to a more discrete color of course. (I added a logger so you can see the colors hex code easily)

function checkReminder() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  var sheet = spreadsheet.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var startRow = 2;
  var range = sheet.getRange(2,5,lastRow-startRow+1,1 );
  var numRows = range.getNumRows();
  var sV = range.getValues();
  var bGColors = range.getBackgroundColors();// get range background colors
  var range_reminder = sheet.getRange(2, 1, lastRow-startRow+1, 1);
  var reminder_info_values = range_reminder.getValues();

  var warning_count = 0;
  var msg = "";


  for (var i = 0; i <= numRows - 1; i++) {
    var statusC = sV[i][0];
    Logger.log(bGColors[i][0]);
    if(statusC > 3 && bGColors[i][0] != '#ff0000') { // if not already sent
      var reminder_name = reminder_info_values[i][0];
      bGColors[i][0] = '#ff0000';
      msg = msg + "The reminder: "+reminder_name+" changed status to "+statusC +" ";
      warning_count++;
    }
  }
  range.setBackgroundColors(bGColors);// update sheet with colors
  if(warning_count>0) {
    MailApp.sendEmail("[e-mail]",
                      "CC Changed status of "+ reminder_name, msg);
  }

};

您可以设置在onEdit或在计时器上调用此脚本的触发器，这两种方法都可以使用，这完全是一个选择问题(除非您绝对需要即时反应).

You can set a trigger calling this script either onEdit or on a timer, both method will work, it's more a matter of choice (unless you absolutely need to have an instantaneous reaction).

这篇关于如何根据Google电子表格上已更改的单元格发送通知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！