Google应用脚本来触发电子邮件 [英] Google App Script to trigger email

问题描述

我试图收到一封电子邮件，提醒我，如果Google Sheet = X中的一列。

我尝试使用通知系统使用EQ操作员在单元格等于X时打开，这会更改单元格，通知系统应该立即发送电子邮件。这不是这样，电子邮件永远不会触发。

我想我需要一个脚本，如果一个单元格= X这将是很好的，因为我会可以自定义电子邮件，因此更有意义。 （即您的活动正在举行的会议室已达到容量，您应该从预订表单中删除）

我是Google脚本的新手，可以使用感谢，

Dave

解决方案

  function testSchemas（）{
 var htmlout = HtmlService.createTemplateFromFile（'data'）。评价（）的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  var temp = values [i] 4]; 
 var e_date = new Date（temp）; 
 if（（（e_date  -  today）/（1000 * 60 * 60 * 24））< 10&& e_date>今天）{
 var cond =done} 
休息} 
 Logger.log（cond）; 
 
 if（cond ==done）{
 
 
 MailAppv.sendEmail（{
 to：'******** ************************************************** ***'，
主题：下一周考试，
 htmlBody：htmlout，
}）; 
 
}} 
  

这将在html文件数据（如下所示）触发时。

 < html> 
< head> 
< style> 
表，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  var temp = values [i] [4]; 
 var e_date = new Date（temp）; 
 if（（（e_date  -  today）/（1000 * 60 * 60 * 24））< 10&& e_date>今天）{
 
 
 if（值[i] [12]！=）{
 var col_status =Live} 
 else 
 if（values [i] [11]！=）{
 col_status =已上传和测试} 
 else 
 if（values [i] [10]！=）{
 col_status =数据处理} 
 else 
 if（values [i] [9]！=）{
 col_status =Template Ready} 
 
 else 
 col_status =尚未启动 
 
 if（values [i] [19]！=）{
 var rank_status =Live} 
 else 
 if（values [i] 18]！=）{
 rank_status =已上传和测试} 
 else 
 if（values [i] [17]！=）{
 rank_status = 数据处理} 
 else 
 rank_status =尚未启动
 
 
？> 
< 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>点击此处查看更多详情< / a>< ; / p为H. 
< / html> 
  

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

解决方案

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>

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