当编辑的单元格值显示“完成"时，自动发送电子邮件.电子邮件主题是已编辑行中的第一个单元格 [英] Sending an email automatically when the edited cell value says "Done" and the email subject is the first cell in the edited row

问题描述

我正在尝试编写一个脚本，在该脚本中，对单元格进行编辑后会自动向特定的人发送电子邮件，并且该单元格等于完成".但是，我希望该主题成为已编辑行的第一个单元格.包含完成"的单元格将始终位于AA列中，而我希望主题是同一行的A列.例如:AA3已被编辑，因此主题为A3.我花了数小时筛选教程，并提出了以下建议:

function checkValue() {
  var sp = PropertiesService.getScriptProperties();
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Accts");
  var valueToCheck = sheet.getRange("AA2:AA1000").getValue();
  if (valueToCheck = 'Done') {
    MailApp.sendEmail("a***[e-mail]", activeCell.offset(-26,0).getValue(), Email.html);
  }
}

我这样做是完全错误的还是有希望?

现在解决了.我以为我会分享脚本的最终效果.我添加了一个UI和一个使用菜单选项执行的选项.希望这对其他人有帮助.

function onEdit(e) 
{
  var editRange = { // AA2:AA1000
    top : 2,
    bottom : 1000,
    left : 27,
    right : 27
  };

  // Exit if we're out of range
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;

  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;

  var thisthang = e.value;
  var doit = 'TRUE'

  // We're in range; timestamp the edit
  if(thisthang == doit)
  {
    doFinish();
  }
  else{return};
} 

function onOpen()
{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Finished')
      .addItem('Finish', 'doFinish')
      .addToUi();
}

function doFinish()
{
   var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
   var row = cell.getRow();

   var Campaign = getCampaignFromRow(row, 1); 

   var ui = SpreadsheetApp.getUi();
   var response = ui.alert('Finish '+Campaign.name+'?', ui.ButtonSet.YES_NO);

   if(response == ui.Button.YES)
   {
     handleFinish(row, Campaign);
   }
   if(response == ui.Button.NO)
   {
     SpreadsheetApp.getActiveSheet().getRange(row, 27).setValue('FALSE');
   }
}

function getCampaignFromRow(row)
{
  var values = SpreadsheetApp.getActiveSheet().getRange(row, 1).getValues();
  var rec = values[0];

  var Campaign = 
      {
        Campaign_Name: rec[0]
      };

   Campaign.name = Campaign.Campaign_Name;

   return Campaign;
}

function handleFinish(row, Campaign)
{
  var templ = HtmlService
      .createTemplateFromFile('Campaign-email');

  templ.Campaign = Campaign;

  var message = templ.evaluate().getContent();

  MailApp.sendEmail({
    to: "a***[e-mail]",
    subject: "A Campaign has been finished!",
    htmlBody: message
  });

  SpreadsheetApp.getActiveSheet().getRange(row, 27).setValue('TRUE');

}

解决方案

在"Accts"表上的"AA"列中，值为="Done"的电子表格进行编辑时，您试图触发电子邮件. /p>

最好的解决方案是使用onEdit触发器，并同时使用Event Objects.在这种情况下，一个简单的触发器无法发送电子邮件" ref ，因此您将需要创建一个可安装的触发器.

您的脚本的主要区别是:
-var valueToCheck = sheet.getRange("AA2:AA1000").getValue();
-这里有几件事.
-1)您试图获取列中的所有值，但使用getValue(单个单元格的方法)而不是getValues.
-2)您可以定义ActiveCell并返回该值
-3)尽管您尝试获取整个列的值，但是您的if语句被设计为好像是单个值而不是值的数组.
-4)这证明了使用事件对象的好处.您可以简洁地获取已编辑单元格和工作表的值.
-在if比较中，使用"=";这仅用于分配值.比较值时，必须使用"=="或"===".
-要获取主题"的值，脚本将使用从事件对象"派生的行号；与脚本中的偏移量相比-两者都是可以接受的.我用getRange演示了替代方法.
-您的电子邮件正文定义为"Email.html"，但未声明.答案使用的是非常简单的正文，但也可以轻松使用其他解决方案.

---

function so5967209001(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()

  // establish the values to be checked
  var checkSheetname = "Accts"
  var checkValue = "Done"
  var checkColumn = 27; // column AA

  // this will return the event objects
  //Logger.log(JSON.stringify(e)); // DEBUG

  // variables to use for checking
  var editedrange = e.range;
  var editedrow = editedrange.getRow();
  var editedcolumn = editedrange.getColumn();
  var editedsheet = editedrange.getSheet().getSheetName();
  var editedvalue = e.value;
  //Logger.log("DEBUG: row = "+editedrow+", column = "+editedcolumn+", sheet = "+editedsheet+", value"+editedvalue)

  // test the sheet, the column and the value
  if (editedsheet ==checkSheetname && editedcolumn==checkColumn && editedvalue == checkValue){

    //Logger.log("DEBUG: this is a match");

    var subject = sheet.getRange(editedrow,1).getValue(); // Column A of the edited row
    //Logger.log("DEBUG: email subject = "+subject);

    // build your own body
    var body = "this is the body of the email"

    // send the email
    MailApp.sendEmail("[e-mail]", subject, body);
    //Logger.log("DEBUG: mail sent")

  }else{
    //Logger.log("DEBUG: this isn't a match");

  }
}

