Google App Script to trigger email

I'm trying to get an email to fire alerting me if a column in a Google Sheet =X.

I've tried using the notification system using using the EQ operator to turn on when a cell equals X, this changes a cell and the notification system is supposed to send an email right away. It's not working that way, the email never fires.

I'm think I need a script that would fire an email if a cell =X which would be nice because I could customize the email so it made more sense. (i.e. "The conference room your event is being held has reached capacity, you should remove it from the reservation form")

I am new to Google Scripts and could use a little help creating the email trigger if cell =X script.

Thanks,

Dave

标签： email triggers google-apps-script

2条回答

The star\"

2楼-- · 2019-06-11 11:04

You would need to add a script to run onEdit.

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NameOfYourSheet");
  var currentValue = sheet.getRange("A5").getValue();
  if (currentValue == (whateverValueYoureLookingFor)) {
    MailApp.sendEmail("youremail@domain.com", "ALERT: The cell in the sheet is equal to X!", "The message body that you want to send.");
  }
}

0人赞添加讨论(0) 举报

劳资没心，怎么记你

3楼-- · 2019-06-11 11:14

function testSchemas() {
  var htmlout = HtmlService.createTemplateFromFile('data').evaluate().getContent();

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('All Exams Tracker');
var range = sheet.getDataRange();
  var values = range.getValues();
  var last_row = sheet.getLastRow();
  var today = new Date()


  for (var i =3 ;i < last_row;i++) {
   var temp = values[i][4];
    var e_date = new Date(temp);
    if ((( e_date - today)/(1000*60*60*24)) < 10 && e_date > today){
      var cond = "done" }
    break; }
Logger.log(cond);  

  if (cond == "done"){


 MailAppv.sendEmail({
    to: '*************************************************************',
     subject: 'Exam Coming Next Week',
    htmlBody: htmlout,
  });

  }}

This will html file data (given below) when triggered.

<html>
<head>
<style>
table, th, td {
    border: 1px solid black;
    border-collapse: collapse;
}
th, td {
    padding: 5px;

}
</style>
</head>
<table style = "border: 1px solid black; border-style: collapse;">
<?   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('All Exams Tracker');
var range = sheet.getDataRange();
  var values = range.getValues();
  var last_row = sheet.getLastRow();

  var today = new Date();?>

  <tr style="border: 1px solid black; padding: 10px;">

    <th style="border: 1px solid black; padding: 10px;">Exam Name</th>
    <th style="border: 1px solid black; padding: 10px;">Exam Date</th>
    <th style="border: 1px solid black; padding: 10px;">Priority</th>
    <th style="border: 1px solid black; padding: 10px;">College Predictor Status</th>
     <th style="border: 1px solid black; padding: 10px;">Rank Predictor Status</th>

</tr>

  <? for (var i =3 ;i < last_row;i++) {
   var temp = values[i][4];
    var e_date = new Date(temp);
    if ((( e_date - today)/(1000*60*60*24)) < 10 && e_date > today) {


   if (values[i][12] != ""){
  var col_status = "Live"}
  else
  if (values[i][11] != ""){
  col_status = "Uploaded and Tested"}
  else
  if (values[i][10] != ""){
  col_status = "Data Processed"}
  else
  if (values[i][9] != ""){
  col_status = "Template Ready"}

  else
  col_status = "Not initiated yet"

   if (values[i][19] != ""){
   var rank_status = "Live"}
  else
  if (values[i][18] != ""){
  rank_status = "Uploaded and Tested"}
  else
  if (values[i][17] != ""){
  rank_status = "Data Processed"}
  else
  rank_status = "Not Initiated yet"   


  ?>
<tr>

    <td style="border: 1px solid black; padding: 10px;"><?= values[i][2]?></td>
    <td style="border: 1px solid black; padding: 10px;"><?= values[i][4]?></td>
    <td style="border: 1px solid black; padding: 10px;"><?= values[i][8]?></td>
    <td style="border: 1px solid black; padding: 10px;"><?= col_status?></td>
    <td style="border: 1px solid black; padding: 10px;"><?= rank_status?></td>

</tr>
<? } }?>
</table>
<br><br>
<p><a href="https://docs.google.com/spreadsheets/d/1fzbRCvnNfuVTCEuRnncJxb1VW7f-tkSEywntzaTugZQ/edit#gid=0">Click Here For More Details</a></p>
</html>

0人赞添加讨论(0) 举报

Google App Script to trigger email

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间