如何在特定单元格的值更改时让Google Sheet脚本发送电子邮件？ [英] How do I make a Google Sheet script send an email when a specific cell's value changes?

问题描述

我试图为我的Google表设置一个脚本，当特定单元格的值发生变化时它将发送一封电子邮件。我也想知道是否有办法设置脚本，以便每天只发送一次;例如，如果在一天中进行了多项更改，我宁愿只在一天结束时发送一封电子邮件，而不是每次发送一封电子邮件。

I'm trying to set up a script for my Google Sheet that will send an email when a specific cell's value changes. I'm also wondering if there's a way to set up the script so that it will only get sent out once a day; for example, if multiple changes are made on a day, I'd rather it only send out one email at the end of the day as opposed to sending out an email every single time.

为了提供一些背景知识，电子表格是针对在网络上发布的客户的声明。我希望在更新账单余额时通知客户。如果我能够将单元格的新值添加到电子邮件中，以便他们能够在电子邮件中看到他们的新余额，它也会很酷。

To provide a bit of background, the spreadsheet is a statement for a customer that is published on the web. I'd like it to notify the customer when an update has been made to their remaining balance on the statement. It'd also be cool if I could plug in the cell's new value to the email message so they can see their new balance in the email itself.

我目前使用下面的脚本没有运气：

I'm currently using the following script with no luck:

function myFunction(e) {

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Statement");
  var valueToCheck = sheet.getRange("J13").getValue();
  var rangeEdit = e.range.getA1Notation();

  if(rangeEdit !== "J13")

    {

      MailApp.sendEmail("*****@gmail.com", "subject", "message");

    }

}

当前脚本设置为onChange事件触发器。

The current script is set to the onChange event trigger.

推荐答案

您需要将值存储在文档（电子表格）的属性中。文档属性只是Google文档的存储功能。

You'll need to store values in the document's (Spreadsheet's) "Properties". The document properties is just a storage feature that Google documents have.

因此，代码必须存储电子表格单元格中的原始值，并存储

So, the code must store the original value that was in the spreadsheet cell, and store the date that the last email was sent.

function myFunction(e) {

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Statement");
  var valueToCheck = sheet.getRange("J13").getValue();
  var rangeEdit = e.range.getA1Notation();

  if (didTheValueInCellChange(valueToCheck) === false) {return}; //The value didn't change, quit

  if (rangeEdit === "J13") {
    if (hasAnEmailBeenSentToday() === false) {
      MailApp.sendEmail("*****@gmail.com", "subject", "message");
      emailJustSent();
      setOriginalValue(valueToCheck);
    };
  };
};

function emailJustSent() {
  var docProps = PropertiesService.getDocumentProperties();
  var todaysDate = new Date();//Use date constructor, then convert date object to a string
  var scriptTimeZone = Session.getScriptTimeZone();

  var formatedDate = Utilities.formatDate(todaysDate, scriptTimeZone, "MM/dd/YYYY");

  docProps.setProperty(key, value)('dateSent');
};

function hasAnEmailBeenSentToday() {
  var docProps = PropertiesService.getDocumentProperties();
  var todaysDate = new Date();//Use date constructor, then convert date object to a string
  var scriptTimeZone = Session.getScriptTimeZone();

  var formatedDate = Utilities.formatDate(todaysDate, scriptTimeZone, "MM/dd/YYYY");

  var dateWhenEmailWasSent = docProps.getProperty('dateSent');

  if (dateWhenEmailWasSent === formatedDate) {
    return true;
  } else {
    return false;
  };
};


function didTheValueInCellChange(currentValue) {

  var docProps = PropertiesService.getDocumentProperties();  
  var theLastValueSaved = docProps.getProperty('originalValue');

  if (theLastValueSaved !== currentValue) {
    return true;
  } else {
    return false;
  };
};

function setOriginalValue(theValue) {
  docProps.setProperty('originalValue', theValue);
};

这篇关于如何在特定单元格的值更改时让Google Sheet脚本发送电子邮件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！