javascript - 当自动更新的单元格更改值时，如何让 Google 表格向您发送电子邮件

Question

我对 Google 表格中的脚本编写非常陌生，因此我的问题可能比我想象的困难得多，也可能简单得多。

基本上，我有一个 Google 表格，其中一列记录了生日列表，以及该人所属的年龄组 - child (13 岁以下)、青少年(13-17 岁)或成人(18+)——在另一栏中。 “年龄组”列使用以下公式计算:

=IF(DATEDIF(H2, TODAY(), "Y") >= 18, "成人", IF(DATEDIF(H2, Today(), "Y") >= 13, "青少年"，“ child ”))

H 列是生日列。

我的 Google 表格设置为每小时重新计算 Today() 函数，无论文件是否打开，因此如果某个人的 13 岁或 18 岁生日临近，年龄组列将自动更新。

我的目标是创建一个功能，只要某人的年龄组从 child 更改为青少年或从青少年更改为成人，该功能就会向我发送电子邮件，而无需用户打开 Google 表格或以任何方式进行修改，触发器。

到目前为止，我一直在尝试“编辑时”触发器，但我注意到这需要我手动更改要触发的年龄组单元格 - 这违背了自动化的目的。

我还尝试将触发事件类型从“编辑时”更改为“更改时”，希望每当年龄组列中的单元格更改其结果值时都会触发脚本，但到目前为止这还没有起作用.

我的代码目前如下(请注意，此代码仅弹出通知，而不是发送实际的电子邮件 - 我还没有到达该部分):

/*
This is the function that is triggered by an edit to
the Google Sheet
*/
function onEdit(e) {
  sendEmailOnApproval(e);  
}

/*
Primarily used for earlier debugging
*/
function showMessageOnApproval(e)
{
  var edited_row = checkStatusIsApproved(e);
  if(edited_row > 0)
  {
    SpreadsheetApp.getUi().alert("Row # "+edited_row+" approved!");
  }
}

/*
Determines if the Age Group column reads Teen or Adult.
Column 9 (or 'I' in the sheet) is the Age Group column.
*/
function checkStatusIsApproved(e)
{
  var range = e.range;

  if(range.getColumn() <= 9 && 
     range.getLastColumn() >=9 )
  {
    var edited_row = range.getRow();

    var status = SpreadsheetApp.getActiveSheet().getRange(edited_row,9).getValue();
    if(status == 'Adult' || status == 'Teen')
    {
      return edited_row;
    }
  }
  return 0;
}

function sendEmailOnApproval(e)
{
  var approved_row = checkStatusIsApproved(e);

  if(approved_row <= 0)
  {
    return;
  }

  sendEmailByRow(approved_row);
}


/*
At the moment, this function only creates a little pop-up
box containing the text of the email I would like to send.
*/
function sendEmailByRow(row)
{
  var values = SpreadsheetApp.getActiveSheet().getRange(row,1,row,9).getValues();
  var row_values = values[0];

  var mail = composeApprovedEmail(row_values);

  SpreadsheetApp.getUi().alert(" subject is "+mail.subject+"\n message "+mail.message);
}


/*
Writes the email. Column 0 (or 'A') is the first name,
1 (or 'B') is the last name.
*/
function composeApprovedEmail(row_values)
{
  var first_name = row_values[0];

  var last_name = row_values[1];

  var age = row_values[8];

  var message = "The following person should be updated on the website: "+first_name+" "+last_name+
    " Age Range: "+age;
  var subject = "UPDATE WEBSITE for "+first_name+" "+last_name;

  return({message:message,subject:subject});
}

虽然此代码在手动更改 Age Range 列中的值时有效，但我如何调整它以捕获该列所依赖的 Today() 函数的每日重新计算所导致的自动更新？

Answer 1

根据Apps Script Trigger documentation :

• onOpen(e) runs when a user opens a spreadsheet, document, presentation, or form that the user has permission to edit

• onEdit(e) runs when a user changes a value in a spreadsheet

因此，您尝试执行的操作无法通过使用之前使用的方法来完成，因为您尝试使用触发器，该触发器每次由函数(表格公式)进行更改时都会触发，而不是由用户。

解决方法

您可以做的是:

1. 使用您之前使用的表格公式，但将重新计算设置更改为更改时，而不是像以前那样的更改时和每小时以前做过。

这样，您仍然可以获得根据提供的日期分类的年龄。

使用以下脚本:

function ageChanges(){
  var ss = SpreadsheetApp.getActive().getActiveSheet();
  var dateVals = ss.getRange("YOUR_RANGE_FOR_THE_DATES").getValues();
  var ageVals = ss.getRange("YOUR_RANGE_FOR_THE_AGES").getValues();
  var dateNow = new Date();
  var recipient = "EMAIL_ADDRESS";
  var subject = "Age Change in Spreadsheet";
  var body = "";

  for (var i=0; i<dateVals.length; i++){
    
    var ages = (dateNow - dateVals[i][0])/(3.154e+10);

    if (ages > 18 && ageVals[i][0]!="Adult") {
      ss.getRange(i+1, 9).setValue("Adult");
      body = "Change in column H, row"+i+".";
      MailApp.sendEmail(recipient,subject,body);
      
    }
    else if (ages > 13 && ages < 18 && ageVals[i][0]!="Teen") {
      ss.getRange(i+1, 9).setValue("Teen");
      body = "Change in column H, row"+i+".";
      MailApp.sendEmail(recipient,subject,body);
    }
      
  }
     
}

该脚本根据电子表格中的日期计算年龄。如果年龄与相应类别不匹配，则会发送一封包含所做更改的邮件。

为了每天监控更改，您必须使用上述脚本作为可安装的触发器。

使用以下选项创建可安装的触发器:

这样，脚本将在每天您选择的时间(在本例中为午夜至凌晨 1 点)运行，如果年龄发生任何变化，您将收到电子邮件(s) 进行更改。

此外，我建议您查看这些链接，因为它们可能对您 future 的开发有所帮助:

• Installable Triggers ;

• MailApp Class .