Google表格-发送电子邮件 [英] Google Sheet - Send Email

问题描述

每当条件成立时，我都需要我的Google工作表才能发送电子邮件.在这种情况下，每次的C2值都小于J2的值.在单元格L2上有电子邮件地址.

I need my google sheet to send emails every time a condition becomes true. In this case, every time value of C2 is lower than value of J2. On the cell L2 there is the email address.

代码(可在线查找并刚刚编辑)

The code (found online and just edited)

function CheckPrice() {
  var LastPriceRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Alerts").getRange("C2"); 
  var LastPrice = LastPriceRange.getValue();
  var EntryLimitRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Alerts").getRange("J2"); 
  var EntryLimit = LastPriceRange.getValue();
  var StockNameRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Alerts").getRange("B2"); 
  var StockName = LastPriceRange.getValue();
  // Check totals sales
  if (LastPrice < EntryLimit){
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Alerts").getRange("L2");
    var emailAddress = emailRange.getValues();
  
    // Send Alert Email.
    var message = 'Ticker ' + StockName + ' has triggered the alert';
    var subject = 'Stock Alert';
    MailApp.sendEmail(emailAddress, subject, message);
    }
}

使用此代码，我没有收到任何错误，但是我什至没有收到电子邮件.首次运行脚本时，我按要求授予了权限.

With this code I don't receive any error, but I don't even receive the email. I granted permissions as requested when I run the script for the first time.

在L2上，我输入了我允许的相同电子邮件地址(我向自己发送电子邮件).我甚至尝试输入我拥有的辅助电子邮件地址.

On L2 I put the same email address I granted permission (I send email to myself). I did a try even putting a secondary email address I have.

你能告诉我怎么了吗?

推荐答案

问题:

• 在定义 LastPrice 的代码的第一行中， EntryLimit 和 StockName .他们都来自同一个范围: LastPriceRange .

• See the first lines of your code where you define LastPrice, EntryLimit and StockName. All of them are coming from the same range: LastPriceRange.

我还删除了脚本中所有不必要的调用.没有需要打电话 SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Alerts")这样很多时候，您可以将其放在变量中并使用变量.

I also removed all the unnecessary calls in your script. There is no need to call SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Alerts") so many times when you can just put it in a variable and use that variable instead.

此外，您无需定义不必要的内容变量.例如，您可以使用一行获取单元格的值: sh.getRange("B2").getValue().

Also, you don't need to define unnecessary variables. For example, you can get the value of a cell with one line: sh.getRange("B2").getValue().

解决方案:

function CheckPrice() {
  
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Alerts");   
  const LastPrice = sh.getRange("C2").getValue();
  const EntryLimit = sh.getRange("J2").getValue();
  const StockName = sh.getRange("B2").getValue(); 

  // Check totals sales
  if (LastPrice < EntryLimit){
    // Fetch the email address
    const emailAddress = sh.getRange("L2").getValue();
  
    // Send Alert Email.
    const message = 'Ticker ' + StockName + ' has triggered the alert';
    const subject = 'Stock Alert';
    MailApp.sendEmail(emailAddress, subject, message);
    }
}

如果您要在编辑特定单元格时发送电子邮件，则需要将上述解决方案转换为

If you want an email to be sent when you edit a particular cell, then you need to transform the aforementioned solution to an onEdit(e) trigger. If you want a time basis trigger then you can just use the solution above directly.

这篇关于Google表格-发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！