I'm trying to write a script where an automatic email is sent to a specific person upon edit of a cell and the cell equals "Done". However, I want the subject to be the first cell of the edited row. Cell that will contain "Done" is always going to be in the AA Column, and I want the subject to be the A column of the same row. Ex: AA3 was edited so subject is A3. I have spent hours sifting through tutorials and came up with this:

function checkValue() {
  var sp = PropertiesService.getScriptProperties();
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Accts");
  var valueToCheck = sheet.getRange("AA2:AA1000").getValue();
  if (valueToCheck = 'Done') {
    MailApp.sendEmail("a***[e-mail]", activeCell.offset(-26,0).getValue(), Email.html);
  }
}

Am I doing this entirely wrong or is there hope?

EDIT: Now that it's resolved. I thought I'd share what my script ended up looking like. I added a UI and an option to execute using a menu option. Hope this helps someone else.

function onEdit(e) 
{
  var editRange = { // AA2:AA1000
    top : 2,
    bottom : 1000,
    left : 27,
    right : 27
  };

  // Exit if we're out of range
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;

  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;

  var thisthang = e.value;
  var doit = 'TRUE'

  // We're in range; timestamp the edit
  if(thisthang == doit)
  {
    doFinish();
  }
  else{return};
} 

function onOpen()
{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Finished')
      .addItem('Finish', 'doFinish')
      .addToUi();
}

function doFinish()
{
   var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
   var row = cell.getRow();

   var Campaign = getCampaignFromRow(row, 1); 

   var ui = SpreadsheetApp.getUi();
   var response = ui.alert('Finish '+Campaign.name+'?', ui.ButtonSet.YES_NO);

   if(response == ui.Button.YES)
   {
     handleFinish(row, Campaign);
   }
   if(response == ui.Button.NO)
   {
     SpreadsheetApp.getActiveSheet().getRange(row, 27).setValue('FALSE');
   }
}

function getCampaignFromRow(row)
{
  var values = SpreadsheetApp.getActiveSheet().getRange(row, 1).getValues();
  var rec = values[0];

  var Campaign = 
      {
        Campaign_Name: rec[0]
      };

   Campaign.name = Campaign.Campaign_Name;

   return Campaign;
}

function handleFinish(row, Campaign)
{
  var templ = HtmlService
      .createTemplateFromFile('Campaign-email');

  templ.Campaign = Campaign;

  var message = templ.evaluate().getContent();

  MailApp.sendEmail({
    to: "a***[e-mail]",
    subject: "A Campaign has been finished!",
    htmlBody: message
  });

  SpreadsheetApp.getActiveSheet().getRange(row, 27).setValue('TRUE');

}

解决方案

You are trying to trigger an email when the spreadsheet is edited on the "Accts" sheet, in Column "AA" and the value = "Done".

The best solution is to use an onEdit trigger and also make use of Event Objects. In the case, "a simple trigger cannot send an email" ref, so you will need to create an Installable Trigger.

The main differences to your script are:
- var valueToCheck = sheet.getRange("AA2:AA1000").getValue();
- a couple of things here.
- 1) you are trying to get all the values in the column, but you use the getValue (the method for a single cell) instead of getValues.
- 2) you could have defined the ActiveCell and just returned that value
- 3) though you tried to get the values for the entire column, your if statement is designed as though there is a single value rather than an array of values.
- 4) This demonstrates the benefit of the using the Event Objects. You can succinctly get the values of the edited cell and sheet.
- in your if comparison, you use "="; this is only used to assign a value. When comparing values you must use "==" or "===".
- To get the value of the "subject", the script uses the row number derived from the Event Objects; compared to the offset in your script - they are both acceptable. I used the getRange to demonstrate the alternative.
- your email body was defined as "Email.html", but this isn't declared. The answer uses a very simple body but could just as easily use another solution.

---

function so5967209001(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()

  // establish the values to be checked
  var checkSheetname = "Accts"
  var checkValue = "Done"
  var checkColumn = 27; // column AA

  // this will return the event objects
  //Logger.log(JSON.stringify(e)); // DEBUG

  // variables to use for checking
  var editedrange = e.range;
  var editedrow = editedrange.getRow();
  var editedcolumn = editedrange.getColumn();
  var editedsheet = editedrange.getSheet().getSheetName();
  var editedvalue = e.value;
  //Logger.log("DEBUG: row = "+editedrow+", column = "+editedcolumn+", sheet = "+editedsheet+", value"+editedvalue)

  // test the sheet, the column and the value
  if (editedsheet ==checkSheetname && editedcolumn==checkColumn && editedvalue == checkValue){

    //Logger.log("DEBUG: this is a match");

    var subject = sheet.getRange(editedrow,1).getValue(); // Column A of the edited row
    //Logger.log("DEBUG: email subject = "+subject);

    // build your own body
    var body = "this is the body of the email"

    // send the email
    MailApp.sendEmail("[e-mail]", subject, body);
    //Logger.log("DEBUG: mail sent")

  }else{
    //Logger.log("DEBUG: this isn't a match");

  }
}

这篇关于当编辑的单元格值显示“完成"时，自动发送电子邮件.电子邮件主题是已编辑行中的第一个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！