json - GoogleScripts - 如何根据单元格值发送电子邮件

Question

我是 Google Scripts 的初学者，想根据 Google 表格中的单元格值发送电子邮件。

确定是否要发送电子邮件的单元格是工作表“FloodEWS”中的 C2。

如果值等于或大于 270，我需要发送特定的电子邮件。如果值等于或大于 310，我需要发送不同的电子邮件。

我到目前为止的脚本是这样的：

function amberwarning() {
  // Fetch the combined flow value
  var combflowrange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FloodEWS").getRange("C2"); 
  var combflow = combflowrange.getValue();
  // Check combined flow value
  if (270 < combflow < 310){
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").getRange("A2");
    var emailAddress = emailRange.getValues();
    
    // Send Alert Email.
    var message = 'It is possible that the Egger site will experience flooding in the coming hours. The advice is to be prepared to take action as combined river flows can increase very quickly during storms. Please keep up to date with the latest river levels for Hexham at <https://flood-warning-information.service.gov.uk/station/9006>. The latest flood warnings from the Environment Agency for Hexham are here <https://flood-warning-information.service.gov.uk/warnings?location=+hexham>. The latest MetOffice weather forecast can be found here <https://www.metoffice.gov.uk/weather/forecast/gcy2xzrne#?>. Please use all available information to inform your decision making. You will keep receiving an email as per each refresh of the latest data. The current combined flow from the North and South Tyne is' + combflow;
    var subject = 'Amber flood warning';
    MailApp.sendEmail(emailAddress, subject, message);
    }
}

任何帮助将非常感激。

标签： jsongoogle-apps-scriptgoogle-sheetsconditional-formatting

Answer 1

假设：

• 如果 value 大于等于 270 且小于 310，则发送消息 1。

• 如果值大于或等于 310，则发送消息 2。

• 其他任何事情，发送错误消息。

• 根据您的要求修改脚本。

使用的表：

     A          B         C
---------------------------------
1    RHF        HBF       CF
2    72.25      63.95     311
3       
4    RL         HBL       HL
5    1.566      1.015     32.014

function val() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet8")
var range = sheet.getRange(1, 1, 5, 3);
var data = range.getValues();
var combinedFlow = data[1][2];

var customMsg_1 = "Hey Level 1" ;
var customMsg_2 = "Hey Level 2" ;

if (combinedFlow >= 270 && combinedFlow < 310)
  message = customMsg_1;
  
else if (combinedFlow >= 310)
  message = customMsg_2;
  
else
  message = "error in script";
  
MailApp.sendEmail("[e-mail]", "Combined Flow", message);

}